Creating BETTER auto-increment columns in Oracle
I was explaining to a developer today how Oracle doesn’t support auto-increment columns for IDs and how you have to implement your own. I wanted to point him to a tutorial but I found that most of the tutorials have PL/SQL code with unexplained side effects. So I thought I’d post some Oracle PL/SQL code that behaves just like a MySQL or PostgreSQL auto-increment column, but which also fixes the sequence number when an application provides its own ID number for a record, which is one place where MySQL and PostgreSQL autoincrement columns fail.
Step 1: Know the names of your tablespaces. Know which tablespace you want to use for your tables and which tablespace you want to use for your indexes. Don’t assume that Oracle is going to put the table in the right place. To get a list of available tablespaces:
select tablespace_name from user_tablespaces;
Step 2: Create your table. In this example the table is built in the tablespace TDATA and the primary key index is built in the tablespace INDX.
create table example (
id number not null,
name varchar2(30),
constraint example_pk primary key(id) using index tablespace INDX
) tablespace TDATA;
Note that by declaring the id column “not null” you can’t add a record with an empty ID field, ever.
Creating the primary key constraint on a separate line allows us to name the constraint, which can be handy when you’re looking through the data dictionary later on trying to figure out which constraint does what.
Step 3: Create a sequence. In this case I create an ID sequence that starts at 1 and goes on from there. If you want to start your ID sequence with a different number, or increment by some other amount, change these values.
create sequence example_id_seq start with 1 increment by 1;
Step 4: Create a trigger. This is what does the actual work setting the ID number. There are many ways to do this, here are two:
Method 1: Use a sequence-generated ID for all IDs.
create or replace trigger example_insert
before insert on example
for each row
begin
select example_id_seq.nextval into :new.id from dual;
end;
/
Method 1 will always use a sequence-generated ID no matter what. For example:
SQL> insert into example (id, name) values (100, 'Earl');
1 row created.
SQL> insert into example (name) values ('Cleopatra');
1 row created.
SQL> insert into example (name) values ('Frankenstein');
1 row created.
SQL> select * from example;
ID NAME
---------- ------------------------------
1 Earl
2 Cleopatra
3 Frankenstein
In this case the trigger-supplied ID 1 overwrote the user-supplied id 100.
Method 2: Allow users to supply their own IDs when they want to.
create or replace trigger example_insert
before insert on example
for each row
declare
max_id number;
cur_seq number;
begin
if :new.id is null then
-- No ID passed, get one from the sequence
select example_id_seq.nextval into :new.id from dual;
else
-- ID was set via insert, so update the sequence
select greatest(max(id), :new.id) into max_id from example;
select example_id_seq.nextval into cur_seq from dual;
while cur_seq < max_id
loop
select example_id_seq.nextval into cur_seq from dual;
end loop;
end if;
end;
/
Method 2 will use the sequence-generated ID if the insert statement doesn’t supply an ID, which is what MySQL and PostgreSQL do. It also updates the sequence so that the next value of the sequence won’t collide with IDs supplied by an insert statement.
For example, let’s say I’m using Trigger Method 2 and just loaded the table with a bunch of old data, including old ID numbers.
SQL> delete from example;
3 rows deleted.
SQL> insert into example (id, name) values (200, 'Cleopatra');
1 row created.
SQL> insert into example (id, name) values (300, 'Frankenstein');
1 row created.
SQL> insert into example (name) values ('Earl');
1 row created.
SQL> select * from example;
ID NAME
---------- ------------------------------
200 Cleopatra
300 Frankenstein
301 Earl
The last record added gets an ID of 301.
Hope you find this useful.
Ghulam Yassen Said,
May 7, 2009 @ 2:51 am
dear sir/madam
please tell me about data increment (autonumber) in oracel developer if the field is empty.
when i press the new button it gave auto number.
with many thanks
Ghulam Yassen
Sarwar Said,
May 28, 2009 @ 2:49 pm
Very good tutorial. I created auto-generated column in a table in few minutes following this tutorial.
Thanks
Earl Ruby Said,
June 2, 2009 @ 4:47 pm
You’re welcome!
Earl Ruby Said,
June 2, 2009 @ 5:07 pm
Ghulam:
What it sounds like you’re doing is creating a trigger that looks like the trigger in Method #1, which is the traditional way to make an Oracle field that behaves like an auto-number field. You can see the trigger you generated using the following SQL:
select TRIGGER_NAME, TABLE_NAME, TRIGGER_BODY from user_triggers;
– Earl
mona Said,
June 4, 2009 @ 11:43 am
thanks earl!
this works right up until i delete a row and then it doesnt pick up the increments(i.e. last insert before the delete was 5 so it should insert the next row as 6 but instead inserts it as 21). do u have any suggestions please!
Earl Ruby Said,
June 4, 2009 @ 12:00 pm
Mona: Could you post the series of SQL statements that you used when you saw this result?
mona Said,
June 4, 2009 @ 12:10 pm
earl,
thanks for the quick reply. im still online trying to trouble shoot this.
i used your exact statements (copy and pasted), i even created your example table to avoid any confusion and implemented the statements on that table. i am using oracle 10g XE if that makes a difference..
thanks!
Earl Ruby Said,
June 5, 2009 @ 4:38 pm
I actually meant the insert/delete statements you used. I wasn’t clear on when you were setting the ID and when you were letting the trigger set the ID.
If you ever set ID 20, the next record will have a 21. Deletions won’t affect this.
More likely what you’re seeing a side effect of the sequence cache value. Since Oracle uses a default sequence cache value of 20, if you were on #5 and you had a server failure the then the next number in the sequence could be 21. See http://www.techonthenet.com/oracle/sequences.php. I’m not sure what else you can do to exhaust the cache (restarting Oracle doesn’t do it), which is why I was asking about the insert/delete statements you used.
You might try recreating the sequence with the “nocache” option and see if you still see this behavior, although I wouldn’t recommend that if you’re working on a database with a large amount of transaction activity.
Rakesh Kumar Said,
July 15, 2009 @ 12:36 am
This suggestion is quite useful if we are inserting records in a single table. However, what would happen if the auto incrementing column is a primary key in the header table and the same is being referenced in the detail table. Also we want to insert records in both header and detail tables in one transaction.
Earl Ruby Said,
July 15, 2009 @ 11:09 am
Rakesh: The triggers still work exactly the same in the case you’re describing. What you want to do is to use the “returning” clause on your insert statement to return the primary key column’s new value, use that as the foreign key in your detail table, then commit after all of the records have been inserted (in a single transaction). Make sure that autocommit has been turned off in your session.
When I’m writing software I usually call a utility function to open the database connection and set up the initial session state so it’s always the same: autocommit off, NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’, etc. You can do the same thing with login.sql to ensure that your sqlplus sessions always start with a known state. If you do that and all parts of your application use the same “get database connection” utility function then your SQL statements will always execute with the environment in a known state.
Andy Said,
November 18, 2009 @ 5:05 am
how would i do this if my table already exists ? i cant be adding the id column since it is marked non-nullable.
Earl Ruby Said,
November 18, 2009 @ 11:37 am
Andy:
Add the ID column as nullable field.
Update the ID column with a unique ID value. (update tablename set id = rownum where id is null;)
Alter the table and make the ID column non-nullable.
Alter the table and add the unique constraint.
– Earl
Duncan Jack Said,
February 5, 2010 @ 10:03 am
Superb. Clearly written, very approachable.
Thank you.
Duncan