How To Add Auto Increment In Oracle- IDENTITY column in Oracle 12c

Lets create employee table for this purpose.

SQL> CREATE TABLE EMP01

(

EMPID NUMBER,

NAME VARCHAR2(50),

DEPT VARCHAR2(20)

);

Next steps is to create oracle sequence to generated the id values.

SQL> CREATE SEQUENCE EMPID_SEQUENCE

START WITH 1

INCREMENT BY 1;

Next Step is to create Trigger to assign the values from sequence to EMPID column.

CREATE OR REPLACE TRIGGER EMPID_TRIGGER

BEFORE INSERT ON EMP01

FOR EACH ROW

BEGIN

SELECT EMPID_SEQUENCE.nextval INTO :NEW. EMPID  FROM dual;

END;

/

Now we will try insertng few values:

SQL> INSERT INTO EMP01 (NAME, DEPT) VALUES ('RON',’ABC’);

1 row created.

SQL> INSERT INTO EMP01 (NAME, DEPT) VALUES ('VICTORIA',’XYZ’);

SQL> SELECT * FROM EMP01;

EMPID NAME DEPT

---------- ------------------------------

1 RON ABC

2 VICTORIA XYZ

You can find that the EMPID getting incremented by 1.
Now there is a new feature available in on Oracle 12c version:IDENTITY column using which we can implement the same auto increment feature.