Oracle 12c New Features - Identity column

SQL> create table t1(id number generated always as identity, item_desc varchar2(100));

Table created.

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


SQL> insert into t1 values (null,'d1');
insert into t1 values (null,'d1')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


SQL> insert into t1 values (default,'d1');

1 row created.

SQL>  insert into t1(item_desc) values ('d2');

1 row created.

SQL> commit;

Commit complete.


SQL> select * from t1;

        ID ITEM_DESC
---------- --------------------
         1 d1
         2 d2

SQL> drop table t1 purge;

Table dropped.


SQL> create table t1(id number generated by default on null as identity, item_desc varchar2(100));

Table created.

SQL> insert into t1 values (1,'d1');

1 row created.

SQL> insert into t1 values (null,'d1');

1 row created.

SQL> insert into t1 values (default,'d1');

1 row created.

SQL> insert into t1(item_desc) values ('d2');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID ITEM_DESC
---------- --------------------
         1 d1
         1 d1
         2 d1
         3 d2

SQL> drop table t1 purge;

Table dropped.


SQL> create table t1(id number generated by default  as identity (start with 10 increment by 11), item_desc varchar2(100));

Table created.

SQL> insert into t1 values (1,'d1');

1 row created.

SQL> insert into t1 values (null,'d1');
insert into t1 values (null,'d1')
                       *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("DONGHUA"."T1"."ID")


SQL> insert into t1 values (default,'d1');

1 row created.

SQL> insert into t1(item_desc) values ('d2');

1 row created.

SQL> commit;

Commit complete.

SQL>  select * from t1;

        ID ITEM_DESC
---------- --------------------
         1 d1
        10 d1
        21 d2

SQL> select * from user_sequences;

SEQUENCE_NAME
--------------------------------------------------------------------------------
 MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER PARTITION_COUNT S
---------- ---------- ------------ - - ---------- ----------- --------------- -
K
-
ISEQ$$_93128
         1 1.0000E+28           11 N N         20         230                 N
N

SQL> select * from user_tab_identity_cols;

TABLE_NAME
--------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
GENERATION
----------
IDENTITY_OPTIONS
--------------------------------------------------------------------------------
T1
ID
BY DEFAULT
START WITH: 10, INCREMENT BY: 11, MAX_VALUE: 9999999999999999999999999999, MIN_V
ALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N