Re: Auto increment

From: Tobin Harris <>
Date: Mon, 12 May 2003 16:53:41 +0100
Message-ID: <b9og29$l7ptr$>

"--CELKO--" <> wrote in message
> One of the biggest errors is the IDENTITY column in the Sybase family
> (SQL Server and Sybase). People actually program with this "feature"
> and even use it as the primary key for the table! Now, let's go into
> painful details as to why this thing is bad.
> But now use a statement with a query expression in it, like this:
> <SNIP>

I'm not at expert, but there seem to be some strong arguments here (as to be expected from Celko!). We use autonumbers all the time at my shop, but it doesn't really 'feel' right. As you may expect, this argument doesn't really stand up at the meeting table, but over the last few months I've discovered a few things I don't like about autonumbers at a very practical level.

  1. Scripting Databases

For a current project, we decided it would be nice to be able to script the database creation/destruction, as well as the test data that is inserted. This gets much more complicated when you're working with auto numbers, because you suddenly have to reconcile foreign keys if the key values in a referenced table get updated. I suppose we could get around this by allowing identity inserts (?), but even so in many circumstances we want to be in control of the keys that certain things have, and so letting the database do this with autonumbers doesn't seem to make sense.

2. Removing and Reinserting Records

If we use autonumbers, it isn't possible to remove a record and then re-insert it with the same key (again, in SqlServer, you can use identity insert, but this comes with problems of it's own). This seems ridiculous really, in that the system prevents you from removing a record and then re-inserting it with the same key!

I think it's realistic to assume that database builders want to have the system help them allocate unique keys where necessary, but perhaps it would be helpful to have much more flexibility than the current vendors provide. For example, what if the database had a 'key engine' that could be programmed to generate meaningful or meaningless keys according to specific business rules?

Tobin Received on Mon May 12 2003 - 17:53:41 CEST

Original text of this message