Oracle 12c Database Identity Columns

oracle 12C 中新特性： Identity Columns ，相当于mysql auto_incream

创建的方法：

Column_Name Number (Data Type)
GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ]

下面建表进行测试：

SQL> create table idm_test (
  2  id number generated ALWAYS as identity,
  3  name varchar2(20));

Table created.

SQL>  insert into idm_test(name) values('John');

1 row created.

SQL> select * from idm_test ;

        ID NAME
---------- --------------------
         1 John

SQL>  insert into idm_test(name) values('huang');          

1 row created.

SQL>  select * from idm_test ;

        ID NAME
---------- --------------------
         1 John
         2 huang

SQL> insert into idm_test(id,name) values(3,'John');
insert into idm_test(id,name) values(3,'John')
                     *

这种情况下不能指定 id 字段的值。如果想手动指定 id 字段的值 ，则需要改成 BY DEFAULT 模式。

但是当不指定 id 的值时，他的值会是上次生成的值加 1 。另外也不能插入 null 值。

 

SQL>  alter table idm_test modify id generated by default as identity;

Table altered.

SQL> insert into idm_test(id,name) values(3,'John')
  2  ;

1 row created.

SQL> select * from idm_test;

        ID NAME
---------- --------------------
         1 John
         2 huang
         3 John

SQL> insert into idm_test(id,name) values(5,'bin');

1 row created.

SQL> select * from idm_test;

        ID NAME
---------- --------------------
         1 John
         2 huang
         3 John
         5 bin

SQL> insert into idm_test(name) values('bi');

1 row created.

SQL> insert into idm_test(id,name) values(null,'b');
insert into idm_test(id,name) values(null,'b')
                                     *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."IDM_TEST"."ID")


SQL> select * from idm_test;

        ID NAME
---------- --------------------
         1 John
         2 huang
         3 John
         5 bin
         3 bi

相关视图：

 

SQL> select object_name,object_type from user_objects where object_name='IDM_TEST';

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
IDM_TEST
TABLE