如何为PostgreSQL数据库所有外键添加ON DELETE CASCADE（可限定schema）

一、SQL脚本方案 #

可以通过生成批量ALTER TABLE语句来实现，以下脚本会针对指定Schema生成所有需要修改的外键调整语句：

SELECT 
    'ALTER TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || 
    ' DROP CONSTRAINT ' || quote_ident(con.conname) || ', ADD CONSTRAINT ' || quote_ident(con.conname) || 
    ' FOREIGN KEY (' || array_to_string(array_agg(quote_ident(a.attname)), ', ') || ') REFERENCES ' || 
    quote_ident(pn.nspname) || '.' || quote_ident(pr.relname) || ' (' || array_to_string(array_agg(quote_ident(pa.attname)), ', ') || ') ON DELETE CASCADE;' AS alter_statement
FROM 
    pg_constraint con
JOIN 
    pg_class c ON con.conrelid = c.oid
JOIN 
    pg_namespace n ON c.relnamespace = n.oid
JOIN 
    pg_class pr ON con.confrelid = pr.oid
JOIN 
    pg_namespace pn ON pr.relnamespace = pn.oid
JOIN 
    pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(con.conkey)
JOIN 
    pg_attribute pa ON pa.attrelid = pr.oid AND pa.attnum = ANY(con.confkey)
WHERE 
    con.contype = 'f'
    AND n.nspname = 'your_schema_name' -- 替换成你的目标Schema名称
GROUP BY 
    n.nspname, c.relname, con.conname, pn.nspname, pr.relname;

使用步骤： #

• 将your_schema_name替换为实际要操作的Schema名称
• 执行查询后，会得到一系列完整的ALTER TABLE语句
• 务必先备份数据库，再测试执行部分语句，确认无误后批量运行所有生成的语句

二、GUI工具方案 #

1. pgAdmin（官方工具） #

• 连接目标数据库，左侧导航栏展开到指定Schema，找到目标表
• 展开表的Constraints目录，右键点击要修改的外键，选择「Properties」
• 在「Definition」标签页，找到「On delete」下拉框，选择「Cascade」
• 点击「Save」完成修改

若需批量处理，可配合上述脚本生成语句，在pgAdmin的「Query Tool」中批量执行。

2. DBeaver（通用数据库工具） #

• 连接数据库后，定位到指定Schema的表，右键选择「Generate SQL」→「Generate DDL」
• 在DDL生成界面，筛选出外键相关语句，用批量替换功能添加ON DELETE CASCADE
• 部分版本支持直接在约束列表中批量选中外键，统一修改「On Delete」属性

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