Oracle的“自增列“（身份列 Identity Columns）_oracle 自增列

从12c开始，Oracle提供了身份列（Identity Column）特性，在创建表时可以实现类似MySQL中的自增（auto_increment），从而为每一行数字自动生成ID值（身份）。

目录

一、身份列简介

二、身份列的定义

2.1 创建generated always类型身份列

2.2 创建generated by default类型身份列

2.3 创建generated by default on null类型身份列

三、序列生成器的属性

---

一、身份列简介

身份列（Identity Column）可以在建表时通过 create table 指定，或者使用alter table 修改为身份列。只有integer, number 和long类型的列可以被指定为身份列。当指定身份列时，系统会隐式为其创建一个序列生成器（Sequence Generator SG），这个序列生成器为身份列提供值。而删除表则也会顺带删除这个序列生成器。

身份列自动生成数值的特性依然是利用序列，只是省去我们自己定义和调用序列的步骤。

二、身份列的定义

语法：generated (always | (by default [on null])) as identity

一个身份列可以有3种定义类型：

• generated always：总是由系统自动生成身份列的值，如果显式给身份列赋值，则会引发异常。
• generated by default：用户可以给身份列赋值，如果未赋值，则由系统生成身份列的值。
• generated by default on null：为第二项定义的扩展，如果指定了on null选项，则为身份列列赋值为null时会由系统生成值。

2.1 创建generated always类型身份列

create table tab1(
id integer generated always as identity,
name varchar2(32));

如果显式对generated always类型的列赋值，则会报错：

insert into tab1 values(1, 'Vincent');

我们仅对name列赋值，插入后，发现id列自动生成数值：

insert into tab1(name) values('Vincent');
select * from tab1;

2.2 创建generated by default类型身份列

create table tab2(
id integer generated by default as identity,
name varchar2(32));

这里第1个insert显式给id赋值，而后面2个insert只给name赋值，由序列生成器生成id列的值。

insert into tab2 values(2, 'Vincent');
insert into tab2(name) values('Victor');
insert into tab2(name) values('Grace');

观察上面的插入的数据，可以看到用户指定的值是可以与序列生成器的值重复的，因此generated by default 并不保证值的唯一，它会与表中已有的数据生成相同的值，同时用户也可以再次插入已存在的值。

select * from tab2;

如果想要获得唯一的序列值，则只能定义为generated always as identity（并且不能指定序列生成器的cycle属性）。

同时generated by default也是不能插入null值的。

insert into tab2 values(null, 'Vincent');

2.3 创建generated by default on null类型身份列

这里我们将tab2里的身份列修改为generated by default on null 类型（增加on null 选项）：

alter table tab2 modify id integer generated by default on null as identity;

增加了on null选项后，如果显式赋值为null则会调用序列生成器为其生成值：

insert into tab2 values(null, 'Vincent');

三、序列生成器的属性

当我们创建身份列的时候，Oracle会隐式的创建一个序列生成器（Sequence Generator SG）。既然底层也是序列，那么这个序列生成器的属性在创建时也是可以显式指定的。

示例：创建身份列时显式指定属性

create table tab3 (
id number generated always as identity (
start with 10
increment by 2
maxvalue 200
nocycle),
name varchar2(32)
);

这里在创建身份列时，显式为序列生成器定义了start with，increment by，maxvalue和nocycle选项。选项的解释可以参考上一篇序列的内容：

Oracle序列（Oracle Sequence）_V1ncent Chen的博客-CSDN博客

可以看到在插入数据时，序列生成器按照我们定义的属性生成了数值（起始值为10，步进位为2）

insert into tab3(name) values('Vincent');
insert into tab3(name) values('Victor');
select *from tab3;