Re: automatically calculating ID field on INSERT

Ruud de Koter <ruud_dekoter_at_hp.com> writes:
> William Boyle wrote:
> > Joe Celko wrote:
> > > Here is the SQL-92 version:
> > >
> > > INSERT INTO MyTable (id, a, b, c, ...)
> > > VALUES (COALESCE(SELECT MAX(id) FROM MyTable) + 1, 1),
> > > new_a, new_b, new_c, ..);
> > [...]
> > IdTable has 2 columns: tablename (char or varchar) and id (number or
> > int)
> >
> > update IdTable set id = id + 1 where tablename = 'MyTable';
> > insert into MyTable (id, a, b, c, ...)
> > values ((select id from IdTable where tablename='MyTable'),
> > new_a, new_b, newc, ...);
> > commit;
> >
>
> Though this approach will work in a lot of situations, is main
> drawback is that in the end it lacks scalability. For every insert
> being done the IdTable will be locked, and other transactions
> will have to wait till the transaction commits.

In most applications, it isn't necessary for IDs to be sequential and without gaps.

A good solution is therefore to preallocate a range of IDs to each client. Each client just consumes its range of IDs as it creates new records, and when they are used up, it allocates a new range of IDs.

Even simpler, you can pick just pick a random 32 bit or 64 bit number as an ID on insert; if the ID is a primary key, if you accidentally pick an existing ID, you will get an error from the database and you simply try the insert again with another random number. If you use a good random number generator, you get the additional advantage that IDs cannot be forged easily (a problem with social security numbers, as well as most employee id numbers, which are allocated sequentially).

That kind of approach is going to be faster than any of the SQL-based solutions and it will work even with simple relational databases like MySQL. Furthermore, in many applications, the client that is doing the insert actually needs to know the ID that it just allocated, something that neither of the methods suggested by Celko or Boyle addresses (although they could be patched up to handle that case).

Tom. Received on Fri Jun 23 2000 - 00:00:00 CEST