PostgreSQL存在外键约束的多表关联删除旧数据方案咨询

核心解决思路就是提前把需要删除的所有关联字段查询并暂存下来，再按照「先删关联表、再删依赖主表、最后删被依赖主表」的顺序执行删除，就可以同时避免外键冲突和数据匹配不到的问题。

你遇到的报错是典型的外键约束依赖问题：approvalsubmission_supplierbookingconfirmation 作为关联表，通过automaticallybookedservices_uri字段关联了supplierbookingconfirmation的uri，同时通过approvalsubmission_id关联了approvalsubmission的id，因此必须先删除关联表的引用记录，才能删除两个主表的对应数据。

最优方案1：使用临时表暂存待删数据（最稳妥，适合生产环境） #

该方案不需要修改表结构，执行逻辑清晰，可回滚性强，适合大数据量清理场景：

1. 首先创建临时表存储所有符合删除条件的关联字段，临时表会在会话结束后自动销毁，不会残留业务数据：

-- 存储需要删除的approvalsubmission_id
CREATE TEMP TABLE temp_del_approval_ids AS
SELECT DISTINCT id FROM approvalsubmission WHERE conclusiondate < now() - INTERVAL '5 year';

-- 存储需要删除的supplierbookingconfirmation的uri
CREATE TEMP TABLE temp_del_supplier_uris AS
SELECT DISTINCT automaticallybookedservices_uri AS uri
FROM approvalsubmission_supplierbookingconfirmation s
INNER JOIN temp_del_approval_ids a ON s.approvalsubmission_id = a.id;

2. 按依赖顺序删除数据：

-- 第一步：删除关联表符合条件的记录
DELETE FROM approvalsubmission_supplierbookingconfirmation
WHERE approvalsubmission_id IN (SELECT id FROM temp_del_approval_ids);

-- 第二步：删除supplierbookingconfirmation的对应记录
DELETE FROM supplierbookingconfirmation
WHERE uri IN (SELECT uri FROM temp_del_supplier_uris);

-- 第三步：删除approvalsubmission的旧记录
DELETE FROM approvalsubmission
WHERE id IN (SELECT id FROM temp_del_approval_ids);

方案2：使用CTE一次性执行（适合小数据量场景） #

如果待删除数据量不大，可以用PostgreSQL的WITH子句一次完成查询和删除，不需要额外建临时表，所有操作在同一个事务内完成：

WITH del_approval_ids AS (
    -- 先查询出所有需要删除的审批id
    SELECT id FROM approvalsubmission WHERE conclusiondate < now() - INTERVAL '5 year'
),
del_supplier_uris AS (
    -- 查询出关联的所有需要删除的uri
    SELECT DISTINCT automaticallybookedservices_uri AS uri
    FROM approvalsubmission_supplierbookingconfirmation
    WHERE approvalsubmission_id IN (SELECT id FROM del_approval_ids)
),
del_junction AS (
    -- 先删关联表
    DELETE FROM approvalsubmission_supplierbookingconfirmation
    WHERE approvalsubmission_id IN (SELECT id FROM del_approval_ids)
),
del_supplier AS (
    -- 再删供应商预订确认表
    DELETE FROM supplierbookingconfirmation
    WHERE uri IN (SELECT uri FROM del_supplier_uris)
)
-- 最后删审批主表
DELETE FROM approvalsubmission
WHERE id IN (SELECT id FROM del_approval_ids);

可选方案：修改外键为级联删除（需评估生产风险） #

如果业务逻辑允许，可以给两个外键增加ON DELETE CASCADE属性，这样删除主表数据时，关联表的对应记录会自动被删除，不需要手动处理关联顺序。但该操作需要修改表结构，生产环境建议先做充分测试再执行。

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