Re: Artificial Primary keys

From: Bernard Peek <>
Date: Mon, 28 Jan 2002 21:43:10 +0000
Message-ID: <>

In message <a333do$hqq$>, Jan Emil Larsen <> writes
>"Bernard Peek" <> skrev i en meddelelse
>> In message <3c5186c2$0$14015$>, Jan Emil
>> Larsen <> writes
>> >If the key holds information, the key will (must) change if that
>> >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.

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

No. I agree that non-key values can change, names can't be primary keys because they don't uniquely identify an individual. Even if there is only one living person called Euckd G. Thsscdd there is nothing stopping someone from being christened with the same name tomorrow.

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

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.

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

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

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


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

Bernard Peek

In search of cognoscenti
Received on Mon Jan 28 2002 - 22:43:10 CET

Original text of this message