Auto-increment primary key in Pre 12c releases (Identity functionality)

Tags

autoincrement, autonumber, identity column, primary key, sequence, trigger

Oracle 12c has introduced the IDENTITY COLUMN functionality which provides an automatic functionality of a trigger-sequence approach for populating the primary key of a table. However, for Pre 12c releases, developers need to stick to the old trigger-sequence approach.

TABLE

SQL> CREATE TABLE t (
  2    ID           NUMBER(10)    NOT NULL,
  3    text  VARCHAR2(50)  NOT NULL);

Table created.

SQL>

PRIMARY KEY to be populated by the sequence

SQL> ALTER TABLE t ADD (
  2    CONSTRAINT id_pk PRIMARY KEY (ID));

Table altered.

SQL>

SEQUENCE to support the primary key

SQL> CREATE SEQUENCE t_seq
  2  START WITH 1000
  3  INCREMENT BY 1;

Sequence created.

SQL>

TRIGGER If you do not want to have the sequence in the INSERT , you could automate it via TRIGGER.

SQL> CREATE OR REPLACE TRIGGER t_trg
  2  BEFORE INSERT ON t
  3  FOR EACH ROW
  4  WHEN (new.id IS NULL)
  5  BEGIN
  6    SELECT t_seq.NEXTVAL
  7    INTO   :new.id
  8    FROM   dual;
  9  END;
 10  /

Trigger created.

SQL>

INSERT

SQL> INSERT INTO t(text) VALUES('auto-increment test 1');

1 row created.

SQL> INSERT INTO t(text) VALUES('auto-increment test 2');

1 row created.

SQL>

Let’s see if we have the ID column auto-incremented with the desired values-

SQL> SELECT * FROM t;

   ID TEXT
----- --------------------------------------------------
 1000 auto-increment test 1
 1001 auto-increment test 2

SQL>

So, the ID column now starts with value 1000 and increments by 1 with subsequent inserts.

Hope it helps!