首页 > Oracle > Oracle 12c New Feature – Identity Columns
2014
06-23

Oracle 12c New Feature – Identity Columns

12C R2版本也出来比较长的时间了,逐步趋向于稳定,这篇文章我们来看一下12C中的一个新特性标识列,这个新特性允许指定列自动的填充系统创建的sequence。来看下分别用create方式和alter方式创建的表的情况。

1. 创建一个普通没有标识列的表和有标识列的表的情况

可以看到jerry_test2表中的transaction_id列创建的时候,指定了标识列,不允许为空,下面看一下这个系统自动填充的sequence

可以看到这个标识列的sequence命名方式为ISEQ$$_<OBJECT_ID>,其次一个表中只允许为一个列创建标志列,否则会报ORA-30669错误。

如果往标识列插入数据的话,数据库会抛出ORA-32795错误,不允许往标识列插入数据,因为此列是填充系统自动生成的序列。

当然我们可以指定创建标识列的方式,比如可以插入值,当没有指定值的时候,使用默认的值,而且可以显示指定标识列可以存储NULL值。

此外我们还可以指定identity 列的初始值和步长,比如

如何查看表中的列是否指定了identity列??

当一个列指定了identity后,在TAB$表中的property列可以看到额外的位数

查看property的值,参考$ORACLE_HOME/rdbms/admin/dcore.bsq

同时在COL$表中也能查看到property值,如下sql列出了property列在COL$表中的情况

2. 下面我们来看下alter identity列的情况,毫无疑问的是identity列也支持alter,不过只能修改已经存在的identity列的属性,不能修改一个不是identity列为identity列,这样会收到ORA-30673错误,我们来看这样一个例子:创建一个identity列,指定 BY DEFAULT ON NULL属性,允许在没有插入值的情况自动填充值。

这个例子中jerry_test6表,显示指定了插入到identity列的值,所以最大的值就是1004。如果此时我们需要把transaction_id列(也就是identity列),改为自动填充,可以使用如下alter语句

需要注意的是,START WITH LIMIT VALUE只能在alter table语句里指定,如果指定了这个属性,数据库就会扫描这个表,获取identity列的最大值,然后在后续的插入中,默认就会在这个值上+1,比如此表中的最大值是1004,那下一个值就是1005.

虽然我们不能指定一个non-identity列为identity列,但可以修改identity列为non-identity列,相当于达到了drop identity列的效果。

限制条件:

 

  1. 一个表只允许有一个identity列
  2. identity列的数据类型只能是number类型,不能为其他类型
  3. 如果一个列指定了是identity列,那这个列就不能添加default属性,也就是不能为一个固定的值
  4. CREATE TABLE AS SELECT语句没有继承源表的identity列属性

注意:如果直接使用drop命令删除一个表,identity列创建的sequence不会被删除,因为这个sequence命名方式是根据object_id来命名的。

最后编辑:
作者:Jerry
一个积极向上的小青年,热衷于分享--Focus on DB,BI,ETL