> -----Original Message----- > From: Michael Glaesemann [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 18, 2006 5:48 PM > To: Dann Corbit > Cc: Leandro Guimarães Faria Corcete Dutra; Jim C. Nasby; pgsql- > [EMAIL PROTECTED] > Subject: Re: [HACKERS] 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?
If you could find an immutable natural key, it would be the *BEST* thing to use. Unfortunately, I believe that immutable natural keys are rarer than horse feathers and pickle smoke. Furthermore, because of statements like the one that I collected and pasted from the above document, I believe that people will choose totally inappropriate things (I have seen it many times and had to deal with the repercussions) to use as natural keys (e.g. SSN) and cause enormous damage through those choices. But I suppose on a sort of "mathematical" level the statement is fully true. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
