Re: Normalization, Natural Keys, Surrogate Keys

From: Bernard Peek <>
Date: Wed, 29 May 2002 13:30:50 +0100
Message-ID: <J$>

In message <>, Malcolm Dew-Jones <> writes
>Bernard Peek ( wrote:
>: In message <>, Kai Ponte
>: <> writes
>: >> Having a surrogate primary key shouldn't let you off the hook. As a
>: >> rule of thumb, you always HAVE TO HAVE an alternate UNIQUE constraint.
>: >I respectfully disagree with "always have to have." Yes, it may often
>: >be necessary, but not always.
>: Can you suggest a situation where you do not need a unique constraint in
>: addition to the surrogate?
>The inability to provide a unique constraint (other than a surrogate) is
>exactly one of the reasons why data may need a surrogate.
>This happens when using old data. In addition to it being impractical to
>fix all old data before loading it into a new system, it may also be
>questionable to do so because the existing data may be a historical
>record, such as a license, which cannot be "corrected" within the
>framework of the application even if it appears to be wrong.

Good point. In that situation I'd probably do something similar, possibly by implementing a unique constraint on all new data but permitting it on old data, and flagging the old data as suspect.

The fact that data has been entered into a database without a uniqueness constraint is an indication that the database structure is at fault. One of the reasons why I prefer to avoid surrogates where possible is that using them can hide this type of failure.

Bernard Peek

In search of cognoscenti
Received on Wed May 29 2002 - 14:30:50 CEST

Original text of this message