This new Oracle 12C feature enables to define auto numbering for a column. Only one column can be defined as identity.

It is recommended to read about other extensions in 12C for default column values

• Default Values for Columns Based on Oracle Sequences
• Default Values for Columns on Explicit NULL Insertion

identity_options – defines sequence generator. It creates a sequence in database that is later used to generate values for identity column.

There are three types of identity that can be created

• GENERATED ALWAYS AS IDENTITY the same as GENERATED AS ALWAYS
• GENERATED BY DEFAULT AS IDENTITY
• GENERATED BY DEFAULT ON NULL AS IDENTITY

GENERATED ALWAYS AS IDENTITY

Following rules are applied for identity column values during INSERT

• DEFAULT clause – value is generated from identity sequence
• NOT NULL           – value is not accepted error is raised
• NULL                    – value is not accepted error is raised

create table test_tbl1
( id  number generated always as identity,
  id1 number );

--default example
insert into test_tbl1 values(default, 1);
insert into test_tbl1 (id1) values(2);

--not null example
insert into test_tbl1 values(6, 3);
--SQL Error: ORA-32795: cannot insert into 
--a generated always identity column

--null example
insert into test_tbl1 values(null, 4);
--01400. 00000 -  "cannot insert NULL into (%s)"

select * from test_tbl1;

        ID        ID1
---------- ----------
         1          1 
         2          2

GENERATED BY DEFAULT AS IDENTITY

Following rules are applied for identity column values during INSERT

• DEFAULT clause – value is generated from identity sequence
• NOT NULL           – value is accepted
• NULL                    – value is not accepted error is raised

create table test_tbl2
( id  number generated by default as identity,
  id1 number );

--default example
insert into test_tbl2 values(default, 1);
insert into test_tbl2 (id1) values(2);

--not null example
insert into test_tbl2 values(6, 3);

--null example
insert into test_tbl2 values(null, 4);
--01400. 00000 -  "cannot insert NULL into (%s)

select * from test_tbl2;

        ID        ID1
---------- ----------
         1          1 
         2          2 
         6          3

GENERATED BY DEFAULT ON NULL AS IDENTITY

Following rules are applied for identity column values during INSERT

• DEFAULT clause – value is generated from identity sequence
• NOT NULL           – value is accepted
• NULL                    – value is accepted

create table test_tbl3
( id  number generated by default on null as identity,
  id1 number );

--default example
insert into test_tbl3 values(default, 1);
insert into test_tbl3 (id1) values(2);

--not null example
insert into test_tbl3 values(6, 3);

--null example
insert into test_tbl3 values(null, 4);

select * from test_tbl3;

        ID        ID1
---------- ----------
         1          1 
         2          2 
         6          3 
         3          4

Sequence generator

identity_options is the same as for sequence

Each time identity column is created new sequence is created in database. Such sequences can’t be drop.

create table test_tbl
(id  number generated as identity (start with 1 increment by 2));

select table_name, column_name, data_default 
from user_tab_columns where table_name like 'TEST_TBL%'
 and column_name='ID';

TABLE_NAME  COLUMN_NAME  DATA_DEFAULT
----------- ------------ -------------
TEST_TBL    ID           "TOMASZ"."ISEQ$$_92553".nextval
TEST_TBL1   ID           "TOMASZ"."ISEQ$$_92550".nextval
TEST_TBL2   ID           "TOMASZ"."ISEQ$$_92551".nextval
TEST_TBL3   ID           "TOMASZ"."ISEQ$$_92552".nextval

drop sequence "TOMASZ"."ISEQ$$_92562";
--ORA-32794: cannot drop a system-generated sequence

Views

New views are available for identity columns DBA|ALL|USER_TAB_IDENTITY_COLS

select * from user_tab_identity_cols
where table_name like 'TEST_TBL%';

TABLE_NAME  COLUMN_NAME  GENERATION_TYPE  IDENTITY_OPTIONS
----------- ------------ ---------------- -----------------
TEST_TBL  ID ALWAYS  START WITH: 1, INCREMENT BY: 2, 
                     MAX_VALUE: 9999999999999999999999999999, 
                     MIN_VALUE: 1, CYCLE_FLAG: N, 
                     CACHE_SIZE: 20, ORDER_FLAG: N
TEST_TBL1 ID ALWAYS  START WITH: 1, INCREMENT BY: 1, 
                     MAX_VALUE: 9999999999999999999999999999, 
                     MIN_VALUE: 1, CYCLE_FLAG: N, 
                     CACHE_SIZE: 20, ORDER_FLAG: N
TEST_TBL2 ID BY DEFAULT START WITH: 1, INCREMENT BY: 1, 
                        MAX_VALUE: 9999999999999999999999999999, 
                        MIN_VALUE: 1, CYCLE_FLAG: N, 
                        CACHE_SIZE: 20, ORDER_FLAG: N
TEST_TBL3 ID BY DEFAULT START WITH: 1, INCREMENT BY: 1, 
                        MAX_VALUE: 9999999999999999999999999999, 
                        MIN_VALUE: 1, CYCLE_FLAG: N, 
                        CACHE_SIZE: 20, ORDER_FLAG: N

Have a fun 🙂

Tomasz