Re: Surrogate keys (Was: enums)

On Jan 19, 2006, at 10:34 , Dann Corbit wrote:

> http://www.db.ucsd.edu/cse132B/Thirdmanifesto.pdf

> "PROPOSITION 1.4: Unique Identifiers (UIDs) for records should be
> assigned by the DBMS only if a user-defined primary key is not
> available.

<snip />

> An immutable primary key has an extra advantage over a system-
> assigned unique identifier because it has a natural, human readable
> meaning. Consequently, in data interchange or debugging this may be
> an advantage. If no primary key is available for a collection,
> then it is imperative that a system-assigned UID be provided.

<snip />

Dann Corbit:

> The primary key should be immutable, meaning that its value should
> not be changed during the course of normal operations of the
> database. What natural key is immutable? The answer is that such
> an attribute does not exist. To use them for such a purpose is
> begging for trouble.

As far as I can tell, the only difference between your position,
Dann, and Date and Darwen's, is that you think no natural key is
immutable. If you *could* find an immutable natural key, would it be
an acceptable key for you? Date and Darwen say explicitly that if no
immutable (natural) (primary) key is available a system-assigned UID
is required. If you think there is no immutable natural key
available, Darwen and Date would agree that you should use a system-
generated key. Or do you think I'm misreading you or The Third
Manifesto?

Michael Glaesemann
grzm myrealbox com