自增列

说明

使用此子句指定自增列。对于每个后续的INSERT语句，将从序列生成器中为标识列分配一个递增或递减整数值。可以使用identity_options子句配置序列生成器。

  • ALWAYS

如果指定ALWAYS，则数据库始终使用序列生成器为列分配值。如果尝试使用INSERT或UPDATE显式地为列赋值，则将返回错误。这是默认设置。

  • BY DEFAULT

如果指定BY DEFAULT，则默认情况下，数据库会使用序列生成器为列分配值，但也可以显式为列分配指定的值。如果指定ON NULL，则当后续INSERT语句尝试分配计算结果为NULL的值时，数据库会使用序列生成器为该列分配值。

  • identity_options

使用identity_options子句配置序列生成器。例外是START WITH LIMIT VALUE，它特定于identity_options，只能与ALTER TABLE MODIFY一起使用。

示例

新建自增列

说明：

创建一个带有自增列的表，并进行插入数据。

CREATE TABLE identity_col (

id number GENERATED ALWAYS AS IDENTITY start WITH 10 INCREMENT by 1,

description VARCHAR2(30)

);

INSERT INTO identity_col (description)VALUES(‘one DESCRIPTION’);

INSERT INTO identity_col (description)VALUES(‘second DESCRIPTION’);

ALWAYS选项

说明：

  1. GENERATED ALWAYS AS IDENTITY 可以不指定该列进行插入
  2. GENERATED ALWAYS AS IDENTITY不能在该列中插入NULL值
  3. GENERATED ALWAYS AS IDENTITY不能指定具体值插入
  4. GENERATED ALWAYS AS IDENTITY 不能使用update更新该列

CREATE TABLE identity_aways (

id NUMBER GENERATED ALWAYS AS IDENTITY,

description VARCHAR2(30)

);

INSERT INTO identity_aways (description) VALUES (‘Just DESCRIPTION’);

BY DEFAULT选项

说明：

  1. GENERATED BY DEFAULT AS IDENTITY 可以不指定该列进行插入
  2. GENERATED BY DEFAULT AS IDENTITY 可以指定具体值插入
  3. GENERATED BY DEFAULT AS IDENTITY 不能在该列中插入null值
  4. 可以使用update更新该列，但不能更新为NULL

--by default选项

CREATE TABLE identity_default (

id NUMBER GENERATED BY DEFAULT AS IDENTITY,

description VARCHAR2(30)

);

INSERT INTO identity_default (description) VALUES (‘Just DESCRIPTION’);–ok

INSERT INTO identity_default (id, description) VALUES (999, ‘ID=999 and DESCRIPTION’);

DEFAULT ON NULL选项

说明：

  1. GENERATED BY DEFAULT ON NULL AS IDENTITY 可以不指定该列进行插入
  2. GENERATED BY DEFAULT ON NULL AS IDENTITY 可以指定具体值插入
  3. GENERATED BY DEFAULT ON NULL AS IDENTITY 可以在该列中插入null值
  4. 可以使用update更新该列，但不能更新为NULL

--default on null选项

CREATE TABLE identity_default_null (

id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,

description VARCHAR2(30)

);

INSERT INTO identity_default_null (description) VALUES (‘Just DESCRIPTION’);

INSERT INTO identity_default_null (id, description) VALUES (999, ‘ID=999 and DESCRIPTION’);

INSERT INTO identity_default_null (id, description) VALUES (NULL, ‘ID=NULL and DESCRIPTION’);

UPDATE identity_default_null SET ID=3 WHERE ID=1;

IDENTITY_OPTIONS

不指定任何选项时

将创建一个从1开始并以1递增且没有上限的升序序列，仅指定INCREMENT BY -1会创建一个以-1开头并没有下限递减的降序序列

代码示例：

CREATE TABLE identity_2(

id number GENERATED BY DEFAULT AS IDENTITY,

description VARCHAR2(30)

);

INSERT INTO identity_2 (description) VALUES (‘one DESCRIPTION’);

select * from identity_2;

ID | DESCRIPTION

1 one DESCRIPTION

INCREMENT BY

用于定义序列步长，如果忽略，则默认为-1，如果出现负值，则代表序列的值是按照此步长递减的，该值的绝对值必须小于MAXVALUE和MINVALUE的差值。

代码示例：

CREATE TABLE identity_3(

id number GENERATED BY DEFAULT AS IDENTITY INCREMENT by 2,

description VARCHAR2(30)

);

INSERT INTO identity_3 (description) VALUES (‘one DESCRIPTION’);

INSERT INTO identity_3 (description) VALUES (‘second DESCRIPTION’);

select * from identity_3;

ID | DESCRIPTION

1 one DESCRIPTION

3 second DESCRIPTION

START WITH

指定生成序列的第一个值，对于升序序列，默认值是序列的最小值。对于降序序列，默认值为序列的最大值

代码示例：

CREATE TABLE identity_4(

id number GENERATED BY DEFAULT AS IDENTITY INCREMENT by 2 START WITH 10,

description VARCHAR2(30)

);

INSERT INTO identity_4 (description) VALUES (‘one DESCRIPTION’);

INSERT INTO identity_4 (description) VALUES (‘second DESCRIPTION’);

select * from identity_4;

ID | DESCRIPTION

10 one DESCRIPTION

12 second DESCRIPTION

MAXVALUE/NOMAXVALUE

指定序列可以生成的最大值。默认为NOMAXVALUE,代表没有最大值定义。这个整数值为bigint的最大值(指定插入值不受MAXVALUE限制)

代码示例：

CREATE TABLE identity_5(

id int GENERATED BY DEFAULT AS IDENTITY INCREMENT by 2 START WITH 10 MAXVALUE 15,

description VARCHAR2(30)

);

INSERT INTO identity_5 (description) VALUES (‘one DESCRIPTION’);

INSERT INTO identity_5 (description) VALUES (‘second DESCRIPTION’);

INSERT INTO identity_5 (description) VALUES (‘third DESCRIPTION’);

INSERT INTO identity_5 (id, description) VALUES (1000,’ fourth DESCRIPTION’);

select * from identity_5;

ID | DESCRIPTION

10 one DESCRIPTION

12 second DESCRIPTION

14 third DESCRIPTION

1000 fourth DESCRIPTION

MINVALUE/NOMINVALUE

指定序列生成的最小值。默认选项为NOMINVALUE,代表没有最小值定义。这个整数值为bigint的最小值，MINVALUE必须小于等于START WITH且必须小于MAXVALUE

代码示例：

CREATE TABLE identity_6(

id int GENERATED BY DEFAULT AS IDENTITY INCREMENT by -2 START WITH 10 MAXVALUE 20 MINVALUE 5,

description VARCHAR2(30)

);

INSERT INTO identity_6 (description) VALUES (‘one DESCRIPTION’);

INSERT INTO identity_6 (description) VALUES (‘second DESCRIPTION’);

INSERT INTO identity_6 (description) VALUES (‘third DESCRIPTION’);

INSERT INTO identity_6 (id, description) VALUES (1000,’sixth DESCRIPTION’);

CYCLE/NOCYCLE

表示序列达值达到限制值后是否循环，NOCYCLE为默选项，表示不循环。如果循环，则当递增序列达到最大值时，循环到最小值；对于递减序列达到最小值时，循环到最大值

代码示例：

--cycle

drop table identity_col;

CREATE TABLE identity_col (

id number GENERATED ALWAYS AS IDENTITY start WITH 10 INCREMENT by 2 maxvalue 15 cycle cache 2,

description VARCHAR2(30)

);

INSERT INTO identity_col (description) VALUES (‘one DESCRIPTION’);

INSERT INTO identity_col (description) VALUES (‘second DESCRIPTION’);

INSERT INTO identity_col (description) VALUES (‘third DESCRIPTION’);

INSERT INTO identity_col (description) VALUES (‘fourth DESCRIPTION’);

select * from identity_col;

--NOCYCLE

CREATE TABLE identity_col1 (

id number GENERATED ALWAYS AS IDENTITY start WITH 10 INCREMENT by 2 maxvalue 15 nocycle cache 2,

description VARCHAR2(30)

);

INSERT INTO identity_col1 (description) VALUES (‘one DESCRIPTION’);

INSERT INTO identity_col1 (description) VALUES (‘second DESCRIPTION’);

INSERT INTO identity_col1 (description) VALUES (‘third DESCRIPTION’);

select * from identity_col1;

ID | DESCRIPTION

10 one DESCRIPTION

12 second DESCRIPTION

14 third DESCRIPTION

CACHE/NOCACHE

定义存放序列的内存块大小，如果不指定，则默认缓存20个序列号

代码示例：

CREATE TABLE identity_7(

id int GENERATED BY DEFAULT AS IDENTITY cache 30,

description VARCHAR2(30)

);

INSERT INTO identity_7 (description) VALUES (‘one DESCRIPTION’);

INSERT INTO identity_7 (description) VALUES (‘second DESCRIPTION’);

INSERT INTO identity_7 (description) VALUES (‘third DESCRIPTION’);

INSERT INTO identity_7 (description) VALUES (‘fourth DESCRIPTION’);

ORDER/NOORDER

该选项用于数据库集群获取有序序列

限制

  1. 每个表只能指定一个Identity column
  2. Identity column必须是数字类型，不能是用户自定义数据类型
  3. Identity column不能有默认子句
  4. Identity column隐含的具有NOT NULL和NOT DEFERRABLE约束，不能被显示的转换其属性
  5. CREATE TABLE AS SELECT不会继承identity column属性