Re: Newbie question
Date: Thu, 23 Jun 2005 21:04:22 +0100
In message <MPG.1d232e6a11c10d119896ac_at_news.ntnu.no>, Jon Heggland <heggland_at_idi.ntnu.no> writes
>In article <gTH5vQUczHuCFwXh_at_shrdlu.com>, bap_at_shrdlu.com says...
>> >Can you elaborate on this? Why does a surrogate have to change when
>> >the natural key for which it is a surrogate changes? Do you have some
>> >historical (temporal) system in mind?
>> Nope. The natural key identifies the object that the record refers to.
>> If the natural key changes it's because the record now refers to a
>> different object.
>Not necessarily; it depends on the problem domain, the purpose of the
>database, and the nature of the change. If you change an erroneous
>natural key, does the "record" now refer to a "different object"? If you
>change the license plates of a car, does it become a "different object"?
>For some purposes yes, for others, no. How do you define "different
I did consider whether to mention the exception for incorrect key values, but decided not to. If the key value is incorrect then it points to the wrong object.
>> It should get the new object's surrogate key too.
>Why? What breaks badly if they don't?
The point of having a key is that there is a 1:1 mapping between key values and the objects they identify. If one key value can be mapped to two or more different objects why use it?
>(I find the phrase "the new object's surrogate key" a little
>objectionable, too---it doesn't *have* a surrogate key unless and until
>we decide to generate one.)
>> Names are not natural keys.
>That depends on the problem domain and the purpose of the database.
That's true. You could have a list of all of the possible unique names, but I can't think of any practical use for that.
>> If you want a natural key for a person try
>> the exact latitude, longitude, altitude and precise time of their birth.
>That is an excellent example of the need for surrogate keys. :)
Of course. There are times when there is no real alternative to using a surrogate key. They are very useful at times, but I usually avoid using them if I have a natural key available. Maintaining the 1:1 mapping between surrogate keys and the objects they identify can involve a lot of work and that usually increases the risk of error.
-- Bernard Peek London, UK. DBA, Manager, Trainer & Author.Received on Thu Jun 23 2005 - 22:04:22 CEST