The Oracle (tm) Users' Co-Operative FAQ

How do you create an 'auto number' or 'auto increment' column similar to the offering from Microsoft's SQL Server ?


Author's name: Jonathan Lewis, Marco Coletti

Author's Email: Jonathan@jlcomp.demon.co.uk, marco.coletti@telecomitalia.it

Date written: 7th Aug 2001

Addendum: 20th May 2002, 13th Oct 2005

Oracle version(s): 7.3 - 8.1.7.0, 9.2

A common question for users converting from MS SQL Server to Oracle is how to handle 'auto increment' or 'auto number; columns. A feature of MS SQL server for handling (typcially) primary key columns which have to be populated with meaningless sequence numbers. There is no direct equivalent in Oracle, but the combination of sequences and triggers comes close.

Marco Coletti: Another option available in Oracle version 9.2 is to use the built in SQL function sys_guid() for the default column value. (13th Oct 2005)

Back to index of questions


From the more detailed descriptions of this question it seems that SQL Server allows a numeric column to be defined with a 'default' mechanism that generates a new sequential number every time a new row is inserted into the table. I do not know if this number is lost if a transaction rolls back or if there is some mechanism to resurrect such numbers. I presume the number generated is there to act as a meaningless unique key.

There is no exact equivalent in Oracle. Default values are limited to constants, or 'pseudo-constants' such as the current user id, current date and time. There is, however, a mechanism that is similar in effect to the SQL Server autonumber. This mechanism requires the creation of a sequence and a trigger.

A sequence is an Oracle object that can be used outside the scope of a transaction as a high-speed mechanism for generating a sequence of numbers. This sequence of numbers may be cyclic, can be ascending or descending, can be bounded or unbounded, and can have a limit. You may want to read a reference document about sequences (based on Oracle 7.3) before you continue.

A trigger is a pre-compiled piece of PL/SQL code that executes when certain actions take place that meet required conditions. Under Oracle 7, triggers could only be associated with DML statements made against tables, but the range of options has expanded under Oracle 8.

An 'autonumber' column then needs you to do two things. Create a sequence as the source of the numbers, that you will store in the column, and create a trigger, attached to the table, which fires every time you insert a row; the trigger will get the next available sequence number and write it into the column. A code sample follows:


Sample of using Sequences and Triggers

create table auto_numb(
        n1             number  primary key,
        v1             varchar2(20)
)
;
 
create sequence auto_seq;
 
create trigger aut_bri
before insert on auto_numb
for each row
begin
        select auto_seq.nextval into :new.n1 from dual;
end;
/
 
 
rem
rem     Note that the trigger ALWAYS fires, so if the users supplies their own 
rem     value for the key column, then this will be overwritten by the trigger.
rem
 
 
insert into auto_numb (v1) values ('asdf');
insert into auto_numb (n1, v1) values (88,'qwerty');
select * from auto_numb;
 
       N1 V1
--------- --------------------
        1 asdf
        2 qwerty
 

To avoid this issue, you will probably want to enhance the trigger to raise an error if the incoming primary key column is not null, for example:

begin
        if (:new.n1 is not null) then
               raise_application_error(
                       -20001, 'I supply the keys, not you'
               );
        end if;
        select auto_seq.nextval into :new.n1 from dual;
end;

This may not solve all your problems, of course.

If you are planning to use the primary key from this table as a foreign key to another table, how do you get the value back to your application ? One option, of course is to forget about the trigger and simply select the value into a local variable. Another is to use a new feature of Oracle 8, the RETURNING clause, possibly eliminating one reound-trip between the client and server. For example, in SQL*Plus:


Using the RETURNING clause in SQL*Plus

variable m_ret number
 
insert into auto_numb (v1) values ('asdf')
returning n1 into :m_ret;
 
print m_ret
 
    M_RET
---------
        3
 

One thing you should definitely NOT do is to create a trigger on the child table and try to use the currval feature of sequences to populate the foreign key column. It may seem cute and quick at first sight as a way of getting a parent and its children into the database in a single group, but since the trigger always fires what do you do when you want to insert further child rows some time later ?

Remember too the special threat of web-based applications. Web-based applications are essentially state-less. This means that a front-end could run two statements that appear to be related, but which are presented to the application server as two separate dialogues. If this happens, then it is quite possible that the application server has not maintained a session between the calls. Depending on the architecture of the application server, there are three possible results, (1) you get lucky, (2) you will get an error message saying that you can't call currval before calling nextval; or (3) you will attach yourself to a completely random session and get whatever value it happened to get the last time it acquired nextval.

Note: Only the owner of the table needs to be able to see the sequence.

Note: Watch out for anything that may disable the trigger.


Addendum (Marco Coletti 13th Oct 2005)

A more exact equivalent in Oracle for the SQL Server autonumber feature (although the generated unique values are not numbers) requires to impose to the key column a default value with an expression involving the SQL function sys_guid(), which is available from version 9.2 onwards.

Oracle9i SQL Reference
Release 2 (9.2)

...
SYS_GUID
generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier and a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.
...

A code sample follows:


Sample of using sys_guid() for the key column default value

create table auto_key(
        id             raw(6) default hextoraw(substr(sys_guid(),1,12)) primary key,
        v1             varchar2(20)
)
;
 
rem
rem     Note that if the users supplies their own value for the key column,
rem     then this will not be overwritten by the default value.
rem
 
 
insert into auto_key (v1) values ('asdf');
insert into auto_key (id, v1) values (hextoraw('CAFE'),'qwerty');
select * from auto_key;
 
ID           V1
------------ --------------------
0300A920CE9E asdf
CAFE         qwerty

Perhaps you noticed that in the preceding code snippet the raw(16) value returned by sys_guid() is truncated to raw(6). This is to economize on space, since I observed that on the same Oracle instance the last 10 bytes do not change. This behaviour is not guaranteed, but is confirmed by an informal test involving the concurrent insert of about 4 million rows from two distinct sessions.

Your application may be more safe using the whole raw(16) value:

create table auto_key(
        id             raw(16) default sys_guid() primary key,
        v1             varchar2(20)
)
;

Further reading: Sequences (an article by Jonathan Lewis, based on Oracle 7.3) .

This question is also addressed by the following documents:

Author

Title/URL

Suggested by

Referee's comments

Howard Rogers

Is there a feature in Oracle like the autonumber in Access

Jonathan Lewis

One of several “discussion documents” on the Dizwell website that take the time and space to examine Oracle features properly.

 

 

 

 


Back to top

Back to index of questions