如何查找PostgreSQL中所有失效的外键约束

问题背景 #

我们发现PostgreSQL数据库存在外键约束工作异常的情况：子表中存在主表无匹配主键ID的外键数据。删除该外键约束后重建时，系统因存在孤立数据报错，清理数据后才成功重建。

此外，部分BEFORE DELETE触发器会因foreign_key_violation异常触发函数返回NULL而静默失败，这也是发现问题的契机：

EXCEPTION
    WHEN foreign_key_violation
        THEN RETURN NULL;

我们需要排查数据库中数千个外键，找出所有存在孤立数据的“失效”外键，目前拟定两种方案：

1. 预检查方案：查询所有外键，逐个执行统计语句定位孤立数据，再删除约束、清理数据后重建：

   select count(parent_id) from child_table
       where foreign_key_id not in (
           select parent_id as foreign_key_id
           from parent_table
       )
   );
   

2. 删除重建方案：生成所有外键的删除重建语句，执行时通过报错定位问题约束：

   SELECT 'Alter table ' || conrelid::regclass || ' drop constraint ' || conname || '; alter table ' || conrelid::regclass || ' add constraint ' ||  conname || ' ' || pg_get_constraintdef(oid) || ';'
   FROM   pg_constraint
   WHERE  contype = 'f'
     AND    connamespace = 'public'::regnamespace
   ORDER  BY conrelid::regclass::text, contype DESC;
   

现需确认：上述方案是否合理？从PostgreSQL中获取外键约束的最佳方式是什么？

方案合理性分析 #

两种方案均可行，各有优劣，可根据实际场景选择：

预检查方案 #

• 优势：提前统计所有问题外键，一次性规划修复操作，无需反复执行重建命令；能精准获取孤立数据量，便于评估修复影响范围。
• 注意事项：
  • 使用NOT IN时需警惕父表主键存在NULL值的情况——若存在NULL，整个NOT IN会返回空结果，导致统计失效。建议改用NOT EXISTS更稳妥：

    SELECT COUNT(*)
    FROM child_table c
    WHERE NOT EXISTS (
        SELECT 1
        FROM parent_table p
        WHERE p.parent_id = c.foreign_key_id
    );
    

  • 针对大表，逐表查询可能耗时较长，建议在业务低峰时段执行；或先添加LIMIT 1快速判断是否存在问题，后续再统计具体数量。

删除重建方案 #

• 优势：无需编写复杂的动态检查语句，直接利用PostgreSQL的约束验证机制自动检测问题；生成的语句能完整保留原外键的所有定义（包括ON UPDATE/ON DELETE规则、DEFERRABLE属性等）。
• 注意事项：
  • 删除外键期间，表处于无约束状态，若有写入操作可能引入新的孤立数据，建议在维护窗口执行，或开启事务（但大表重建外键可能长时间锁表，需提前评估影响）。
  • 需要手动记录报错的外键，反复执行直到全部成功，效率略低于预检查方案。

获取PostgreSQL外键约束的最佳方式 #

直接查询系统表pg_constraint是最可靠的方式，以下是优化后的查询语句，能更清晰地展示外键关联关系：

基础外键信息查询 #

SELECT
    conrelid::regclass AS child_table,
    conname AS fk_constraint_name,
    pg_get_constraintdef(oid) AS fk_definition,
    confrelid::regclass AS parent_table
FROM pg_constraint
WHERE contype = 'f'
  AND connamespace = 'public'::regnamespace
ORDER BY child_table, fk_constraint_name;

含字段映射的外键查询 #

如果需要获取外键字段与父表主键字段的对应关系，可关联pg_attribute查询：

SELECT
    conrelid::regclass AS child_table,
    a.attname AS child_column,
    confrelid::regclass AS parent_table,
    pa.attname AS parent_column,
    conname AS fk_constraint_name,
    pg_get_constraintdef(c.oid) AS fk_definition
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
JOIN pg_attribute pa ON pa.attrelid = c.confrelid AND pa.attnum = ANY(c.confkey)
WHERE contype = 'f'
  AND connamespace = 'public'::regnamespace
ORDER BY child_table, fk_constraint_name;

批量检查与修复的优化建议 #

1. 动态生成检查语句：利用系统表自动生成所有外键的孤立数据检查语句，避免手动编写：

   SELECT
       format(
           'SELECT ''%I'' AS child_table, ''%I'' AS fk_constraint, COUNT(*) AS orphaned_rows FROM %I c WHERE NOT EXISTS (SELECT 1 FROM %I p WHERE p.%I = c.%I);',
           conrelid::regclass,
           conname,
           conrelid::regclass,
           confrelid::regclass,
           pa.attname,
           a.attname
       ) AS check_query
   FROM pg_constraint c
   JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
   JOIN pg_attribute pa ON pa.attrelid = c.confrelid AND pa.attnum = ANY(c.confkey)
   WHERE contype = 'f'
     AND connamespace = 'public'::regnamespace;
   

   执行后可直接复制生成的语句批量运行，快速得到所有问题外键的统计结果。

2. 批量生成修复语句：针对已确认的问题外键，动态生成删除孤立数据+重建外键的语句：

   SELECT
       format(
           'DELETE FROM %I c WHERE NOT EXISTS (SELECT 1 FROM %I p WHERE p.%I = c.%I); ALTER TABLE %I DROP CONSTRAINT %I; ALTER TABLE %I ADD CONSTRAINT %I %s;',
           conrelid::regclass,
           confrelid::regclass,
           pa.attname,
           a.attname,
           conrelid::regclass,
           conname,
           conrelid::regclass,
           conname,
           pg_get_constraintdef(c.oid)
       ) AS fix_query
   FROM pg_constraint c
   JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
   JOIN pg_attribute pa ON pa.attrelid = c.confrelid AND pa.attnum = ANY(c.confkey)
   WHERE contype = 'f'
     AND connamespace = 'public'::regnamespace
     -- 可添加筛选条件，仅生成有问题外键的修复语句
     AND EXISTS (
         SELECT 1 FROM %I c WHERE NOT EXISTS (SELECT 1 FROM %I p WHERE p.%I = c.%I)
     );
   

   注意：执行删除操作前务必备份数据，或先执行SELECT确认待删除内容，避免误删。

3. 外键失效的可能原因：PostgreSQL外键本身不会自动失效，大概率是以下场景导致：

   • 初始数据导入时未启用外键，直接写入了不符合约束的数据
   • 外键被临时删除后未及时重建，期间写入了孤立数据
   • 使用SET CONSTRAINTS ... DEFERRED并在事务中写入了不符合约束的数据，事务提交时未触发检查（默认约束为IMMEDIATE，此场景较少）

内容的提问来源于Stack Exchange，提问作者irrational