Re: Newbie question

From: Bernard Peek <>
Date: Tue, 21 Jun 2005 21:47:24 +0100
Message-ID: <>

In message <1CNte.7368$>, Jonathan Leffler <> writes
>Bernard Peek wrote:
>> Jon Heggland <> writes
>>> I'm not sure why you think surrogate keys are not acceptable---they are
>>> a staple of relational database design---but I'll assume you think they
>>> are useless in the presence of natural keys (which is the term for keys
>>> that come from the "real world").
>> I haven't found anyone who says that surrogate keys are
>>unacceptable. They do have disadvantages though so I would usually
>>suggest trying to use natural keys wherever possible.
>>> Off the top of my head, the main
>>> advantages of surrogate keys vs. natural keys are that they are stable
>>> (I.e. they don't change),
>> That is one of their failure modes. If the natural key changes then
>>any surrogate keys should change too. If they don't then the system is
>>badly broken.
>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. It should get the new object's surrogate key too.

>Suppose either that someone made a mistake entering an employee's name
>in the database or that an employee gets married and changes their
>name; neither circumstance means, in my view (understanding) that their
>employee number needs to change. The records that were associated with
>their past performance, for example, are still applicable to them under
>the corrected or new name.

Names are not natural keys. If you want a natural key for a person try the exact latitude, longitude, altitude and precise time of their birth.

Bernard Peek
London, UK. DBA, Manager, Trainer & Author.
Received on Tue Jun 21 2005 - 22:47:24 CEST

Original text of this message