只想知道何如解决的小伙伴,可以直接跳到第三章节哦!
1、问题原因
在Postgres数据库,执行insert语句时,数据库报错Key (id)=(1849) already exists。数据库id为1849的记录已经存在。
一般出现这个情况的原因为,表的主键是自增的,手动插入数据或通过sql插入但给主键赋值时,无法触发自增序列。当不指定主键,使用自增序列插入数据时,就可能会报主键已经存在的错误。
2、自增序列原理
原理:pg数据库通过自增序列实现主键自增,每次新增数据时,如果未指定主键,会查询当前自增序列的值,并将值加1,当作新的主键值。如果数据库已经存在一条id与当前自增序列值加1相等的值,就会报主键值已经存在。
情景模拟:
(1)创建student表
create table student(
id int primary key,
name varchar(10)
);
(2)创建自增序列,并设置其实值为1,步长为1。将序列与student表主键关联,实现自增。
//创建序列
CREATE SEQUENCE table_student_id_seq;
//将序列与student表的主键关联
ALTER TABLE student ALTER COLUMN id SET DEFAULT nextval('table_student_id_seq');
//设置初始值为1
ALTER SEQUENCE table_student_id_seq START WITH 1;
//设置步长为1
ALTER SEQUENCE table_student_id_seq INCREMENT BY 1;
(3)通过sql添加一条数据,不指定id,即通过自增序列指定主键。
insert into student(name) values('张三');
查询当前student表,新的数据的id为1
select * from student;
id|name|
--+----+
1|张三 |
查询序列 table_student_id_seq当前值,当前序列值为1
SELECT currval('table_student_id_seq');
currval|
-------+
1|
(4)手动插入一条数据,指定id=3,不触发自增序列。
insert into student(id,name) values(3,'张三');
select * from student;id|name|
--+----+
1|张三 |
3|张三 |
数据库里存在一条id为1,3的数据
(5)通过sql添加一条数据,不指定id,即通过自增序列指定主键。由于上次自增序列为1,新插入的数据的id应该为1+1=2,可以正常插入。
不指定id,新插入一条数据
insert into student(name) values('张三');
当前student表内容:
id|name|
--+----+
1|张三 |
3|张三 |
2|张三 |查询当前序列值:
SELECT currval('table_student_id_seq');
currval|
-------+
2|
(6)通过sql添加一条数据,不指定id,即通过自增序列指定主键。上次自增后,自增序列值为2,新插入的数据id应该为2+1=3,但是数据库已经存了id为3的数据。因此就会报错Key (id)=(3) already exists。
不指定id,新插入一条数据
insert into student(name) values('张三');
报错:
SQL 错误 [23505]: ERROR: duplicate key value violates unique constraint "student_pkey"
Detail: Key (id)=(3) already exists.
3、解决方案
重新设置序列值,使序列值为当前数据库id的最大值即可。
- 查询当前表主键的最大值:select max(主键) from 表名。
- 设置设置序列值:select setval(序列名, 新值);
综合上面两个方法,可以合成为一个sql:
select setval(序列名, select max(主键) from 表名);
通过上一节情景演示解决方案:
(1)重新设置序列table_student_id_seq为student表id的最大值.
//设置序列为主键最大值
select setval('table_student_id_seq', (select max(id) from student));
查询当前序列值:
SELECT currval('table_student_id_seq');currval|
-------+
3|
(2)通过sql添加一条数据,不指定id,即通过自增序列指定主键。由于重新设置序列值,当前序列值为3,新增记录的序列值为3+1=4。数据库没有id为4的记录,插入成果。
insert into student(name) values('张三');
查询数据库记录
select * from student;id|name|
--+----+
1|张三 |
3|张三 |
2|张三 |
4|张三 |