Re: Artificial Primary keys

"Bernard Peek" <bap_at_shrdlu.com> skrev i en meddelelse news:V4YEVzEOOIV8Ewhm_at_shrdlu.com...
> In message <3c5186c2$0$14015$edfadb0f_at_dspool01.news.tele.dk>, Jan Emil
> Larsen <jel_at_g-it.dk> writes

<snip>
> >If the key holds information, the key will (must) change if that
information
> >changes.
>
> If it is a natural key then the information cannot change, it is
> immutable. The value of the key identifies one and only one object. If
> the value of the key changes then it must identify a different object.

Your are _defining_ a natural key as a key that does not change. By that definition you cannot be wrong :-)

> >If the key do not hold information, then nothing force it to change.

> That is true and that is one reason why surrogate keys are often more
> convenient.

> >Identifying one and only on thing isn't enough to keep the key immutable.
> >"Things" may change, eg. Name or even SSN, but they identify at any time
> >only one "thing".
>
> Names are not good candidates for keys because they change and they are
> not unique. John Q. Smith may be the same person as Quentin Smith.

But then you do agree!

> SSNs are good keys but are not perfect. Some people don't have them (I
> don't have one) and they sometimes change. For some purposes they are
> good enough, which is why some organisations have used them
> successfully.
>
> >Or could you give an example - that is: of a natural key that holds
> >information, but is immutable?
>
> I quoted one a few days ago, a natural key that uniquely identifies a
> person. If you have the precise location and time that someone is born
> you can uniquely identify them. Once they have been born the data is
> immutable. The uniqueness of the key is enforced by the laws of physics,
> it is impossible for two objects to occupy the same space at the same
> time.

And you would chose such a key instead of a surrogate key? Could you please be completely explicit: what is the domain of the key, and how do you represent it?
Such a key - if indeed represented to reflect to laws of physics - would be very awkward.
In fact, to a degree that there is a risk that the value recorded turns out to be wrong. Then you have to change it ...

If no other "natural key" as such one was offered, I wouldn't hesitate to take a surrogate.

> In general I have complete trust in keys where uniqueness is guaranteed
> by the laws of physics.
The easiest way to achive that is by using a surrogate key. The "law of physics" only applies to physical objects, but there are many concepts that are not physical objects.

> I have a lot of trust in keys where I have
> control over the codes that are issued.
>I have somewhat less trust in
> keys that are supposed to be unique but are issued by third-parties
> (SSN, ISBN etc.) where I have no control.

Why don't you prefer surrogates then? Received on Mon Jan 28 2002 - 09:51:02 CET