Oracle 12c New Feature IDENTITY Columns

In Oracle 12c when we create a table we can populate a column automatically via a system generated sequence by using the GENERATED AS IDENTITY clause in the CREATE TABLE statement.

We can use GENERATED AS IDENTITY with the ALWAYS, DEFAULT or DEFAULT ON NULL keywords and that will affect the way or when the identity column value is populated.

By default the GENERATED AS IDENTITY clause implicitly includes the ALWAYS keyword i.e GENERATED ALWAYS AS IDENTITY.

When the ALWAYS keyword is specified it is not possible to explicitly include values for the identity column in INSERT OR UPDATE SQL statements.

SQL> create table emp
  2  (emp_id NUMBER GENERATED ALWAYS AS IDENTITY, ename varchar2(10));

Table created.

SQL> desc emp
Name                                                              Null?    Type
----------------------------------------------------------------- -------- --------------------------------------------
EMP_ID                                                            NOT NULL NUMBER
ENAME                                                                      VARCHAR2(10)


SQL> alter table  emp
  2  add constraint pk_emp primary key (emp_id);

Table altered.


We cannot explicitly enter a value for the identity column EMP_ID as that is generated automatically.

SQL> insert into emp
  2  values
  3  (1,'Bob');
insert into emp
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


SQL> insert into emp (ename)
  2  values
  3  ('Bob');

1 row created.

SQL> select * from emp;

    EMP_ID ENAME
---------- ----------
         1 Bob


Let us look at another example using this time the DEFAULT keyword


SQL> drop table emp;

Table dropped.

SQL> create table emp
  2  (emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY, ename varchar2(10));

Table created.


Unlike the previous case we can specify a value for the identity column. The identity column is only automatically populated if we do not provide a value for the identity column.

SQL> insert into emp
  2  values
  3  (1,'Bob');

1 row created.

SQL> insert into emp
  2  (ename)
  3  values
  4  ('Tom');

1 row created.


SQL> select * from emp;

    EMP_ID ENAME
---------- ----------
         1 Bob
         2 Tom


SQL>  insert into emp
  2  (ename)
  3  values
  4  ('Fred');

1 row created.


SQL> select * from emp;

    EMP_ID ENAME
---------- ----------
         1 Bob
         2 Tom
         3 Fred


SQL>  insert into emp
  2   values
  3  (4,'Jim');

1 row created.


SQL> insert into emp
  2  (ename)
  3    values
  4   ('Fred');
insert into emp
*
ERROR at line 1:
ORA-00001: unique constraint (SH.PK_EMP) violated  - WHY???


SQL> insert into emp
  2  (ename)
  3  values
  4  ('Tony');

1 row created.

SQL>  select * from emp;

    EMP_ID ENAME
---------- ----------
         1 Bob
         2 Tom
         3 Fred
         4 Tony



Try and insert a null value

SQL>  insert into emp
  2  values
  3  (null,'Jim');
(null,'Jim')
*
ERROR at line 3:
ORA-01400: cannot insert NULL into ("SH"."EMP"."EMP_ID”)


BY DEFAULT ON NULL clause ensures that initially the identity column will only be populated automatically if no value is supplied for the column and also if a null value is provided unlike the previous example


SQL> drop table emp;

Table dropped.

SQL> create table emp
  2  (emp_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, ename varchar2(10));

Table created.

SQL> insert into emp
  2  (ename)
  3  values
  4   ('Tom');

1 row created.


SQL>  insert into emp
  2  values
  3  (null,'Bob');

1 row created.

SQL> select * from emp;

    EMP_ID ENAME
---------- ----------
         1 Tom
         2 Bob



The sequence will have the prefix ISEQ$$ followed by the Object ID of the table.


SQL> select sequence_name from user_sequences;

SEQUENCE_NAME
------------------------------------------------------------------------------------------------------------------------
ISEQ$$_93421


There is a new view called *_TAB_IDENTITY_COLS and the *_TABLES view has a new column HAS_IDENTITY


SQL>  select table_name, column_name, generation_type,identity_options
2  from user_tab_identity_cols where sequence_name='ISEQ$$_93421';

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
GENERATION
----------
IDENTITY_OPTIONS
------------------------------------------------------------------------------------------------------------------------
EMP
EMP_ID
BY DEFAULT
START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, OR
DER_FLAG: N


SQL> select has_identity from user_tables where table_name='EMP';

HAS
---
YES