Identity Columns Feature in Oracle 12c

Identity Columns Feature in Oracle 12c

Before Oracle 12c there was no direct equivalent of the Auto Number or Identity functionality, when needed it will implemented using a combination of sequences and triggers. The oracle 12c database introduces the ability to define an identity clause for a table column defined using a numeric type.
The Syntax:
GENERATED
[ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ (identity_options ) ]

Using 'ALWAYS' keyword will force the use of the identity. In that case if an insert statement references
the identity column, even you specify a NULL value, an error is occured.
CREATE TABLE payslip_master (
  empid NUMBER GENERATED ALWAYS AS IDENTITY,  latin_name Varchar2(50));

Insert into payslip_master (latin_name) values('SADHAN_001');
Insert into payslip_master (latin_name) values('SADHAN_002');
SQL> select * from cust_master;
     EMPID LATIN_NAME
---------- --------------------
         1 SADHAN_001
         2 SADHAN_002

SQL> Insert into cust_master (empid,latin_name) values(101,'ISSCO_003');
Insert into payslip_master (empid,latin_name) values(101,'ISSCO_003')
               *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

Using BY DEFAULT allows you to use the identity if the column isn't referenced in the insert statement,
but if the column is referenced, the specified value will be used in place of the identity. In that case if you try
to specify the value NULL then an error occured, since identity columns are always NOT NULL.

CREATE TABLE payslip_master(
  empid NUMBER GENERATED BY DEFAULT AS IDENTITY, latin_name Varchar2(50));

Insert into payslip_master (latin_name) values('SADHAN_001');
Insert into payslip_master (empid,latin_name) values(40,'SADHAN_002');
SQL> select * from payslip_master;
     EMPID LATIN_NAME
---------- --------------------
         1 SADHAN_001
        40 SADHAN_002

Using BY DEFAULT ON NULL allows the identity to be used even when the identity column is referenced and NULL value is specified.
CREATE TABLE payslip_master(
EMPID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,  latin_name varchar2(50));

Insert into payslip_master (empid,latin_name) values(null,'SADHAN_001');
Insert into payslip_master (empid,latin_name) values(40,'SADHAN_002');
Insert into payslip_master (latin_name) values('SADHAN_003');
SQL> select * from payslip_master;
     EMPID LATIN_NAME
---------- --------------------
         1 sadhan_001
        40 sadhan_002
         2 sadhan_003

As we know oracle uses a sequence internally to generate value to populate the identity column.