There's plenty of DB's that support AUTOINCREMENT (or some variant of that word) to generate a unique primary key.

But what about for those cases where I might want to know the key in advance of the row being inserted?

Or perhaps I want a GUID type situation, where the keys are not only unique within their table, but globally unique.

Perhaps I just don't want to use AUTOINCREMENT


The sloppy (?) way is to start a transaction, select max(pk) + 1, insert a row with the result, and end the transaction. Does this *always* guarantee uniqueness? I vaguely recall there was a way to do select AND update the table in one query (in Oracle). Is there a way to do this that is database-vendor neutral?

Other ideas? Best practices?

Thanks.

The max(pk)+1 approach doesn't work in a multi-user environment, at least not efficiently. You and I both select max(pk)+1 at around the same time, and both get same value. One of our inserts then fails. You can trap that and try again, but it isn't exactly efficient. Or you can use a serialising locking mechanism that only allows one person to insert a record at a time, again sacraficing performance.

Oracle's alternative to AUTOINCREMENT columns is the Sequence. This guarantees unique values without serialising inserts. You can use a sequence per table (usual approach), or a single sequence shared by all tables - which is not always ideal, because the sequence can become a slight performance bottleneck. A typical usage would be:

insert into mytable( id, .... )
values ( mytable_seq.NEXTVAL, ... )
returning id into v_id;

The "returning" clause gives you the new ID value back after the insert.
If you really needed to know the value before inserting you would have to do this:

select mytable_seq.NEXTVAL into v_id from dual;
insert into mytable( id, .... ) values ( v_id, ... );

Of course, all this is entirely vendor-specific! Probably the best you can do to be vendor-neutral is to hide your unique key generation code in a function, e.g.:

v_id := unique_key_for('MYTABLE');
insert into mytable( id, .... ) values ( v_id, ... );

Now all you have to do is rewrite the function for each vendor.

People tend to overuse fields with auto generated values, probably because at first sight they seem such a simple and convenient solution for generating unique key values. But experience taught me they are useful only when you are 100% sure that the genarated value will never ever have any significance for the user. Very often, you don't know that in advance.

So, for me the best solution is, as the previous poster suggested, to roll my own key values. For each table in the database I need to generate a unique key value I create a row in a special 'keys' table. As this table has always the same name and structure in all my projects, I wrapped the key generating function in a class that I reuse wherever possible.

This schema does introduce a small performance penalty, but for a reasonable number of concurrant users, the loss in speed won't be noticeable.