这个特性可以生成序号，但是不用手工创建sequence。

create table t
(
id int generated by default as identity,
name varchar2(100)
);

插入数据
insert into t(name) values ('aaa');
insert into t(id, name) values (100, 'bbb');
insert into t(name) values ('ccc');

查询，发现如果insert的时候不指定id，就会自动填入序号。

SQL> select * from t;

        ID NAME
---------- ------------
         1 aaa
       100 bbb
         2 ccc

这样创建的表，id字段是not null的

SQL> desc t
Name        Null?    Type
----------- -------- -----------------
ID          NOT NULL NUMBER(38)
NAME                 VARCHAR2(100)

因此不能插入NULL

SQL> insert into t values (null, 'ddd');
insert into t values (null, 'ddd')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("LS"."T"."ID")

要想自动替换代码里的null，需要添加on NULL属性。

alter table t modify id int generated by default on NULL as identity;

insert into t values (null, 'ddd');
insert into t values (null, 'eee');
insert into t values (null, 'fff');
commit;

这样再插入null值时，还是会自动填上序号。

SQL> select * from t where name > 'd';

        ID NAME
---------- ----------
         6 ddd
         7 eee
         8 fff

还可以改成generated always

alter table t modify id int generated always as identity;

这样就不允许自己填值了

SQL> insert into t values(200, 'ggg');
insert into t values(200, 'ggg')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

并且也不允许更新

SQL> update t set id = 123 where name = 'aaa';
update t set id = 123 where name = 'aaa'
*
ERROR at line 1:
ORA-32796: cannot update a generated always identity column

设置起始和间隔

create table t
(
id int generated always as identity(start with 100 increment by 50),
name varchar2(100)
);

insert into t (name) values ('a');
insert into t (name) values ('b');
insert into t (name) values ('c');
insert into t (name) values ('d');

SQL> select * from t;

        ID NAME
---------- ----------------
       100 a
       150 b
       200 c
       250 d

原理是会启用一个自动生成的sequence

sequence名里包含表的objid，因此一张表最多只有一个identity字段

SQL> select table_name,column_name,default_on_null,identity_column,data_default from user_tab_columns where table_name = 'T';

TABLE_NAME   COLUMN_NAME    DEF IDE DATA_DEFAULT
------------ -------------- --- --- --------------------------------
T            ID             NO YES "LS"."ISEQ$$_92388".nextval
T            NAME           NO NO

这个sequence比较郁闷的是不能自定义cache

SQL> alter sequence ISEQ$$_92392 cache 100;
alter sequence ISEQ$$_92392 cache 100
*
ERROR at line 1:
ORA-32793: cannot alter a system-generated sequence

测试过程还遇到一个bug

当把increment by设置为负数的时候

SQL> alter table t modify id int generated always as identity(start with 0 increment by -1000);
alter table t modify id int generated always as identity(start with 0 increment by -1000)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [12811], [92386], [], [], [], [], [], [], [], [], [], []

然后这张表就什么都设置不了了

SQL> alter table t modify id int generated always as identity(start with 0 increment by 1);

alter table t modify id int generated always as identity(start with 0 increment by 1)

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [12811], [92386], [], [], [], [], [], [], [], [], [], []

删字段也不行

SQL> alter table t drop column id;

alter table t drop column id

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [12811], [92386], [], [], [], [], [], [], [], [], [], []

删表可以，但是不能purge

SQL> drop table t purge;

drop table t purge

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [12811], [92386], [], [], [], [], [], [], [], [], [], []

SQL> drop table t;

Table dropped.

SQL> purge recyclebin;
purge recyclebin
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [12811], [92386], [], [], [], [], [], [], [], [], [], []

看来是出现了数据字典不一致

SQL> select count(*) from obj$ where name = 'ISEQ$$_92385';

COUNT(*)
----------
0

ITPub博客

12c新的自增键方式

最新文章