PostgreSQL 查询指定表所有外键引用列表的SQL脚本

不同数据库的系统元数据结构不同，以下是两类最常用数据库的实现语句，均满足需求：覆盖非主键字段被引用的场景、支持复合外键，返回字段完全匹配要求的格式。

PostgreSQL 版本 #

直接查询系统目录表，外键的每一组字段映射会单独返回一行，复合外键会按字段顺序逐行展示：

SELECT
    confrelid::regclass AS base_table,
    a2.attname AS base_col,
    relid::regclass AS referencing_table,
    a1.attname AS referencing_col,
    pg_get_constraintdef(c.oid) AS constraint_sql
FROM pg_constraint c
JOIN pg_attribute a1 
    ON a1.attrelid = c.conrelid 
    AND a1.attnum = ANY(c.conkey)
JOIN pg_attribute a2 
    ON a2.attrelid = c.confrelid 
    AND a2.attnum = ANY(c.confkey)
WHERE c.contype = 'f'
  AND confrelid = 'breeds'::regclass
ORDER BY referencing_table, constraint_sql, a1.attnum;

针对示例中的cats表，执行后返回结果完全符合预期：

base_table | base_col   | referencing_table | referencing_col | constraint_sql
-----------+------------+-------------------+-----------------+------------------------------------------------------------------------
breeds     | breed_name | cats              | cat_breed       | CONSTRAINT cat_breed_name FOREIGN KEY (cat_breed) REFERENCES breeds(breed_name)

MySQL 版本 #

查询information_schema内置库的元数据，同样支持复合外键、非主键引用场景：

SELECT
    kcu.REFERENCED_TABLE_NAME AS base_table,
    kcu.REFERENCED_COLUMN_NAME AS base_col,
    kcu.TABLE_NAME AS referencing_table,
    kcu.COLUMN_NAME AS referencing_col,
    CONCAT(
        'CONSTRAINT ', kcu.CONSTRAINT_NAME,
        ' FOREIGN KEY (', GROUP_CONCAT(kcu.COLUMN_NAME ORDER BY kcu.ORDINAL_POSITION SEPARATOR ', '),
        ') REFERENCES ', kcu.REFERENCED_TABLE_NAME,
        '(', GROUP_CONCAT(kcu.REFERENCED_COLUMN_NAME ORDER BY kcu.ORDINAL_POSITION SEPARATOR ', '), ')'
    ) AS constraint_sql
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE kcu.REFERENCED_TABLE_SCHEMA = DATABASE()
  AND kcu.REFERENCED_TABLE_NAME = 'breeds'
GROUP BY kcu.CONSTRAINT_NAME, kcu.COLUMN_NAME, kcu.REFERENCED_COLUMN_NAME
ORDER BY referencing_table, kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION;

返回字段说明：

• base_table：被引用的基础表，此处固定为breeds
• base_col：基础表中被引用的字段
• referencing_table：创建了外键约束的关联表
• referencing_col：关联表中对应的外键字段
• constraint_sql：外键约束的完整定义语句

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