Re: NextNumbers Tables

From: Alan Gutierrez <>
Date: Mon, 10 Feb 2003 18:30:20 GMT
Message-ID: <0dS1a.21891$>

stu wrote:

> "Alan Gutierrez" <> wrote in message
> news:1XO1a.20790$

>> I don't know if you are listening but, don't use a next number. Find a
>> a natural primary key.

> Why? My uni teacher used to say always use a surrogate key. It stops
> you from duplicating keys later. What advantages does it have over
> using IDs?

The word "always" is a red flag in computer science.

How are surrogate keys going to prevent you from duplicating keys later? I'm not sure I understand what you mean by 'duplicate a key'.

Bernard Peek offered me the following:

When a table has a real and surrogate key it is not fully normalised. That is the surrogate key is (you hope) completely dependent on the real key. But that isn't always certain. There are ways that dependency can be broken. When that happens your database is broken.

Insert Anomaly: A new record can be inserted that duplicates all of the data from an existing record. But it will have a new surrogate key. The system will duplicate data.

Update Anomaly: The data for the real key is changed, but without changing the surrogate. As a result the data in associated tables linked by the surrogate key now refers to the wrong person.

Neither of these situations can be detected from within the database, they need additional processing systems to detect the anomalies, or the risk of anomalies. There may have to be manual processes or additional programs. This additional processing makes the system as a whole less robust. That is my main objection to using surrogate keys unnecessarily. </quote>

>> If you absolutely cannot find one, use the sequence facilities
>> provided by your db. Now you are going to have to find some other way
>> to check for duplicate entries in your database. If there is no
>> sequence facility provided by your db, get a different db.

> I wish! DB2 as/400 dont have ID.

From what I can Google, DB2 has a sequence facility as of version 7.2. Look for CREATE SEQUENCE.

All the best on your project.

Alan Gutierrez -
Received on Mon Feb 10 2003 - 19:30:20 CET

Original text of this message