Re: Newbie question

In message <MPG.1d25cdea855726249896af_at_news.ntnu.no>, Jon Heggland <heggland_at_idi.ntnu.no> writes

>In article <YCBnLhJGXxuCFwxa_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
>> >object"?
>>
>> 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.
>
>Perhaps, perhaps not. There may not even *be* an "object" to "point to";
>the incorrect key value may be unused in the real world. Anyway, that
>still does not answer the question why you would have to change the
>surrogate key.

To maintain 1:1 mapping between surrogate keys and the natural key, which identifies the object that the record holds data on.

>
>> License plates are not natural keys that identify cars if it's possible
>> to change plates on the same car.
>
>Why not? They are certainly unique (within a given scope and barring
>counterfeits). While there may be advantages to stable keys, stability
>is not a requirement.

They aren't unique if it's possible to have two different plates on something that would otherwise be described as the same car. There is a 1:m relationship between plates and cars. (Of course it's difficult to tell just what is "the same car" given that any part of the car may be removed and replaced by another piece without necessarily creating a new car.)

>
>> >> 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?
>
>Please show how this 1:1 mapping is lost.

If there can be more than one surrogate key value for an object then there cannot be a 1:1 relationship between that value and the object.

>
>> >> 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.
>
>Me neither, so I don't understand why you mention that. I can think of
>several cases where a name is a perfectly valid key, however. I am even
>the administrator of such a beast.

I haven't yet found any system where a name alone is a good key.

>
>> 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.
>
>YMMV. Changing surrogate keys when natural keys change surely seems like
>a lot of unnecessary work.

Provided you can do it without anyone noticing there isn't really a problem. But I've seen many systems where the surrogate key leaks out and gets used for other purposes. Once that happens it's very difficult to make invisible changes.

Essentially I see three extra risks when surrogate keys are used.

There is an insertion anomaly when a new record is created that assigns a new surrogate key value for an object which already has a record in the database.

There is a deletion anomaly when a record is selected for deletion using a surrogate key that does not point to the correct object.

There is an update anomaly when a record is updated using a surrogate key that does not point to the correct object.

All of these are additional risks that don't arise when a natural key is used. There are lots of occasions when they are necessary but they impose an additional requirement to test for those risks before making any changes to the database.

-- 
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.