Re: Newbie question

In article <o0ALWJBGtFvCFw8y_at_shrdlu.com>, bap_at_shrdlu.com says...
> >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.

No, there is still a 1:1 mapping even if you don't change the surrogate key. I think we are talking past each other here. Are you talking about a mapping in an informal sense, as in existing in the users' minds and/or outside of the database?

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

Yes they are, unless it's possible to have two different plates on the same car at the same time.

> There is a 1:m relationship between plates and cars.

If you want to record the plate history or something like that, sure. If not, there is not.

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

See above.

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

Yes, therefore surrogate keys should not change. Half the point of them is that they don't change!

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

Yes, this is known as the singularity problem, I think. You have the same problem with incorrect natural keys, though.

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

Isn't this just the problem that it is always possible to feed incorrect information to the database?

Anyway, I think we agree that both natural and surrogate keys are useful. My main argument is that it is never necessary to change surrogate keys.

-- 
Jon