Oracle IDENTITY标识列
在本教程中,您将学习如何使用Oracle标识列来为表定义一个自动生成的数字列。
注意:本教程仅针对 oracle 12c以上版本。
Oracle标识列介绍
Oracle 12c引入了一种新的方法,允许为表定义一个标识列,类似于中的AUTO_INCREMENT列或SQL Server中的IDENTITY列。
标识列对代理列非常有用。在标识列中插入新行时,Oracle会自动生成并向列中一个顺序值。
要定义标识列,可以使用如下所示的标识子句:
GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ]
首先,GENERATED关键字是强制性的。
其次,可以指定一个选项来生成标识值:
（1）GENERATED ALWAYS:Oracle始终为标识列生成一个值。尝试将值插入标识列将导致错误。
（2）GENERATED BY DEFAULT:如果没有提供任何值,Oracle将为标识列生成一个值。如果提供一个值,Oracle会将该值插入到标识列中。对于这个选项,如果向标识列中插入一个NULL值,Oracle将发出一个错误。
（3）GENERATED BY DEFAULT ON NULL - 如果提供NULL值或根本就没有提供值,则Oracle为标识列生成一个值。
第三,可以有多个标识列的选项。
（1）START WITH initial_value 控制用于标识列的初始值。默认的初始值是1。
（2）INCREMENT BY internval_value 定义生成值之间的时间间隔。默认情况下,间隔值为1。
（3）CACHE定义了Oracle应该预先生成的一些值,以提高性能。对具有大量插入的列使用此选项。
Oracle标识列的例子
下面来看一些使用Oracle标识列的例子。
1. GENERATED ALWAYS例子
以下语句创建一个名为identity_demo的表,该表由一个标识列组成:
-- 12c语法 CREATE TABLE identity_demo ( id NUMBER GENERATED ALWAYS AS IDENTITY, description VARCHAR2(100) NOT NULL );
以下语句将新行插入到identity_demo表中:
INSERT INTO identity_demo(description)
VALUES('Oracle identity column demo with GENERATED ALWAYS');因为没有为id列指定一个值,所以Oracle自动生成一个从1开始的顺序值。
SELECT * FROM identity_demo;
执行上面查询语句,得到以下结果 -
以下语句尝试将值插入id标识列中:
INSERT INTO identity_demo(id,description) VALUES(2, 'Oracle identity column example with GENERATED ALWAYS ');
Oracle发布了一个错误:
SQL Error: ORA-32795: cannot insert into a generated always identity column
因为id列被定义为GENERATED ALWAYS,所以不能接受任何提供的值。
2. GENERATED BY DEFAULT例子
下面为id列更改为GENERATED BY DEFAULT属性:
DROP TABLE identity_demo; CREATE TABLE identity_demo ( id NUMBER GENERATED BY DEFAULT AS IDENTITY, description VARCHAR2(100) not null );
以下语句将新行插入到identity_demo表中:
INSERT INTO identity_demo(description)
VALUES('Oracle identity column demo with GENERATED BY DEFAULT');执行上面语句,它按预期那样工作。
以下语句为id列提供的值在identity_demo表中插入一个新行:
INSERT INTO identity_demo(id,description) VALUES(2, 'Oracle identity column example with GENERATED BY DEFAULT');
在这个例子中,Oracle使用提供的值并将其插入到表中。
SELECT * FROM identity_demo;
执行上面查询语句,得到以下结果 -
以下示例尝试将空值插入到id列中:
INSERT INTO identity_demo(id,description) VALUES(NULL, 'Oracle identity column demo with GENERATED BY DEFAULT, NULL value');
Oracle发布了一个错误:
SQL Error: ORA-01400: cannot insert NULL into ("OT"."IDENTITY_DEMO"."ID")3. GENERATED BY DEFAULT ON NULL 例子
首先,将identity_demo表的id列更改为GENERATED BY DEFAULT ON NULL:
DROP TABLE identity_demo; CREATE TABLE identity_demo ( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, description VARCHAR2(100) not null );
以下语句对id列没有提供任何值,Oracle会自动为insert生成一个值:
INSERT INTO identity_demo(description)
VALUES('Oracle identity column demo with no value');以下语句将NULL值插入到id列中,因为id列已被定义为GENERATED BY DEFAULT ON NULL,Oracle生成一个连续值并将其用于插入:
INSERT INTO identity_demo(description)
VALUES('Oracle identity column demo with null');查询表中的数据,得到以下结果 -
4. START WITH 选项示例
首先,重新创建id列被定义为标识列的identity_demo表,其初始值从100开始:
DROP TABLE identity_demo; CREATE TABLE identity_demo ( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 100, description VARCHAR2(100) not null );
其次,将一行插入到identity_demo表中:
INSERT INTO identity_demo(description)
VALUES('Oracle identity column demo with START WITH option');第三,从identity_demo表中查询数据:
SELECT * FROM identity_demo;
正如所看到的,id列的初始值是100,如在identity子句中指定的。
5. INCREMENT BY选项示例
首先,更改包含START WITH和INCREMENT BY选项的identity_demo表的id列。
DROP TABLE identity_demo; CREATE TABLE identity_demo ( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 10 INCREMENT BY 10, description VARCHAR2(100) not null );
其次,在identity_demo表中插入两行:
INSERT INTO identity_demo(description)
VALUES('Oracle identity column demo 1 with INCREMENT BY option');
INSERT INTO identity_demo(description)
VALUES('Oracle identity column demo 2 with INCREMENT BY option');第三,从表中查询数据来验证插入:
SELECT * FROM identity_demo;
执行查询上面示例代码,得到以下结果 -
正如所看到的,第一行的id值为10。第二行的id值为20。这是id列开始的定义,应该以10开头,第次新增10。
Oracle标识列限制
id列受以下限制:
（1）每个表都有一个且只有一个标识列。
（2）标识列的必须是类型。用户定义的数据类型不允许与标识子句一起使用。
（3）标识列不被CREATE TABLE AS SELECT语句继承。
（4）标识列不能有另一个DEFAULT约束。
（5）可以推断加密标识列的加密算法,因此应该使用强加密算法。
（6）id列的内联约束不得与标识子句所述的NOT NULL和NOT DEFERRABLE约束冲突。
您阅读本篇文章共花了： 