How to create an auto increment column

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. A common question for users converting from MS SQL Server to Oracle is how to handle 'auto increment' columns. Primary key columns can be easily populated if this feature is available in oracle. There is no direct equivalent in Oracle, but the combination of sequences and triggers comes close.

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.

A trigger is a pre-compiled piece of PL/SQL code that executes when certain actions take place that meet required conditions. 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:

Create Sequence and Trigger

SQL> create sequence employee_seq start with 1 increment by 1;

Sequence created.

SQL> create or replace trigger employee_trg
  2  before insert on employee
  3  for each row
  4  BEGIN
  5  select employee_seq.nextval INTO :new.empno from dual;
  6  END;
  7  /

Trigger created.


SQL> insert into employee(ename,job, sal,deptno) values('Tom', 'Manager', 4500, 20);

1 row created.

SQL> select empno from employee where ename='Tom';

     EMPNO
----------
         1

Using the RETURNING clause in SQL*Plus

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 is to simply select the value into a local variable. Another is to use the RETURNING clause, possibly eliminating one reound-trip between the client and server. For example, in SQL*Plus:

SQL> var v_empno number
SQL> insert into employee(ename,job, sal,deptno) values('Mike', 'Manager', 4000, 30) 
  2  returning empno into :v_empno;

1 row created.

SQL> print :v_empno;

   V_EMPNO
----------
         2