12c新的自增键方式-白红宇

12c新的自增键方式

阅读量：2434 次

发布时间：2019-05-10

本文共 3705 字，大约阅读时间需要 12 分钟。

这个特性可以生成序号，但是不用手工创建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

----------- -------- -----------------

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: