Re: Surrogate keys (Was: enums)

[Michael Glaesemann <grzm(at)myrealbox(dot)com>]

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