Re: Artificial Primary keys

From: Jan Emil Larsen <jel_at_g-it.dk>
Date: Tue, 29 Jan 2002 09:03:25 +0100
Message-ID: <a35l0g$a5k$1@news.net.uni-c.dk>

"Bernard Peek" <bap_at_shrdlu.com> skrev i en meddelelse news:7twfQtDuXcV8EwEh_at_shrdlu.com...
> In message <a333do$hqq$1_at_news.net.uni-c.dk>, Jan Emil Larsen
> <jel_at_g-it.dk> writes
> >
> >"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 :-)
>
> Correct. It stems from the definition of a key.
A (primary) key is a (combination of) attribute(s) that uniquely identifies an object (or a row in an RDB).
A row should represent a fact, that is: the combination of values (the information) shall be true.
Even though your definition has very nice consequences, I doubt that is reflects current practice.
This can eg be seen by the ability of many RDBMs' to cascade on a PK/FK change.
Your definition rules out almost any "natural key". Do you have an reference for your definition?

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

>

> If the information was available for everyone then nobody would use
> anything else as the key in their personnel databases. The data isn't
> available, which is why we are forced to substitute surrogate keys like
> the SSN.

>
> >Could you please be completely explicit: what is the domain of the key,
and
> >how do you represent it?

>

> I would probably represent it as latitude, longitude and altitude.
> Alternatively it would be just as acceptable to use polar co-ordinates.
> The units of measurement are not important.
I'm sure you would take the time of birth into consideration too. Lets just say that the key is four numbers: latitude, longitude, altitude and time, and that the units of measurement is able to distinguish nearby positions and events in time:

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

>

> The data could have been recorded incorrectly, but once someone has been
> born the true values have been established and cannot be changed.
The point is here that there is a risk that you record the wrong numbers, and this risk is quite high. There are almost infinite many wrong key values that is in the apparent domain - you can't detect an error. By your definition of a key, you cannot change the key after its recording, and you end up with false information.... This risk alone force you to use a surrogate (a key that carries no information).

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

>

> Of course. But you should only choose a surrogate after establishing
> that there is no usable natural key.

I agree. There should be a "natural (almost) candidate key" to communicate with the users.

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

>

> It is always possible to use a surrogate key and it is often easier.

>

> >The "law of physics" only applies to physical objects, but there are many
> >concepts that are not physical objects.
>
> True.

But then you cannot demand that a primary key should be ruled be the law of physics.

> >> 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?
>
> I accept surrogates as a necessary evil.

What is evil about surrogates? Received on Tue Jan 29 2002 - 02:03:25 CST