Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Auto increment

Re: Auto increment

From: D Guntermann <>
Date: Mon, 12 May 2003 22:15:29 GMT
Message-ID: <>

"Tobin Harris" <> wrote in message news: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
> 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
> stand up at the meeting table, but over the last few months I've
> 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
> 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.

Have you considered the ON UPDATE CASCADE referential qualifier?

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
> 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 won't argue with you on the IDENTITY data type as a key, but what's stopping you from remembering the original key value from an auto-number generation and re-using it as a value for the key upon reinsertion of the same tuple? Certainly, if the auto-generation mechanism is defined as a DEFAULT value, then the user asserting his or her own value for the key will supercede the default behavior?
> 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
> 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
> business rules?

Hear! Hear! Of course this would imply procedural extensions for business-context-sensitive key generation.

> Tobin
Received on Mon May 12 2003 - 17:15:29 CDT

Original text of this message