PostgreSQL不可改表结构时如何删除主表及关联表匹配数据

PostgreSQL 不支持 MySQL 风格的单条语句多表联合删除语法，DELETE 语句单次仅能操作一张表，因此你编写的delete t2, rel from ...语法无法正常执行。
由于你没有修改表结构、添加外键级联删除规则的权限，最稳妥简洁的实现方式是在显式事务中借助CTE（公共表表达式）统一维护待删除数据的筛选逻辑，严格按外键依赖顺序依次删除三张表的对应数据，避免重复书写筛选条件导致的逻辑错误。

注意删除顺序必须遵循外键依赖规则：先删除关联关系表rel_table的记录解除外键绑定，再删除子表table2的关联数据，最后删除主表table1的目标记录，顺序颠倒会触发外键校验报错。

通用实现SQL #

BEGIN;

WITH target_t1 AS (
    -- 此处填写table1的删除筛选条件，仅需书写一次
    SELECT id FROM table1 WHERE id IN (...)
),
target_t2 AS (
    -- 捞出所有和待删table1记录关联的table2 ID
    SELECT DISTINCT id_table2 AS id 
    FROM rel_table 
    WHERE id_table1 IN (SELECT id FROM target_t1)
)
-- 第一步：删除关联关系表记录，解除外键约束
DELETE FROM rel_table WHERE id_table1 IN (SELECT id FROM target_t1);

-- 第二步：删除关联的table2记录
DELETE FROM table2 WHERE id IN (SELECT id FROM target_t2);

-- 第三步：删除table1的目标记录
DELETE FROM table1 WHERE id IN (SELECT id FROM target_t1);

COMMIT;

多对多场景适配 #

如果业务中存在一条table2记录同时被多条table1记录关联的情况，上述逻辑会误删仍被其他有效table1记录引用的table2数据，此时需要调整待删table2的筛选逻辑，仅删除无其他有效关联的记录，修改target_t2部分的逻辑即可：

target_t2 AS (
    SELECT id_table2 AS id 
    FROM rel_table r
    WHERE id_table1 IN (SELECT id FROM target_t1)
    -- 排除仍被不需要删除的table1记录关联的table2
    AND NOT EXISTS (
        SELECT 1 
        FROM rel_table r2 
        WHERE r2.id_table2 = r.id_table2 
        AND r2.id_table1 NOT IN (SELECT id FROM target_t1)
    )
)

方案优势 #

• 筛选条件仅需维护一次，不需要多次复制ID列表或重复书写过滤逻辑，从根源避免错漏
• 所有操作包裹在同一个事务中，任意步骤执行失败都会整体回滚，不会出现部分数据删除、部分留存的脏数据问题
• 完全不需要修改现有表结构、外键配置，符合你的操作权限限制

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