PostgreSQL插入指定ID记录并迁移数据及外键关联问题

错误原因 #

你执行的UPDATE语句按id正序递增（从15开始），当把原有id=15改为16时，表中原本的id=16还未更新，此时会触发唯一键约束冲突——新的id值已被未更新的行占用，这就是报错The key "(id)=(17)" already exists的原因。

正确操作步骤 #

要实现插入id=15的新记录且原有id>14的记录后移，同时同步更新关联的product表外键，需按以下步骤执行（全程用事务保证数据一致性）：

1. 开启事务

BEGIN TRANSACTION;

2. 同步更新product表的外键值（倒序更新，避免冲突）
   假设product表中关联employees.id的外键列是employee_id，执行：

UPDATE product
SET employee_id = employee_id + 1
WHERE employee_id >= 15
ORDER BY employee_id DESC;

3. 倒序更新employees表的id
   先从最大的id开始递增，确保每次更新的新id不会和未更新的行重复：

UPDATE employees
SET id = id + 1
WHERE id >= 15
ORDER BY id DESC;

4. 插入新记录

INSERT INTO employees (id, firstname, lastname)
VALUES (15, 'john', 'doe');

5. 提交事务

COMMIT;

关键说明 #

• 倒序更新是核心：从大到小修改id，每次更新的新id都是当前表中不存在的（更大的id已先被移走），彻底避免唯一键冲突。
• 事务必须包裹所有操作：确保employees和product表的修改要么全部成功，要么全部回滚，防止出现数据不一致的情况。
• 如果还有其他表关联employees.id，需同步执行类似的倒序更新操作，否则会触发外键约束错误。

内容的提问来源于stack exchange，提问作者karim1990