postgresql - 如何在 Postgresql 中获取 FK 引用的表

Question

对于给定的表，我试图获取所有列，包括它们的名称、类型、该列是主键还是外键，如果它是 FK，它指向哪个表。我在下面写了查询，但它似乎给了我引用列的内容，而不是相反:

select c.column_name, c.udt_name, constraint_type, kcu.table_name as references from information_schema.columns c
 left outer join information_schema.constraint_column_usage u on c.column_name=u.column_name
 left outer join information_schema.table_constraints t on u.constraint_name=t.constraint_name
 left outer join information_schema.key_column_usage AS kcu on t.constraint_name = kcu.constraint_name
where c.table_name=@name

我不太担心边缘情况，我只是想反转引用列。感谢您的宝贵时间。

Answer 1

这是一个从给定表中获取所有外键及其指向的查询:

SELECT  c.conname,
        t1.relname AS from_table,
        a1.attname AS from_column,
        t2.relname AS to_table,
        a2.attname AS to_column
FROM    pg_catalog.pg_constraint c,
        pg_catalog.pg_class t1,
        pg_catalog.pg_class t2,
        pg_catalog.pg_attribute a1,
        pg_catalog.pg_attribute a2,
        pg_catalog.pg_namespace n1,
        pg_catalog.pg_namespace n2
WHERE   c.conrelid = t1.oid
AND     c.confrelid = t2.oid
AND     c.contype = 'f'
AND     a1.attrelid = t1.oid
AND     a1.attnum = ANY(c.conkey)
AND     a2.attrelid = t2.oid
AND     a2.attnum = ANY(c.confkey)
AND     t1.relkind = 'r'
AND     t2.relkind = 'r'
AND     n1.oid = t1.relnamespace
AND     n2.oid = t2.relnamespace
AND     n1.nspname NOT IN ('pg_catalog', 'pg_toast')
AND     n2.nspname NOT IN ('pg_catalog', 'pg_toast')
AND     pg_catalog.pg_table_is_visible(t1.oid)
AND     pg_catalog.pg_table_is_visible(t2.oid)
AND     t1.relname = @name;                       

我看到您正在使用 information_schema 而不是 pg_catalog，所以最简单的方法可能是将我的查询转换为 CTE 或子查询，然后根据您的表名和列名。

编辑:听起来您只是需要一些帮助，将我的查询与您已有的查询结合起来。实际上，您的查询在如何进行连接方面存在很多错误，因此我重写了它以包含正确的外键结果:

SELECT  c.column_name,
        c.udt_name,
        t.constraint_type,
        t.constraint_name,
        x.to_table
FROM    information_schema.columns c
LEFT OUTER JOIN (
        SELECT  t.constraint_type,
                t.constraint_catalog,
                t.constraint_schema,
                t.constraint_name,
                t.table_catalog,
                t.table_schema,
                t.table_name,
                u.column_name
        FROM    information_schema.constraint_column_usage u
        LEFT OUTER JOIN information_schema.table_constraints t
        ON      t.table_catalog = u.table_catalog
        AND     t.table_schema = u.table_schema
        AND     t.table_name = u.table_name
        AND     t.constraint_catalog = u.constraint_catalog
        AND     t.constraint_schema = u.constraint_schema
        AND     t.constraint_name = u.constraint_name
        WHERE   t.constraint_type IS DISTINCT FROM 'FOREIGN KEY'
) t
ON      c.table_catalog = t.table_catalog
AND     c.table_schema = t.table_schema
AND     c.table_name = t.table_name
AND     c.column_name = t.column_name
LEFT OUTER JOIN (
  SELECT  c.conname,
          t1.relname AS from_table,
          a1.attname AS from_column,
          t2.relname AS to_table,
          a2.attname AS to_column
  FROM    pg_catalog.pg_constraint c,
          pg_catalog.pg_class t1,
          pg_catalog.pg_class t2,
          pg_catalog.pg_attribute a1,
          pg_catalog.pg_attribute a2,
          pg_catalog.pg_namespace n1,
          pg_catalog.pg_namespace n2
  WHERE   c.conrelid = t1.oid
  AND     c.confrelid = t2.oid
  AND     c.contype = 'f'
  AND     a1.attrelid = t1.oid
  AND     a1.attnum = ANY(c.conkey)
  AND     a2.attrelid = t2.oid
  AND     a2.attnum = ANY(c.confkey)
  AND     t1.relkind = 'r'
  AND     t2.relkind = 'r'
  AND     n1.oid = t1.relnamespace
  AND     n2.oid = t2.relnamespace
  AND     n1.nspname NOT IN ('pg_catalog', 'pg_toast')
  AND     n2.nspname NOT IN ('pg_catalog', 'pg_toast')
  AND     pg_catalog.pg_table_is_visible(t1.oid)
  AND     pg_catalog.pg_table_is_visible(t2.oid)
) x
ON      x.from_table = c.table_name
AND     x.from_column = c.column_name
WHERE   c.table_name = @cards
;

如果您有 NOT NULL 或 CHECK 约束，此版本还可以防止出现大量重复行。