Re: Surrogate keys (Was: enums)

Martjin,

> In any of these either misspellings, changes of names, ownership or
> even structure over time render the obvious useless as keys. There are
> techniques for detecting and reducing duplication but the point is that
> for any of these duplicates *can* be valid data.

Please point me out where, in the writings of E.F. Codd or in the SQL
Standard, it says that keys have to be immutable for the life of the row.

Duplicate *values* can be valid data. Duplicate *tuples* show some
serious flaws in your database design. If you have a personnel
directory on which you've not bothered to define any unique constraints
other than the ID column, then you can't match your data to reality. If
you have two rows with the same first and last name, you don't know if
they are two different people or the same person, duplicated. Which
will be a big problem come paycheck time.

Per E.F. Codd, each tuple is a *unique* predicate (or key) comprising a
set of values definining a *unique* data entity. i.e. "The employeee
named "John" "Little" at extension "4531". There is nothing anywhere
said about keys never changing.

This is Databases 101 material. Really!

--Josh