Postgres:列出表外键的SQL

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列出给定表的所有外键?我知道表名/模式，我可以代入它。

20 个解决方案

#1

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
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';

#2

psql does this, and if you start psql with:

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

#3

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

#4

Extension to ollyc recipe :

扩展到奥利克食谱:

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';

从foreign_keys_view中选择*，其中table_name='YourTableNameHere';

#5

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

在PostgreSQL提示符上发出\d+ tablename，除了显示表列的数据类型之外，它还将显示索引和外键。

#6

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

检查ff post，找到你的解决方案，当你发现这个有用的时候，别忘了标记一下

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 c ON c.oid = o.conrelid
  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');

#7

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

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

#8

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

#9

You can use the PostgreSQL system catalogs. Maybe you can query pg_constraint to ask for foreign keys. You can also use the Information Schema

您可以使用PostgreSQL系统编目。也许您可以查询pg_constraint以获取外键。您还可以使用信息模式

#10

Here is a solution by Andreas Joseph Krogh from the PostgreSQL mailing list: http://www.postgresql.org/message-id/[e-mail]

以下是来自PostgreSQL邮件列表的Andreas Joseph Krogh的解决方案:http://www.postgresql.org/message-id/[e-mail]

SELECT source_table::regclass, source_attr.attname AS source_column,
    target_table::regclass, target_attr.attname AS target_column
FROM pg_attribute target_attr, pg_attribute source_attr,
  (SELECT source_table, target_table, source_constraints[i] source_constraints, target_constraints[i] AS target_constraints
   FROM
     (SELECT conrelid as source_table, confrelid AS target_table, conkey AS source_constraints, confkey AS target_constraints,
       generate_series(1, array_upper(conkey, 1)) AS i
      FROM pg_constraint
      WHERE contype = 'f'
     ) query1
  ) query2
WHERE target_attr.attnum = target_constraints AND target_attr.attrelid = target_table AND
      source_attr.attnum = source_constraints AND source_attr.attrelid = source_table;

This solution handles foreign keys that reference multiple columns, and avoids duplicates (which some of the other answers fail to do). The only thing I changed were the variable names.

此解决方案处理引用多个列的外键，并避免重复(其他一些答案无法做到)。我唯一改变的是变量名。

Here is an example that returns all employee columns that reference the permission table:

这里有一个示例，它返回引用权限表的所有employee列:

SELECT source_column
FROM foreign_keys
WHERE source_table = 'employee'::regclass AND target_table = 'permission'::regclass;

#11

To expand upon Martin's excellent answer here is a query that lets you filter based on the parent table and shows you the name of the child table with each parent table so you can see all of the dependent tables/columns based upon the foreign key constraints in the parent table.

扩大在马丁的优秀的答案是一个查询,它让你过滤基于父表和显示与每个父表的子表的名称你可以看到所有相关的表或列基于父表的外键约束。

select 
    con.constraint_name,
    att2.attname as "child_column", 
    cl.relname as "parent_table", 
    att.attname as "parent_column",
    con.child_table,
    con.child_schema
from
   (select 
        unnest(con1.conkey) as "parent", 
        unnest(con1.confkey) as "child", 
        con1.conname as constraint_name,
        con1.confrelid, 
        con1.conrelid,
        cl.relname as child_table,
        ns.nspname as child_schema
    from 
        pg_class cl
        join pg_namespace ns on cl.relnamespace = ns.oid
        join pg_constraint con1 on con1.conrelid = cl.oid
    where  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
   where cl.relname like '%parent_table%'       

#12

Use the name of the Primary Key to which the Keys are referencing and query the information_schema:

使用键引用的主键的名称并查询information_schema:

select table_name, column_name
from information_schema.key_column_usage
where constraint_name IN (select constraint_name
  from information_schema.referential_constraints 
  where unique_constraint_name = 'TABLE_NAME_pkey')

Here 'TABLE_NAME_pkey' is the name of the Primary Key referenced by the Foreign Keys.

这里的“TABLE_NAME_pkey”是外键引用的主键的名称。

#13

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_cols and to_cols could be vastly simplified on Postgres 9.4 and later using WITH ORDINALITY rather than the window-function-using hackery I'm using.
• 用于生成from_cols和to_cols的表达式可以在Postgres 9.4上得到极大的简化，然后使用序数而不是使用我正在使用的windows函数hackery。
• 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.
• 这些表达式依赖于查询计划器，而不改变UNNEST返回的结果顺序。我不认为它会，但是我的数据集里没有任何多列外键来测试。添加9.4细节消除了这种可能性。
• The query itself requires Postgres 9.0 or later (8.x didn't allow ORDER BY in aggregate functions)
• 查询本身需要Postgres 9.0或更高版本(8)。在聚合函数中x不允许排序)
• Replace STRING_AGG with ARRAY_AGG if you want an array of columns rather than a comma-separated string.
• 如果您想要一个列数组而不是一个逗号分隔的字符串，请使用ARRAY_AGG替换STRING_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;

#14

SELECT r.conname
      ,ct.table_name
      ,pg_catalog.pg_get_constraintdef(r.oid, true) as condef
  FROM pg_catalog.pg_constraint r, information_schema.constraint_table_usage ct
 WHERE r.contype = 'f' 
   AND r.conname = ct.constraint_name
 ORDER BY 1

#15

I wrote a solution that like and use frequently. The code is at http://code.google.com/p/pgutils/. See the pgutils.foreign_keys view.

我写了一个经常使用的解决方案。代码在http://code.google.com/p/p/pgutils/。看到pgutils。foreign_keys视图。

Unfortunately, the output is too wordy to include here. However, you can try it on a public version of the database here, like this:

不幸的是，输出太过冗长，不能包含在这里。但是，您可以在这个数据库的公共版本上尝试它，如下所示:

$ psql -h unison-db.org -U PUBLIC -d unison -c 'select * from pgutils.foreign_keys;

This works with 8.3 at least. I anticipate updating it, if needed, in the next few months.

这至少适用于8.3。如果需要的话，我希望在接下来的几个月里更新它。

-Reece

莉丝

#16

I created little tool to query and then compare database schema: Dump PostgreSQL db schema to text

我创建了一个小工具来查询，然后比较数据库模式:Dump PostgreSQL db模式到文本

There is info about FK, but ollyc response gives more details.

有关于FK的信息，但是ollyc的回复提供了更多的细节。

#17

One another way:

另一种方法:

WITH foreign_keys AS (
    SELECT
      conname,
      conrelid,
      confrelid,
      unnest(conkey)  AS conkey,
      unnest(confkey) AS confkey
    FROM pg_constraint
    WHERE contype = 'f' -- AND confrelid::regclass = 'your_table'::regclass
)
-- if confrelid, conname pair shows up more than once then it is multicolumn foreign key
SELECT fk.conname as constraint_name,
       fk.confrelid::regclass as referenced_table, af.attname as pkcol,
       fk.conrelid::regclass as referencing_table, a.attname as fkcol
FROM foreign_keys fk
JOIN pg_attribute af ON af.attnum = fk.confkey AND af.attrelid = fk.confrelid
JOIN pg_attribute a ON a.attnum = conkey AND a.attrelid = fk.conrelid
ORDER BY fk.confrelid, fk.conname
;

#18

Proper solution to the problem, using information_schema, working with multi column keys, joining columns of different names in both tables correctly and also compatible with ms sqlsever:

正确的解决方案，使用information_schema，使用多列键，在两个表中正确地连接不同名称的列，并且与ms sqlsever兼容:

select fks.TABLE_NAME as foreign_key_table_name
, fks.CONSTRAINT_NAME as foreign_key_constraint_name
, kcu_foreign.COLUMN_NAME as foreign_key_column_name
, rc.UNIQUE_CONSTRAINT_NAME as primary_key_constraint_name
, pks.TABLE_NAME as primary_key_table_name
, kcu_primary.COLUMN_NAME as primary_key_column_name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS fks -- foreign keys
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu_foreign -- the columns of the above keys
    on fks.TABLE_CATALOG = kcu_foreign.TABLE_CATALOG
    and fks.TABLE_SCHEMA = kcu_foreign.TABLE_SCHEMA
    and fks.TABLE_NAME = kcu_foreign.TABLE_NAME
    and fks.CONSTRAINT_NAME = kcu_foreign.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc -- referenced constraints
    on rc.CONSTRAINT_CATALOG = fks.CONSTRAINT_CATALOG
    and rc.CONSTRAINT_SCHEMA = fks.CONSTRAINT_SCHEMA
    and rc.CONSTRAINT_NAME = fks.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS pks -- primary keys (referenced by fks)
    on rc.UNIQUE_CONSTRAINT_CATALOG = pks.CONSTRAINT_CATALOG
    and rc.UNIQUE_CONSTRAINT_SCHEMA = pks.CONSTRAINT_SCHEMA
    and rc.UNIQUE_CONSTRAINT_NAME = pks.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu_primary
    on pks.TABLE_CATALOG = kcu_primary.TABLE_CATALOG
    and pks.TABLE_SCHEMA = kcu_primary.TABLE_SCHEMA
    and pks.TABLE_NAME = kcu_primary.TABLE_NAME
    and pks.CONSTRAINT_NAME = kcu_primary.CONSTRAINT_NAME
    and kcu_foreign.ORDINAL_POSITION = kcu_primary.ORDINAL_POSITION -- this joins the columns
where fks.TABLE_SCHEMA = 'dbo' -- replace with schema name
and fks.TABLE_NAME = 'your_table_name' -- replace with table name
and fks.CONSTRAINT_TYPE = 'FOREIGN KEY'
and pks.CONSTRAINT_TYPE = 'PRIMARY KEY'
order by fks.constraint_name, kcu_foreign.ORDINAL_POSITION

Note: There are some differences between potgresql and sqlserver implementations of information_schema which make the top answer give different results on the two systems - one shows column names for the foreign key table the other for the primary key table. For this reason I decided to use KEY_COLUMN_USAGE view instead.

注意:在information_schema的potgresql和sqlserver实现之间存在一些差异，这使得上面的答案在两个系统上给出不同的结果——一个显示外键表的列名，另一个显示主键表的列名。出于这个原因，我决定使用KEY_COLUMN_USAGE视图。

#19

Note: Do not forget column's order while reading constraint columns!

注意:在读取约束列时，不要忘记列的顺序!

SELECT conname, attname
  FROM pg_catalog.pg_constraint c 
  JOIN pg_catalog.pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
 WHERE attrelid = 'schema.table_name'::regclass
 ORDER BY conname, array_position(c.conkey, a.attnum)

#20

This is what I'm currently using, it will list a table and it's fkey constraints [remove table clause and it will list all tables in current catalog]:

这是我目前使用的，它将列出一个表，它是fkey约束[移除表子句，它将列出当前目录中的所有表]:

SELECT

    current_schema() AS "schema",
    current_catalog AS "database",
    "pg_constraint".conrelid::regclass::text AS "primary_table_name",
    "pg_constraint".confrelid::regclass::text AS "foreign_table_name",

    (
        string_to_array(
            (
                string_to_array(
                    pg_get_constraintdef("pg_constraint".oid),
                    '('
                )
            )[2],
            ')'
        )
    )[1] AS "foreign_column_name",

    "pg_constraint".conindid::regclass::text AS "constraint_name",

    TRIM((
        string_to_array(
            pg_get_constraintdef("pg_constraint".oid),
            '('
        )
    )[1]) AS "constraint_type",

    pg_get_constraintdef("pg_constraint".oid) AS "constraint_definition"

FROM pg_constraint AS "pg_constraint"

JOIN pg_namespace AS "pg_namespace" ON "pg_namespace".oid = "pg_constraint".connamespace

WHERE
    --fkey and pkey constraints
    "pg_constraint".contype IN ( 'f', 'p' )
    AND
    "pg_namespace".nspname = current_schema()
    AND
    "pg_constraint".conrelid::regclass::text IN ('whatever_table_name')