Re: Newbie question

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.

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

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

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

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

-- 
Jon