在12c的新特性中，自增长字段是个亮点，用自增长字段做主键就方便多了，MYSQL和SQL SERVER很早就有这样类型的字段了，ORACLE直到12C才整出自增长字段。本案例主要测试自增长字段、用序列做字段的默认值和用序列填充空值几个新特性，由于这三个新功能ORACLE总结到了一起叫Identity Columns，就一起测试下，下面是官方文档对自增长字段的介绍。

Identity Columns

Oracle Database 12c Release 1 implements ANSI-compliant IDENTITY columns. Migration from database systems that use identity columns is simplified and can take advantage of this new functionality.

This feature implements auto increment by enhancing DEFAULT or DEFAULT ON NULL semantics for use by SEQUENCE.NEXTVAL and SYS_GUID, supports built-in functions and implicit return of default values.

Example 1-1 creates a table with an identity column, which generated by default. When explicit nulls are inserted into the identity column, the default behavior is to use the sequence generator. For further details, see Oracle Database SQL Language Reference.

在12c的官方文档的create table语法不分可以看到自增长字段的语法介绍。

identity_clause::=

identity_options::=

下面测试下自增长字段。

首先创建一张GENERATED ALWAYS属性的自增长字段。

SQL> create table iden_test(id number GENERATED ALWAYS AS IDENTITY,name varchar2(10));
Table created.

官方文档也介绍了，自增长字段是调用的序列，那么能否查到这个序列呢？

SQL> select object_name,object_type from user_objects;
OBJECT_NAME  OBJECT_TYPE
------------ -----------
ISEQ$$_91615 SEQUENCE
IDEN_TEST    TABLE

ORACLE自动创建了名字叫ISEQ$$_91615的序列，那看看这个序列的属性是什么样的。

SQL> select SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER from user_sequences
SEQUENCE_NAM  MIN_VALUE  MAX_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------ ---------- ---------- ------------ ---------- -----------
ISEQ$$_91615          1 1.0000E+28            1         20           1

可以看到这个序列的属性都是默认值，从IDEN_TEST表的元数据也能看到这个序列的属性。

SQL> select dbms_metadata.get_ddl('TABLE','IDEN_TEST') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','IDEN_TEST')
--------------------------------------------------------------------------------
  CREATE TABLE "DBDREAM"."IDEN_TEST"
   (    "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 99999999999999
99999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE,
        "NAME" VARCHAR2(10)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

那么可不可以修改这个序列的属性呢？

SQL> alter sequence ISEQ$$_91615 cache 100;
alter sequence ISEQ$$_91615 cache 100
*
ERROR at line 1:
ORA-32793: cannot alter a system-generated sequence

ORACLE不让手动修改自增长字段自己维护的序列，那么，是不是也不让删除呢？

SQL> drop sequence ISEQ$$_91615;
drop sequence ISEQ$$_91615
              *
ERROR at line 1:
ORA-32794: cannot drop a system-generated sequence

也不允许删除，那么可不可以通过alter table命令修改这个序列的属性呢？

SQL> alter table IDEN_TEST modify(id number GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 99999999999999 INCREMENT BY 1 START WITH 1 CACHE  100);
Table altered.

再看看序列的属性是否被更改了呢？

SQL>  select SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER from user_sequences;
SEQUENCE_NAM  MIN_VALUE             MAX_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------ ---------- --------------------- ------------ ---------- -----------
ISEQ$$_91615          1        99999999999999            1        100           1

序列的属性也被修改了，可见通过alter table命令是可以间接修改自增长字段调用的序列的属性的，那么可不可以直接查这个序列的nextval呢？

SQL> select ISEQ$$_91615.nextval from dual;
   NEXTVAL
----------
         1

查询是没问题的，要是不让查，就赋不了值了，那么如果把表删掉，序列会不会也被删掉呢？

SQL> select object_name,object_type from user_objects;
OBJECT_NAME  OBJECT_TYPE
------------ -----------
ISEQ$$_91615 SEQUENCE

序列还在，那么如果通过purge的方式不让表进回收站，直接删掉会不会序列也继续存在呢？

SQL> flashback table iden_test to before drop;
Flashback complete.
SQL> drop table iden_test purge;
Table dropped.
SQL> select object_name,object_type from user_objects;
no rows selected

不进回收站，直接删除表，相应的序列也直接被删掉了。

下面测试下，向这张表中插入数据。

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

自增长字段不允许插入数据。

SQL> insert into iden_test(name) values('dbdream');
1 row created.
SQL> insert into iden_test(name) values('stream');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from iden_test;
        ID NAME
---------- --------------------
         1 dbdream
         2 stream

这种generated always属性的自增长字段也不允许插入空值。

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

下面测试下generated by default属性，也就是用序列做为字段的默认值，创建测试表。

SQL> create table def_test(id number GENERATED BY DEFAULT AS IDENTITY,name varchar2(10));
Table created.

测试插入数据。

SQL> insert into def_test values(1,'dbdream');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into def_test(name) values('stream');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into def_test values(null,'wind');
insert into def_test values(null,'wind')
                            *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("DBDREAM"."DEF_TEST"."ID")
SQL> select * from def_test;
        ID NAME
---------- --------------------
         1 dbdream
         1 stream

可见，generated by default属性的字段，如果这个字段在插入时有值，那么就插入这个值，如果这个字段没有插入值，就插入序列的值，但是这种类型的字段都是非空的，不能插入空值。

SQL> alter table def_test modify id NULL;
alter table def_test modify id NULL
*
ERROR at line 1:
ORA-01451: column to be modified to NULL cannot be modified to NULL

而且，也不允许将这种类型的字段设置为可以为空，这种类型的字段就是将序列做为这个字段的默认值。

那么在测试下generated by default on null类型的字段，也就是用序列填充空值，先创建测试表。

SQL> create table dtn_test(ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,NAME VARCHAR2(10));
Table created.

然后测试插入数据。

SQL> insert into dtn_test values (1,'dbdream');
1 row created.
SQL> insert into dtn_test values(null,'stream');
1 row created.
SQL> insert into dtn_test(name) values ('wind');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dtn_test;
        ID NAME
---------- --------------------
         1 dbdream
         1 stream
         2 wind

这种类型的字段，如果在插入数据的时候，这个字段有值，就插入这个值，只要这个字段为空(不管是插入时插入的控制还是不插入这个字段)，都向这个字段插入序列的值。

如果用generated by default或者generated by default on null类型的字段做主键，如果程序控制的不好，可能会遇到ORA-00001(主键重复)错误。

如果使用10046追踪上述的insert动作，可以在SQL执行计划中看到调用SEQUENCE的信息，在官方文档中ORACLE也说明自增长字段就是调用的序列。

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  (cr=4 pr=2 pw=0 time=41859 us)
         1          1          1   SEQUENCE  ISEQ$$_91621 (cr=3 pr=0 pw=0 time=1113 us)

ORACLE对IDENTIFY类型的字段也有一些限制，下面是官方文档中列举的限制条件。

•You can specify only one identity column per table.
•If you specify identity_clause, then you must specify a numeric data type for datatype in the column_definition clause. You cannot specify a user-defined data type.
•If you specify identity_clause, then you cannot specify the DEFAULT clause in the column_definition clause.
•When you specify identity_clause, the NOT NULL constraint and NOT DEFERRABLE constraint state are implicitly specified. If you specify an inline constraint that conflicts with NOT NULL and NOT DEFERRABLE, then an error is raised.
•If an identity column is encrypted, then the encryption algorithm may be inferred. Oracle recommends that you use a strong encryption algorithm on identity columns.
•CREATE TABLE AS SELECT will not inherit the identity property on a column.

本案例测试了自增长字段，用序列做字段的默认值，用序列填充空值，仅是测试，没有深入研究，仅供参考。

—————————————–end——————————————-

本文固定链接: https://www.dbdream.com.cn/2013/08/oracle-12c%e6%96%b0%e7%89%b9%e6%80%a7-%e8%87%aa%e5%a2%9e%e9%95%bfidentity%e5%ad%97%e6%ae%b5/ | 信春哥，系统稳，闭眼上线不回滚！