Re: Artificial Primary keys

In message <3c5186c2$0$14015$edfadb0f_at_dspool01.news.tele.dk>, Jan Emil Larsen <jel_at_g-it.dk> writes

>> >> >A key should be imutable, and should therefore be without information
>in
>> >it
>> >> >self.
>> >>
>> >> The first is true but the second doesn't follow from it.
>> >
>> >That is right. It goes the other way round: If it has information in it
>self,
>> >it may change.
>> >No-information in the key is a measure to secure immutability.
>>
>> No, that's still not right. Immutability is important but artificial
>> keys are not the only way to get it. If you have a natural key that
>> truly identifies one and only one thing then it is immutable. If it
>> changes then either it wasn't a real key or someone recorded the wrong
>> value.
>
>Yes, it IS right :-)
>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.

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

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.

In general I have complete trust in keys where uniqueness is guaranteed by the laws 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.

-- 
Bernard Peek
bap_at_shrdlu.com

In search of cognoscenti