Re: Auto increment

"Tobin Harris" <comedyharris_at_hotmail.com> wrote in message news:b9og29$l7ptr$1_at_ID-135366.news.dfncis.de...
>
> "--CELKO--" <71062.1056_at_compuserve.com> wrote in message
> news:c0d87ec0.0305101930.65eb8cca_at_posting.google.com...
> > 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.

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
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 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
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?

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

>
> Tobin
>
>
Received on Tue May 13 2003 - 00:15:29 CEST