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 ++ 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