AutoNumber and Identity columns

Most databases support autonumber or identity columns. While Oracle doesn't directly support this feature, it can be simulated in Oracle.

Other databases

For example, Microsoft SQL Server developers can create "Identity" primary key columns and MS Access users can create "AutoNumber" columns. Here is an MS-SQL example:

CREATE TABLE tab1 ( id INT IDENTITY(1,1) PRIMARY KEY );

Similarly, MySQL offers AUTO_INCREMENT columns and PostgreSQL supports SERIAL and BIGSERIAL column types.

Oracle solution

While Oracle supports sequences for generating primary key values, SEQUENCES are not tied to a particular column in a table. To get the same effect in Oracle, one can create a TRIGGER to automatically assign sequence values to a column. At first, this sounds cumbersome. However, it is as effective, and a good deal more flexible too.

Look at this example:

rem Create a table and a sequence for generating key values:

SQL> CREATE TABLE tab1 (
  2    id  NUMBER PRIMARY KEY,
  3    val VARCHAR2(30)
  4  );
Table created.

SQL>
SQL> CREATE SEQUENCE tab1_id_seq;
Sequence created.

SQL>
SQL> INSERT INTO tab1(id, val) VALUES (tab1_id_seq.nextval, 'row1');
1 row created.

SQL>
SQL> SELECT * FROM tab1;
        ID VAL
---------- ------------------------------
         1 row1

Now, let's automate this using a trigger to simulate AutoNumber/Identity functionality:

SQL> CREATE OR REPLACE TRIGGER tab1_trg
  2    BEFORE INSERT ON tab1
  3    FOR EACH ROW
  4  BEGIN
  5    SELECT tab1_id_seq.nextval INTO :new.id FROM dual;
  6  END;
  7  /
Trigger created.

SQL> SHOW ERRORS
No errors.

SQL>
SQL> INSERT INTO tab1(val) VALUES ('row2');
1 row created.

SQL> INSERT INTO tab1(id, val) VALUES (null, 'row3');
1 row created.

SQL> SELECT * FROM tab1;
        ID VAL
---------- ------------------------------
         1 row1
         2 row2
         3 row3