Re: Surrogate keys (Was: enums)

On Thu, Jan 19, 2006 at 12:06:41AM -0500, Tom Lane wrote:
> 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.

No, that's not the big problem. The big problem is that it's very
likely illegal for you to use it for anything unless you happen to be
the Social Security Administration.

> 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.

True.

> 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.

Right :)

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!