AutoNumber and Identity columns

From Oracle FAQ
Jump to: navigation, search

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

[edit] 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.

[edit] 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