ORACLE GENERATED ALWAYS AS IDENTITY

Lets create a new table with Oracle 12c New feature Identity column.

CREATE TABLE TEST_ID
(
  ID NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL
, FNAME VARCHAR2(20)
, LNAME VARCHAR2(20)
, CONSTRAINT TEST_ID_PK PRIMARY KEY
  (
    ID
  )
  ENABLE
);

Table TEST_ID created.


INSERT INTO "PDBORCL1_USR2"."TEST_ID" (FNAME, LNAME) VALUES ('test', 'me');
INSERT INTO "PDBORCL1_USR2"."TEST_ID" (FNAME, LNAME) VALUES ('and', 'me');

Commit Successful;

Oracle generate the PK values for us. We no longer have to worry about auto increment.Leaving everything in oracle hands.

select id,FNAME,LNAME from TEST_ID;

        ID FNAME                LNAME            
---------- -------------------- --------------------
         1 test                 me                
         2 and                  me  


In the Background Oracle creates the a sequence & uses it to generate the id for this column and auto populates these id's.

select sequence_name from user_sequences;

SEQUENCE_NAME                                                                
--------------------------------------------------------------------------------
ISEQ$$_91816