Re: Surrogate keys (Was: enums)

Michael Glaesemann <grzm(at)myrealbox(dot)com> writes:
> 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.

D&D's examples of "natural" keys are worth a second look though:

>> If a primary key exists for a collection that is known never to change,
>> for example social security number, student registration number, or
>> employee number, then no additional system-assigned UID is required.

The problem with SSN is that somebody other than you controls it.
If you are the college registrar, then you control the student's
registration number, and you don't have to change it. In fact, guess
what: you probably generated it in the same way as a surrogate key.

I'd argue that all of these are in reality the exact same thing as
a surrogate key --- from the point of view of the issuing authority.
But from anyone else's point of view, they are external data and you
can't hang your own database design on the assumption that they won't
change.

regards, tom lane