如何通过SQL批量修改PostgreSQL中外键指向至新表

步骤1：生成删除所有指向表X的外键SQL #

执行以下查询，获取所有删除外键的语句：

SELECT format(
    'ALTER TABLE %I.%I DROP CONSTRAINT %I;',
    tc.table_schema,
    tc.table_name,
    tc.constraint_name
) AS drop_fk_sql
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND kcu.referenced_table_schema = 'public' -- 替换为表X所在的schema，默认public
    AND kcu.referenced_table_name = 'X'; -- 替换为损坏表名X

将查询结果复制执行，完成原外键的删除。

步骤2：生成创建指向表Y的新外键SQL #

如果是单字段外键，执行以下查询生成创建语句：

SELECT format(
    'ALTER TABLE %I.%I ADD CONSTRAINT %I FOREIGN KEY (%I) REFERENCES %I.%I(%I)%s;',
    tc.table_schema,
    tc.table_name,
    tc.constraint_name, -- 直接复用原外键名称，已删除原约束无冲突
    kcu.column_name,
    'public', -- 表Y所在的schema，与X一致即可
    'Y', -- 新表名Y
    rc_kcu.column_name,
    CASE WHEN tc.is_deferrable = 'YES' THEN ' DEFERRABLE' ELSE '' END ||
    CASE WHEN tc.initially_deferred = 'YES' THEN ' INITIALLY DEFERRED' ELSE '' END
) AS create_fk_sql
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.referential_constraints rc
    ON tc.constraint_name = rc.constraint_name
JOIN information_schema.key_column_usage rc_kcu
    ON rc.unique_constraint_name = rc_kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND kcu.referenced_table_schema = 'public'
    AND kcu.referenced_table_name = 'X';

如果存在复合外键（多字段关联），改用以下查询：

SELECT format(
    'ALTER TABLE %I.%I ADD CONSTRAINT %I FOREIGN KEY (%s) REFERENCES %I.%I(%s)%s;',
    tc.table_schema,
    tc.table_name,
    tc.constraint_name,
    string_agg(kcu.column_name, ', ' ORDER BY kcu.ordinal_position),
    'public',
    'Y',
    string_agg(rc_kcu.column_name, ', ' ORDER BY rc_kcu.ordinal_position),
    CASE WHEN tc.is_deferrable = 'YES' THEN ' DEFERRABLE' ELSE '' END ||
    CASE WHEN tc.initially_deferred = 'YES' THEN ' INITIALLY DEFERRED' ELSE '' END
) AS create_fk_sql
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.referential_constraints rc
    ON tc.constraint_name = rc.constraint_name
JOIN information_schema.key_column_usage rc_kcu
    ON rc.unique_constraint_name = rc_kcu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND kcu.referenced_table_schema = 'public'
    AND kcu.referenced_table_name = 'X'
GROUP BY tc.table_schema, tc.table_name, tc.constraint_name, tc.is_deferrable, tc.initially_deferred;

复制查询结果执行，完成新外键的创建。

步骤3：验证外键关联 #

执行以下查询确认所有外键已指向Y：

SELECT tc.table_name, tc.constraint_name, kcu.referenced_table_name
FROM information_schema.table_constraints tc
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 = 'Y';

核对结果数量与原指向X的外键数量一致即可。

步骤4：删除损坏表X #

确认外键修改无误后，执行删除语句：

DROP TABLE X;

注意：操作前务必做好全库备份，避免意外；替换SQL中的public、X、Y为实际的schema和表名。

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