如何实现PostgreSQL中删除父表行时自动删除关联子表行数据

看起来你搞反了外键的默认行为，PostgreSQL的外键默认不会自动删除子表记录——得手动给外键加上ON DELETE CASCADE选项才行。

先理清楚关系：表A是子表（因为它的外键引用了B、D、E的主键），B、D、E是父表。默认情况下，如果你尝试删除父表中被子表引用的记录，PostgreSQL会直接报错阻止你；而删除子表记录时，父表完全不受影响，这是正常的，因为外键约束是单向的——子表依赖父表，父表不依赖子表。

如果你希望删除父表记录时，所有关联的子表行自动被删除，按以下步骤操作：

1. 先检查现有外键的定义 #

首先运行这条SQL，查看你所有外键的当前配置，确认它们有没有ON DELETE CASCADE：

SELECT
    tc.table_schema,
    tc.table_name,
    kcu.column_name,
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name,
    tc.constraint_name,
    pg_get_constraintdef(tc.oid) AS constraint_def
FROM
    information_schema.table_constraints AS tc
JOIN
    information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN
    information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE
    tc.constraint_type = 'FOREIGN KEY'
    AND tc.table_schema IN ('schema1', 'schema2', 'schema3');

在返回的constraint_def列里，你会看到类似FOREIGN KEY (b_id) REFERENCES schema1.b(id)的内容，如果没有ON DELETE CASCADE，就说明这个外键没有级联删除的功能。

2. 修改外键，添加级联删除 #

对每个需要级联删除的外键，先删除原约束，再重新创建带ON DELETE CASCADE的约束。

举个例子，假设表schema1.a有个名为fk_a_b的外键，引用schema1.b的id列：

-- 先删除原外键约束
ALTER TABLE schema1.a DROP CONSTRAINT fk_a_b;

-- 重新创建外键，加上ON DELETE CASCADE
ALTER TABLE schema1.a
ADD CONSTRAINT fk_a_b
FOREIGN KEY (b_id) REFERENCES schema1.b(id)
ON DELETE CASCADE;

重复这个操作，给表A引用D、E的外键也加上ON DELETE CASCADE。

3. 验证效果 #

现在你尝试删除schema1.b中的一条记录，PostgreSQL会自动删除schema1.a中所有引用该记录主键的行，完全符合你的预期。

注意事项 #

• 操作外键时，尽量在业务低峰期进行，避免锁表影响正常业务。
• 如果你的表有多层依赖（比如还有表C引用表A的主键），记得给表C的外键也加上ON DELETE CASCADE，这样删除B时，A和C的关联行都会被递归删除。
• 如果你是想反过来：删除表A的记录时自动删除B、D、E中没有其他子表引用的记录——这没法用外键实现，得写触发器函数来处理这种“孤儿记录”的清理，不过看你的描述，应该不需要这个场景。

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