Postgres：列出表外键的 SQL

Postgres: SQL to list table foreign keys

提问by smack0007

Is there a way using SQL to list all foreign keys for a given table? I know the table name / schema and I can plug that in.

有没有办法使用 SQL 列出给定表的所有外键？我知道表名/模式，我可以插入它。

回答by ollyc

You can do this via the information_schema tables. For example:

您可以通过 information_schema 表执行此操作。例如：

SELECT
    tc.table_schema, 
    tc.constraint_name, 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
      AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
      AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';

回答by Magnus Hagander

psql does this, and if you start psql with:

psql 执行此操作，如果您使用以下命令启动 psql：

psql -E

it will show you exactly what query is executed. In the case of finding foreign keys, it's:

它将准确显示执行的查询。在查找外键的情况下，它是：

SELECT conname,
  pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '16485' AND r.contype = 'f' ORDER BY 1

In this case, 16485 is the oid of the table I'm looking at - you can get that one by just casting your tablename to regclass like:

在这种情况下， 16485 是我正在查看的表的 oid - 您只需将表名转换为 regclass 即可获得该表，例如：

WHERE r.conrelid = 'mytable'::regclass

Schema-qualify the table name if it's not unique (or the first in your search_path):

如果表名不是唯一的（或您的 中的第一个），则对表名进行模式限定search_path：

WHERE r.conrelid = 'myschema.mytable'::regclass

回答by Gre Hahn

Issue \d+ tablenameon PostgreSQL prompt, in addition to showing table column's data types it'll show the indexes and foreign keys.

问题\d+ tablenamePostgreSQL的提示，除了显示表列的数据类型，它会显示索引和外键。

回答by martin

Ollyc's answer is good as it is not Postgres-specific, however, it breaks down when the foreign key references more than one column. The following query works for arbitrary number of columns but it relies heavily on Postgres extensions:

Ollyc 的回答很好，因为它不是 Postgres 特定的，但是，当外键引用多于一列时，它会崩溃。以下查询适用于任意数量的列，但它严重依赖 Postgres 扩展：

select 
    att2.attname as "child_column", 
    cl.relname as "parent_table", 
    att.attname as "parent_column",
    conname
from
   (select 
        unnest(con1.conkey) as "parent", 
        unnest(con1.confkey) as "child", 
        con1.confrelid, 
        con1.conrelid,
        con1.conname
    from 
        pg_class cl
        join pg_namespace ns on cl.relnamespace = ns.oid
        join pg_constraint con1 on con1.conrelid = cl.oid
    where
        cl.relname = 'child_table'
        and ns.nspname = 'child_schema'
        and con1.contype = 'f'
   ) con
   join pg_attribute att on
       att.attrelid = con.confrelid and att.attnum = con.child
   join pg_class cl on
       cl.oid = con.confrelid
   join pg_attribute att2 on
       att2.attrelid = con.conrelid and att2.attnum = con.parent

回答by Mvoicem

Extension to ollyc recipe :

ollyc 配方的扩展：

CREATE VIEW foreign_keys_view AS
SELECT
    tc.table_name, kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage 
        AS kcu ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage 
        AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY';

Then:

然后：

SELECT * FROM foreign_keys_view WHERE table_name='YourTableNameHere';

SELECT * FROM foreign_keys_view WHERE table_name='YourTableNameHere';

回答by sheldon

check the ff post for your solution and don't forget to mark this when you fine this helpful

检查 ff 帖子以获取您的解决方案，当您觉得这很有帮助时不要忘记标记它

http://errorbank.blogspot.com/2011/03/list-all-foreign-keys-references-for.html

http://errorbank.blogspot.com/2011/03/list-all-foreign-keys-references-for.html

SELECT
  o.conname AS constraint_name,
  (SELECT nspname FROM pg_namespace WHERE oid=m.relnamespace) AS source_schema,
  m.relname AS source_table,
  (SELECT a.attname FROM pg_attribute a WHERE a.attrelid = m.oid AND a.attnum = o.conkey[1] AND a.attisdropped = false) AS source_column,
  (SELECT nspname FROM pg_namespace WHERE oid=f.relnamespace) AS target_schema,
  f.relname AS target_table,
  (SELECT a.attname FROM pg_attribute a WHERE a.attrelid = f.oid AND a.attnum = o.confkey[1] AND a.attisdropped = false) AS target_column
FROM
  pg_constraint o LEFT JOIN pg_class f ON f.oid = o.confrelid LEFT JOIN pg_class m ON m.oid = o.conrelid
WHERE
  o.contype = 'f' AND o.conrelid IN (SELECT oid FROM pg_class c WHERE c.relkind = 'r');

回答by oscavi

This query works correct with composite keys also:

此查询也适用于复合键：

select c.constraint_name
    , x.table_schema as schema_name
    , x.table_name
    , x.column_name
    , y.table_schema as foreign_schema_name
    , y.table_name as foreign_table_name
    , y.column_name as foreign_column_name
from information_schema.referential_constraints c
join information_schema.key_column_usage x
    on x.constraint_name = c.constraint_name
join information_schema.key_column_usage y
    on y.ordinal_position = x.position_in_unique_constraint
    and y.constraint_name = c.unique_constraint_name
order by c.constraint_name, x.ordinal_position

回答by Shaun McCready

I think what you were looking for and very close to what @ollyc wrote is this:

我认为您正在寻找并且非常接近@ollyc 所写的是：

SELECT
tc.constraint_name, tc.table_name, kcu.column_name, 
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name 
FROM 
information_schema.table_constraints AS tc 
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name='YourTableNameHere';

This will list all the tables that use your specified table as a foreign key

这将列出使用您指定的表作为外键的所有表

回答by NikhilP

short but sweet upvote if it works for you.

如果它适合你，简短但甜蜜的upvote。

select  * from information_schema.key_column_usage where constraint_catalog=current_catalog and table_name='your_table_name' and position_in_unique_constraint notnull;

回答by dewin

None of the existing answers gave me results in the form that I actually wanted them in. So here is my (gargantuan) query for finding information about foreign keys.

现有的答案都没有以我真正想要的形式给我结果。所以这是我的（庞大的）查询，用于查找有关外键的信息。

A few notes:

一些注意事项：

• The expressions used to generate from_colsand to_colscould be vastly simplified on Postgres 9.4 and later using WITH ORDINALITYrather than the window-function-using hackery I'm using.
• Those same expressions are relying on the query planner not altering the returned order of results from UNNEST. I don't think it will, but I don't have any multiple-column foreign keys in my dataset to test with. Adding the 9.4 niceties eliminates this possibility altogether.
• The query itself requires Postgres 9.0 or later (8.x didn't allow ORDER BYin aggregate functions)
• Replace STRING_AGGwith ARRAY_AGGif you want an array of columns rather than a comma-separated string.

• 用于生成from_cols并且to_cols可以在 Postgres 9.4 及更高版本上大大简化的表达式，WITH ORDINALITY而不是我正在使用的使用窗口函数的hackery。
• 这些相同的表达式依赖于查询规划器不会改变从UNNEST. 我不认为它会，但我的数据集中没有任何多列外键可以测试。添加 9.4 的细节完全消除了这种可能性。
• 查询本身需要 Postgres 9.0 或更高版本（8.x 不允许ORDER BY在聚合函数中）
• 如果您想要一个列数组而不是逗号分隔的字符串，请替换STRING_AGG为ARRAY_AGG。

-

——

SELECT
    c.conname AS constraint_name,
    (SELECT n.nspname FROM pg_namespace AS n WHERE n.oid=c.connamespace) AS constraint_schema,

    tf.name AS from_table,
    (
        SELECT STRING_AGG(QUOTE_IDENT(a.attname), ', ' ORDER BY t.seq)
        FROM
            (
                SELECT
                    ROW_NUMBER() OVER (ROWS UNBOUNDED PRECEDING) AS seq,
                    attnum
                FROM
                    UNNEST(c.conkey) AS t(attnum)
            ) AS t
            INNER JOIN pg_attribute AS a ON a.attrelid=c.conrelid AND a.attnum=t.attnum
    ) AS from_cols,

    tt.name AS to_table,
    (
        SELECT STRING_AGG(QUOTE_IDENT(a.attname), ', ' ORDER BY t.seq)
        FROM
            (
                SELECT
                    ROW_NUMBER() OVER (ROWS UNBOUNDED PRECEDING) AS seq,
                    attnum
                FROM
                    UNNEST(c.confkey) AS t(attnum)
            ) AS t
            INNER JOIN pg_attribute AS a ON a.attrelid=c.confrelid AND a.attnum=t.attnum
    ) AS to_cols,

    CASE confupdtype WHEN 'r' THEN 'restrict' WHEN 'c' THEN 'cascade' WHEN 'n' THEN 'set null' WHEN 'd' THEN 'set default' WHEN 'a' THEN 'no action' ELSE NULL END AS on_update,
    CASE confdeltype WHEN 'r' THEN 'restrict' WHEN 'c' THEN 'cascade' WHEN 'n' THEN 'set null' WHEN 'd' THEN 'set default' WHEN 'a' THEN 'no action' ELSE NULL END AS on_delete,
    CASE confmatchtype::text WHEN 'f' THEN 'full' WHEN 'p' THEN 'partial' WHEN 'u' THEN 'simple' WHEN 's' THEN 'simple' ELSE NULL END AS match_type,  -- In earlier postgres docs, simple was 'u'nspecified, but current versions use 's'imple.  text cast is required.

    pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM
    pg_catalog.pg_constraint AS c
    INNER JOIN (
        SELECT pg_class.oid, QUOTE_IDENT(pg_namespace.nspname) || '.' || QUOTE_IDENT(pg_class.relname) AS name
        FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
    ) AS tf ON tf.oid=c.conrelid
    INNER JOIN (
        SELECT pg_class.oid, QUOTE_IDENT(pg_namespace.nspname) || '.' || QUOTE_IDENT(pg_class.relname) AS name
        FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
    ) AS tt ON tt.oid=c.confrelid
WHERE c.contype = 'f' ORDER BY 1;