This article is about how not to create auto increment columns. If you’re looking for a more acceptable way, you’ll want to check out this article.

Matt has posted some code on his blog for accomplishing an auto increment in Oracle. While his solution may seem like an easy way out of creating triggers and sequences, it will fall apart in practical use.

Here’s what Matt suggested:

INSERT INTO table (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, ‘bork’ FROM table);

Basically he is taking the max value currently in the ID column of the table and adding one for the new ID value. Matt mentions on his site that he is not sure how efficient this would be. While it wouldn’t be very efficient, it will probably not be too slow if the ID column has an index on it (which it will automatically have if it is a primary key.)

The bigger problem will come when you have multiple users connected. To illustrate that we’ll create a small test table:

SQL> CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));
Table created.

Now let’s insert a couple rows in the table:

SQL> INSERT INTO test (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, 'bork' FROM test);1 row created.

INSERT INTO test (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, 'Jon' FROM test);

1 row created.

SQL> select * from test;

ID NAME
---------- ------------------------------
1 bork
2 Jon

OK, so far so good, the incrementing works. Now let’s open a second session to the database and insert a row.

Session 2
SQL> INSERT INTO test (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, 'Matt' FROM test);

This session hangs! If you dig into the data dictionary (or Enterprise Manager) you will find that the first session has a lock on the table that is blocking the second transaction! So session 2 is waiting for session 1 to commit. Let’s do that and see what happens.

Session 1
SQL> commit;Commit complete.

Session 2
INSERT INTO test (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, 'Matt' FROM test)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C002948) violated

Now we see the bigger problem. Since the subquery is evaluated before session 1 commits the subquery which is incrementing the ID value based on the MAX(id) is grabbing old data.

So sorry Matt, there’s no bulletproof way around using triggers and sequences for this.

database, database administration, dba, database development, oracle, sql, plsql, pl/sql