PostgreSQL刷新脚本生成添加外键约束动态SQL出错，求排查

嘿，我看到你在写数据刷新脚本时，生成重建外键的动态SQL遇到了坑——返回了所有字段，还把父表名搞成了子表名。这其实是因为你没关联到正确的系统表，没法准确获取外键对应的字段和父表信息。让我帮你修正这个问题：

原语句的核心问题 #

1. 用错了表获取外键字段：你关联了INFORMATION_SCHEMA.COLUMNS，这会返回表的所有字段，而不是仅外键关联的字段。应该用INFORMATION_SCHEMA.KEY_COLUMN_USAGE，它专门记录约束和对应列的映射关系。
2. 父表信息没正确关联：REFERENTIAL_CONSTRAINTS里的unique_constraint_name对应父表上的唯一约束（通常是主键），你需要通过这个字段关联到父表的约束信息，才能拿到正确的父表名和父表字段。

修正后的动态SQL语句 #

下面这个查询会准确生成每个外键的重建语句，包括处理复合外键的情况，还会保留原有的更新/删除规则：

SELECT 
  'ALTER TABLE ' || quote_ident(cs.table_schema) || '.' || quote_ident(cs.table_name) ||
  ' ADD CONSTRAINT ' || quote_ident(rc.constraint_name) ||
  ' FOREIGN KEY (' || string_agg(quote_ident(kcu_child.column_name), ', ') || ')' ||
  ' REFERENCES ' || quote_ident(cs_parent.table_schema) || '.' || quote_ident(cs_parent.table_name) ||
  ' (' || string_agg(quote_ident(kcu_parent.column_name), ', ') || ')' ||
  COALESCE(' ON UPDATE ' || rc.update_rule, '') ||
  COALESCE(' ON DELETE ' || rc.delete_rule, '') || ';' AS add_fk_stmt
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS cs 
  ON cs.constraint_name = rc.constraint_name 
  AND cs.constraint_type = 'FOREIGN KEY'
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu_child
  ON kcu_child.constraint_name = rc.constraint_name
  AND kcu_child.table_schema = cs.table_schema
  AND kcu_child.table_name = cs.table_name
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS cs_parent
  ON cs_parent.constraint_name = rc.unique_constraint_name
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu_parent
  ON kcu_parent.constraint_name = rc.unique_constraint_name
  AND kcu_parent.table_schema = cs_parent.table_schema
  AND kcu_parent.table_name = cs_parent.table_name
  AND kcu_parent.ordinal_position = kcu_child.ordinal_position -- 保证复合外键列顺序一致
WHERE 
  UPPER(cs.table_schema) = 'SSP2_PCAT'
  AND UPPER(cs.table_name) IN ('ADDITIONAL_RULES', 'RATES') -- 替换成你的目标表列表
GROUP BY 
  cs.table_schema, cs.table_name, rc.constraint_name, 
  cs_parent.table_schema, cs_parent.table_name, 
  rc.update_rule, rc.delete_rule
ORDER BY 
  cs.table_name, rc.constraint_name;

关键细节说明 #

• quote_ident()：用来处理表名/列名包含特殊字符或关键字的情况，避免SQL语法错误，让语句更安全。
• KEY_COLUMN_USAGE：分别关联子表和父表的约束列，确保只获取外键对应的字段。
• ordinal_position：复合外键的列顺序必须和父表约束的列顺序完全一致，这个字段用来匹配对应关系，避免生成无效的外键。
• STRING_AGG()：把多列的外键拼接成逗号分隔的列表，完美处理复合外键场景。
• COALESCE()：如果外键的更新/删除规则是默认的（比如NO ACTION），会自动省略这部分，让生成的语句更简洁。

使用建议 #

生成语句后，先把结果输出检查一遍，确认每个ALTER TABLE语句的子表、外键列、父表、父表列都是正确的，再批量执行。这样能避免因为错误语句导致的问题。

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