http://www.cndba.cn/dave/article/219
1 Identity Columns 说明
在Oracle 12c 之前，表中的列是没有自增功能的，所以要想实现主键的自增，通常的做法是先创建一个序列，在创建一个触发器，来实现自增，在Oracle 12c 中，Oracle 解决了这个问题， Identity Columns的新特性，实现了自增列的功能。
关于Oracle 12c序列的操作，可以直接查看官方手册，有详细的说明。 我们这里重点测试identity columns。
Identity Columns 是自动增加的列，该特性使用默认的序列生成器来强制表列序列按递增或者递减的值来分别值。http://www.cndba.cn/dave/article/219
SQL> create table cndba (
id1 number generated by default as identity ,
name varchar2(50)
);
Table created.
SQL> insert into cndba(name) values ('tianlesoftware');
1 row created.
SQL> insert into cndba(name) values ('http://cndba.cn');
1 row created.
SQL> insert into cndba(name) values ('http://ahdba.cn');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from cndba;
ID1 NAME
---------- -----------------------------------------------
1 tianlesoftware
2 http://cndba.cn
3 http://ahdba.cn这里id列成功的使用默认的序列生成器来自增。
2 使用generated by default
除了使用默认的序列生成器之外，也可以使用指定的序列来自增，或者仅当插入NULL 的时候才自增。
在语法上，如采用generated by default就是采用默认的序列生成器，此时用户可以插入自己的值，如果使用GENERATED ALWAYS 就是使用特定的序列，此时自增列只能使用序列生成器提供的值，用户无法更改自增列。
如果在添加ON Null 选项，则表示仅当插入NULL时，才会自增。
示例：
SQL> create table ahdba (
id1 number generated by default as identity ,
name varchar2(50)
);
Table created.
SQL> insert into ahdba(name) values ('http://ahdba.com');
1 row created.
SQL> insert into ahdba values(100,'dave');
1 row created.
SQL> commit;
Commit complete.
--注意这里，用户可以插入自增列。
SQL> insert into ahdba values (NULL,'tianlesoftware');
insert into ahdba values (NULL,'tianlesoftware')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."AHDBA"."ID1")
SQL> select * from ahdba;
ID1 NAME
---------- --------------------------------------------------
1 http://ahdba.com
100 dave
创建仅插入NULL 时才自增的对象：
create table huaining (
id1 number generated by default on NULL as identity ,
name varchar2(50));
SQL> desc huaining
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NOT NULL NUMBER
NAME VARCHAR2(50)
SQL> insert into huaining values (1, '安庆');
1 row created.
SQL> insert into huaining values(20,'怀宁');
1 row created.
SQL> insert into huaining values (NULL,'月山');
1 row created.
SQL> insert into huaining values (NULL,'合肥');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from huaining;
ID1 NAME
---------- --------------------------------------------------
1 安庆
20 怀宁
1 月山
2 合肥
可以看到上面的结果，只有最后2条记录自增了，其他2条记录，采用了我们指定的值。
3 使用Generated Always
上面的实验都是采用默认的序列生成器来自增，也可以使用Generated Always的方式指定独立的序列规则，如果指定为generated always,那么列就不能被用户更新，只能使用序列生成器提供的值。
示例：http://www.cndba.cn/dave/article/219
http://www.cndba.cn/dave/article/219
create table aniqng (
id1 number generated always as identity ,
name varchar2(50));
SQL> desc anqing
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NOT NULL NUMBER
NAME VARCHAR2(50)
SQL> insert into anqing values (1,'aniqng');
insert into anqing values (1,'aniqng')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
SQL> !oerr ora 32795
32795,0000, "cannot insert into a generated always identity column"
// *Cause: An attempt was made to insert a value into an identity column
// created with GENERATED ALWAYS keywords.
// *Action: A generated always identity column cannot be directly inserted.
// Instead, the associated sequence generator must provide the value.
SQL> insert into anqing values (NULL,'dave');
insert into anqing values (NULL,'dave')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
SQL> insert into anqing (name) values ('安庆');
1 row created.
SQL> insert into anqing (name) values ('怀宁');
1 row created.
SQL> insert into anqing (name) values ('月山');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from anqing;
ID1 NAME
---------- --------------------------------------------------
1 安庆
2 怀宁
3 月山
SQL> update anqing set id1=4 where id1=2;
update anqing set id1=4 where id1=2
*
ERROR at line 1:
ORA-32796: cannot update a generated always identity column
4 修改Identity Columns
可以在创建table时指定identity columns的类型和规则，也可以创建之后使用alter table 来修改，生成器的规则在identity选择中指定。http://www.cndba.cn/dave/article/219http://www.cndba.cn/dave/article/219
create table yueshan (
id1 number generated always as identity (start with 8 increment by 3),
name varchar2(50)
);
SQL> insert into yueshan (name) values ('安庆');
SQL> insert into yueshan (name) values ('月山');
SQL> commit;
SQL> select * from yueshan;
ID1 NAME
---------- --------------------------------------------------
8 安庆
11 月山
注意这里的序列开始值和增加的间隔。
可以使用如下SQL 查看identity columns 对应的序列。http://www.cndba.cn/dave/article/219http://www.cndba.cn/dave/article/219
http://www.cndba.cn/dave/article/219
SQL> col table_name for a20
SQL> col COLUMN_NAME for a20
SQL>set lin 130
SQL> select table_name,column_name,default_on_null,identity_column,data_default from user_tab_columns where table_name in ('ANIQNG','HUAINING','YUESHAN','CNDBA');
TABLE_NAME COLUMN_NAME DEF IDE DATA_DEFAULT
-------------------- ---------------------------------------- --- --- ------------------------------
CNDBA ID1 NO YES "SYS"."ISEQ$$_92197".nextval
CNDBA NAME NO NO
HUAINING ID1 YES YES "SYS"."ISEQ$$_92201".nextval
HUAINING NAME NO NO
YUESHAN ID1 NO YES "SYS"."ISEQ$$_92205".nextval
YUESHAN NAME NO NO
6 rows selected.
5 12c 中Identity columns的限制
1． 一张表里只能有一个identity column。
2． Identity column 的类型只能是number，不能是用户定义的类型。
3． 对identity column的表进行CTAS 复制时不会继承identity column的属性。
4． 创建identity column时，不能指定DEFAULT 默认值。
5． Identity column列默认包含not null 和non deferrable约束。
http://www.cndba.cn/dave/article/219
版权声明：本文为博主原创文章，未经博主允许不得转载。
CNDBA社区
