金仓数据库KingbaseES之自增列

• Sequence

测试用例

1. create sequence seq_1 INCREMENT BY 1 MINVALUE 1 START WITH 1;
2. create table test_seq
3. (
4.     id int not null default nextval('seq_1') primary key,
5.     name varchar(10)
6. );

隐式插入

1. insert into test_seq (name) values ('aaa');
2. insert into test_seq (name) values ('bbb');
3. insert into test_seq (name) values ('ccc');

显式插入

1. insert into test_seq (id,name) values (5,'ddd');
2. select * from test_seq;
3. test1=# select * from test_seq;
4. id |name
5. ----+------
6.   1 |aaa
7.   2 |bbb
8.   3 |ccc
9.   5 |ddd

复制代码

再次隐式插入

1. --可以正常插入
2. insert into  test_seq (name)values ('eee');
3. --插入失败，主键重复，因为序列自身是递增的，不会关心表中被显式插入的数据
4. insert into  test_seq (name)values ('fff');
5. test1=# insert into  test_seq(name) values ('eee');
6. INSERT 0 1
7. test1=# insert into  test_seq(name) values ('fff');
8. 错误: 重复键违反唯一约束"test_seq_pkey"
9. 描述: 键值"(id)=(5)" 已经存在
10. --再次执行语句可正常插入，序列因为之前的错误调用自动增加
11. test1=# insert into  test_seq(name) values ('fff');
12. INSERT 0 1
13. test1=# select * from test_seq;
14. id | name
15. ----+------
16.   1 | aaa
17.   2 | bbb
18.   3 | ccc
19.   5 | ddd
20.   4 | eee
21.   6 | fff

1. --重置序列的最大值
2. select setval(' seq_1',(select max(id) from  test_seq)::BIGINT);

1. --事务回滚后，序列号并不会回滚
2. test1=# begin;
3. BEGIN
4. test1=# insert into  test_seq(name) values ('ggg');
5. INSERT 0 1
6. test1=# rollback;
7. ROLLBACK
8. test1=#
9. test1=#
10. test1=# select * from test_seq;
11. id | name
12. ----+------
13.   1 | aaa
14.   2 | bbb
15.   3 | ccc
16.   5 | ddd
17.   4 | eee
18.   6 | fff
19. (6 行记录)
21. test1=# insert into  test_seq(name) values ('ggg');
22. INSERT 0 1
23. test1=# select * from test_seq;
24. id | name
25. ----+------
26.   1 | aaa
27.   2 | bbb
28.   3 | ccc
29.   5 | ddd
30.   4 | eee
31.   6 | fff
32.   8 | ggg
33. (7 行记录)

1. -- truncate 表之后，序列不受影响
2. test1=# truncate table test_seq;
3. TRUNCATE TABLE
4. test1=# insert into  test_seq(name) values ('ggg');
5. INSERT 0 1
6. test1=#  select * fromtest_seq;
7. id | name
8. ----+------
9.   9 | ggg
10. (1 行记录)

复制代码

1. --重置序列
2. ALTER SEQUENCE seq_1 RESTART WITH 1;
3. test1=# ALTER SEQUENCE  seq_1RESTART WITH 1;
4. ALTER SEQUENCE
5. test1=# insert into  test_seq(name) values ('ggg');
6. INSERT 0 1
7. test1=#  select * fromtest_seq;                    
8. id | name
9. ----+------
10.   9 | ggg
11.   1 | ggg

• Serial

测试用例

1. create table  test_serial
2. (
3.    id serial primary key,
4.    name varchar(100)
5. );

隐式插入

1. insert into   test_serial(name) values ('aaa');
2. insert into   test_serial(name) values ('bbb');
3. insert into   test_serial(name) values ('ccc');

显示插入

1. insert into   test_serial(id,name) values (5,'ddd);
2. select * from  test_serial;

1. --再次隐式插入,第二次会报错
2. insert into test_serial(name) values ('eee');
3. insert into test_serial(name) values('fff');
5. test1=# insert into   test_serial(id,name) values (5,'ddd);
6. INSERT 0 1
7. test1=# insert into   test_serial(name) values ('eee');
8. INSERT 0 1
9. test1=# insert into   test_serial(name) values ('fff');
10. 错误:  重复键违反唯一约束"test_serial_pkey"
11. 描述:  键值"(id)=(5)" 已经存在

2. --再次执行语句可正常插入，序列因为之前的错误调用自动增加
3. test1=# insert into test_serial(name)values ('fff');
4. INSERT 0 1
5. test1=# select * from  test_serial;
6. id |name
7. ----+------
8.   1 |aaa
9.   2 |bbb
10.   3 |ccc
11.   5 |ddd
12.   4 |eee
13.   6 |fff
14. (6 行记录)

1. --重置serial
2. SELECT SETVAL((SELECT sys_get_serial_sequence(' test_serial','id')), 1, false);

• Identity

Identity是R6版本新增的语法，R3数据库不支持该语法。

identity定义成generated by default as identity允许显式插入，

identity定义成always as identity 不允许显示插入，但是加上overriding system value也可以显式插入。

测试用例1

1. create table  test_identiy_1
2. (
3.    id int generated always as identity (START WITH 1 INCREMENT BY 1)  primary key ,
4.    name varchar(100)
5. );
6. insert into test_identiy_1(name) values ('aaa');
7. insert into test_identiy_1(name) values ('bbb');
8. insert into test_identiy_1(name) values ('ccc');

1. --显式插入值
2. 如果定义为generated always as identity则不允许显式插入，除非增加overriding system value 提示。
3. insert into test_identiy_1(id,name) values (5,'ccc');
4. insert into test_identiy_1(id,name)overriding system value values (5,'ccc');
6. test1=# insert into test_identiy_1(id,name)values (5,'ccc');
7. 错误:  无法插入到列"id"
8. 描述:  列"id"是定义为GENERATED ALWAYS的标识列.
9. 提示:  使用OVERRIDING SYSTEM VALUE覆盖.
10. test1=# insert intotest_identiy_1(id,name)overriding system value values (5,'ccc');
11. INSERT 0 1

     测试用例2

2. create table  test_identiy_2
3. (
4.    id int generated by default as identity (START WITH 1 INCREMENT BY1)  primary key ,
5.    name varchar(100)
6. );
8. insert into test_identiy_2(name) values ('aaa');
9. insert into test_identiy_2(name) values ('bbb');
10. insert into test_identiy_2(name) values ('ccc');
12. test1=# insert into test_identiy_2(id,name)values (5,'ccc');
13. INSERT 0 1

重置Identity的方式有2种：

1. 1.      ALTER TABLE test_identiy_1ALTER COLUMN id RESTART WITH 100;
2. 2.      TRUNCATE table test_identiy_1RESTART IDENTITY;