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.

Identity columns can be specified in oracle table which will generate value by itself. Oracle implicitly creates a sequence and assigns it to the identity column for each insert.

Syntax: 

GENERATED

[ ALWAYS | BY DEFAULT [ ON NULL ] ]

AS IDENTITY [ ( identity_options ) ]

CREATE TABLE EMP01

(

EMPID NUMBER GENERATED BY DEFAULT AS IDENTITY,

NAME VARCHAR2(50),

);

A sequence is created automatically by Oracle when the table is created. default will use identity if the column is not referenced in the insert statement, if it’s referenced then the value specified will be used.

SQL> insert into EMP01 (NAME) values ('ROY');

SQL> select * from EMP01;

     EMPID NAME

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

       1 ROY

>>>>>>>   Mail @dwhlaureate@gmail.com for any queries    <<<<<<<<<