PostgreSQL如何合并库存表两行条码数据且不丢失外键引用

前置注意事项 #

• 操作前务必全量备份inventory表和所有关联表数据，避免误操作导致数据丢失
• 建议在业务低峰期执行操作，避免批量更新锁表影响正常业务
• 所有操作建议先在测试环境验证通过后再操作生产环境

操作步骤 #

可选前置步：保留旧条码的商品信息 #

如果需要将旧条码对应的商品名称等字段值合并到新条码上，先执行以下更新，新条码信息已经正确的话可跳过本步：

UPDATE inventory 
SET name = (SELECT name FROM inventory WHERE barcode = 'SH9025H36SP23') 
WHERE barcode = 'SH9025H36SP23N1';

步骤1：查询所有关联表避免遗漏 #

通过以下SQL可以快速定位所有引用了inventory.barcode的外键关联表和对应字段，无需手动逐个梳理：

SELECT 
  tc.table_name AS 关联表名, 
  kcu.column_name AS 外键字段名
FROM 
  information_schema.table_constraints tc
INNER JOIN 
  information_schema.key_column_usage kcu 
ON 
  tc.constraint_name = kcu.constraint_name
WHERE 
  tc.constraint_type = 'FOREIGN KEY' 
  AND kcu.referenced_table_name = 'inventory'
  AND kcu.referenced_column_name = 'barcode';

步骤2：批量更新所有关联表的旧条码值 #

因为新条码SH9025H36SP23N1已经存在于inventory表中，直接更新关联表的外键字段为新条码不会触发外键约束报错，对每一个关联表执行如下更新语句即可：

-- 以act表为例
UPDATE act SET fkbarcode = 'SH9025H36SP23N1' WHERE fkbarcode = 'SH9025H36SP23';
-- 以do表为例
UPDATE do SET fkbarcode = 'SH9025H36SP23N1' WHERE fkbarcode = 'SH9025H36SP23';
-- 其余关联表按上述格式替换表名和外键字段名即可

步骤3：校验旧条码引用是否完全清理 #

逐个查询所有关联表，确认已经没有旧条码SH9025H36SP23的引用：

SELECT COUNT(*) FROM act WHERE fkbarcode = 'SH9025H36SP23';
SELECT COUNT(*) FROM do WHERE fkbarcode = 'SH9025H36SP23';
-- 其余关联表同理查询

所有查询结果均返回0之后再执行下一步。

步骤4：删除inventory表中的旧条码记录 #

此时没有任何表引用旧条码，DELETE RESTRICT约束不会拦截删除操作：

DELETE FROM inventory WHERE barcode = 'SH9025H36SP23';

安全操作建议 #

所有操作可以包裹在事务中执行，出现问题可以随时回滚，示例事务结构如下：

BEGIN TRANSACTION;
-- 此处填写所有UPDATE语句
-- 此处填写所有校验查询语句
-- 确认数据无误后执行DELETE语句
-- 没问题就执行COMMIT提交，有问题就执行ROLLBACK回滚

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