PostgreSQL：用于获取具有特定列作为外键的所有表的列表的 SQL 脚本

PostgreSQL: SQL script to get a list of all tables that has a particular column as foreign key

提问by Gaurav Dadhania

I'm using PostgreSQL and I'm trying to list all the tables that have a particular column from a table as a foreign-key/reference. Can this be done? I'm sure this information is stored somewhere in information_schemabut I have no idea how to start querying it.

我正在使用 PostgreSQL，并且我正在尝试列出所有具有表中特定列作为外键/引用的表。这能做到吗？我确定这些信息存储在某个地方，information_schema但我不知道如何开始查询它。

回答by RichardTheKiwi

select R.TABLE_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
    on U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
    and U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
    and U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE R
    ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
WHERE U.COLUMN_NAME = 'a'
  AND U.TABLE_CATALOG = 'b'
  AND U.TABLE_SCHEMA = 'c'
  AND U.TABLE_NAME = 'd'

This uses the full catalog/schema/name triplet to identify a db table from all 3 information_schema views. You can drop one or two as required.

这使用完整的目录/模式/名称三元组从所有 3 个 information_schema 视图中标识一个 db 表。您可以根据需要删除一两个。

The query lists all tables that have a foreign key constraint against the column 'a' in table 'd'

该查询列出了对表 'd' 中的列 'a' 具有外键约束的所有表

回答by Tony K.

The other solutions are not guaranteed to work in postgresql, as the constraint_name is not guaranteed to be unique; thus you will get false positives. PostgreSQL used to name constraints silly things like '$1', and if you've got an old database you've been maintaining through upgrades, you likely still have some of those around.

其他解决方案不能保证在 postgresql 中工作，因为不保证constraint_name 是唯一的；因此你会得到误报。PostgreSQL 曾经将约束命名为诸如“$1”之类的愚蠢事物，如果您有一个旧数据库，并且一直在通过升级进行维护，那么您可能仍然有一些旧数据库。

Since this question was targeted AT PostgreSQL and that is what you are using, then you can query the internal postgres tables pg_class and pg_attribute to get a more accurate result.

由于这个问题是针对 AT PostgreSQL 而你正在使用的，那么你可以查询内部 postgres 表 pg_class 和 pg_attribute 以获得更准确的结果。

NOTE: FKs can be on multiple columns, thus the referencing column (attnum of pg_attribute) is an ARRAY, which is the reason for using array_agg in the answer.

注意：FK 可以在多个列上，因此引用列（pg_attribute 的属性）是一个数组，这就是在答案中使用 array_agg 的原因。

The only thing you need plug in is the TARGET_TABLE_NAME:

您唯一需要插入的是 TARGET_TABLE_NAME：

select 
  (select r.relname from pg_class r where r.oid = c.conrelid) as table, 
  (select array_agg(attname) from pg_attribute 
   where attrelid = c.conrelid and ARRAY[attnum] <@ c.conkey) as col, 
  (select r.relname from pg_class r where r.oid = c.confrelid) as ftable 
from pg_constraint c 
where c.confrelid = (select oid from pg_class where relname = 'TARGET_TABLE_NAME');

If you want to go the other way (list all of the things a specific table refers to), then just change the last line to:

如果您想反其道而行（列出特定表所指的所有内容），只需将最后一行更改为：

where c.conrelid = (select oid from pg_class where relname = 'TARGET_TABLE_NAME');

Oh, and since the actual question was to target a specific column, you can specify the column name with this one:

哦，由于实际问题是针对特定列，您可以使用此列指定列名：

select (select r.relname from pg_class r where r.oid = c.conrelid) as table, 
       (select array_agg(attname) from pg_attribute 
        where attrelid = c.conrelid and ARRAY[attnum] <@ c.conkey) as col, 
       (select r.relname from pg_class r where r.oid = c.confrelid) as ftable 
from pg_constraint c 
where c.confrelid = (select oid from pg_class where relname = 'TARGET_TABLE_NAME') and 
      c.confkey @> (select array_agg(attnum) from pg_attribute 
                    where attname = 'TARGET_COLUMN_NAME' and attrelid = c.confrelid);

回答by Anomie

Personally, I prefer to query based on the referenced unique constraint rather than the column. That would look something like this:

就个人而言，我更喜欢根据引用的唯一约束而不是列进行查询。这看起来像这样：

SELECT rc.constraint_catalog,
       rc.constraint_schema||'.'||tc.table_name AS table_name,
       kcu.column_name,
       match_option,
       update_rule,
       delete_rule
FROM information_schema.referential_constraints AS rc 
    JOIN information_schema.table_constraints AS tc USING(constraint_catalog,constraint_schema,constraint_name)
    JOIN information_schema.key_column_usage AS kcu USING(constraint_catalog,constraint_schema,constraint_name)
WHERE unique_constraint_catalog='catalog'
    AND unique_constraint_schema='schema'
    AND unique_constraint_name='constraint name';

---

---

Here is a version that allows querying by column name:

这是一个允许按列名查询的版本：

SELECT rc.constraint_catalog,
       rc.constraint_schema||'.'||tc.table_name AS table_name,
       kcu.column_name,
       match_option,
       update_rule,
       delete_rule
FROM information_schema.referential_constraints AS rc
    JOIN information_schema.table_constraints AS tc USING(constraint_catalog,constraint_schema,constraint_name)
    JOIN information_schema.key_column_usage AS kcu USING(constraint_catalog,constraint_schema,constraint_name)
    JOIN information_schema.key_column_usage AS ccu ON(ccu.constraint_catalog=rc.unique_constraint_catalog AND ccu.constraint_schema=rc.unique_constraint_schema AND ccu.constraint_name=rc.unique_constraint_name)
WHERE ccu.table_catalog='catalog'
    AND ccu.table_schema='schema'
    AND ccu.table_name='name'
    AND ccu.column_name='column';

回答by vallismortis

This query requires onlythe referenced table nameand column name, and produces a result set containing both sides of the foreign key.

此查询仅需要引用的表名和列名，并生成包含外键两侧的结果集。

select confrelid::regclass, af.attname as fcol,
       conrelid::regclass, a.attname as col
from pg_attribute af, pg_attribute a,
  (select conrelid,confrelid,conkey[i] as conkey, confkey[i] as confkey
   from (select conrelid,confrelid,conkey,confkey,
                generate_series(1,array_upper(conkey,1)) as i
         from pg_constraint where contype = 'f') ss) ss2
where af.attnum = confkey and af.attrelid = confrelid and
      a.attnum = conkey and a.attrelid = conrelid 
  AND confrelid::regclass = 'my_table'::regclass AND af.attname = 'my_referenced_column';

---

---

Example result set:

结果集示例：

confrelid |         fcol         |   conrelid    |     col
----------+----------------------+---------------+-------------
 my_table | my_referenced_column | some_relation | source_type
 my_table | my_referenced_column | some_feature  | source_type

---

---

All credit to Lane and Krogh at the PostgreSQL forum.

全部归功于PostgreSQL 论坛上的Lane 和 Krogh。

回答by Lucas

SELECT
  main_table.table_name            AS main_table_table_name,
  main_table.column_name           AS main_table_column_name,
  main_table.constraint_name       AS main_table_constraint_name,
  info_other_table.table_name      AS info_other_table_table_name,
  info_other_table.constraint_name AS info_other_table_constraint_name,
  info_other_table.column_name     AS info_other_table_column_name
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE main_table
  INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS other_table
    ON other_table.unique_constraint_name = main_table.constraint_name
  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE info_other_table
    ON info_other_table.constraint_name = other_table.constraint_name
WHERE main_table.table_name = 'MAIN_TABLE_NAME';

回答by Fadid

A simple request for recovered the names of foreign key as well as the names of the tables:

一个恢复外键名称和表名称的简单请求：

SELECT CONSTRAINT_NAME, table_name
FROM
   information_schema.table_constraints 
WHERE table_schema='public' and constraint_type='FOREIGN KEY'

回答by Eugene Yarmash

If you use the psqlclient, you can simply issue the \d table_namecommand to see which tables reference the given table. From the linked documentation page:

如果您使用psql客户端，您可以简单地发出\d table_name命令来查看哪些表引用了给定的表。从链接的文档页面：

\d[S+] [ pattern ]

For each relation (table, view, materialized view, index, sequence, or foreign table) or composite type matching the pattern, show all columns, their types, the tablespace (if not the default) and any special attributes such as NOT NULLor defaults. Associated indexes, constraints, rules, and triggers are also shown. For foreign tables, the associated foreign server is shown as well.

\d[S+] [ pattern ]

对于与 匹配的每个关系（表、视图、物化视图、索引、序列或外部表）或复合类型pattern，显示所有列、它们的类型、表空间（如果不是默认值）和任何特殊属性，例如NOT NULL或 默认值。还显示了关联的索引、约束、规则和触发器。对于外部表，还会显示关联的外部服务器。

回答by Jakobii

Table constraints can include multiple columns. The trick to getting this right is to join each column by their constraint ordinal positions. If you don't join correctly your script will blow up with duplicate rowswhenever a table has multiple columns in a unique constraint.

表约束可以包括多个列。做到这一点的技巧是通过约束顺序位置连接每一列。如果您没有正确连接， 只要表在唯一约束中具有多个列，您的脚本就会因重复行而炸毁。

Table Notes

表注

This may be helpful in understanding what these tables do.

这可能有助于理解这些表的作用。

information_schema.table_constraints

information_schema.table_constraints

• lists all table constraints
• explains constraint types
• does not include column information

• 列出所有表约束
• 解释约束类型
• 不包括列信息

information_schema.referential_constraints

information_schema.referential_constraints

• maps fk constraints to pk constraints.
  • constraint_name = fk constraint name
  • unique_constraint_name = pk constraint name
• does not include column information

• 将 fk 约束映射到 pk 约束。
  • 约束名称 = fk 约束名称
  • unique_constraint_name = pk 约束名称
• 不包括列信息

information_schema.key_column_usage

information_schema.key_column_usage

• list column level constraint info
• includes column ordinal positionsin constraints. this is important!

• 列出列级约束信息
• 包括约束中的列序号位置。这个很重要！

Query

询问

Lists all foreign key columns and their references.

列出所有外键列及其引用。

select
       -- unique reference info
       ref.table_catalog    as ref_database,
       ref.table_schema     as ref_schema,
       ref.table_name       as ref_table,
       ref.column_name      as ref_column,
       refd.constraint_type as ref_type, -- e.g. UNIQUE or PRIMARY KEY

       -- foreign key info
       fk.table_catalog as fk_database,
       fk.table_schema  as fk_schema,
       fk.table_name    as fk_table,
       fk.column_name   as fk_column,
       map.update_rule  as fk_on_update,
       map.delete_rule  as fk_on_delete

-- lists fk constraints and maps them to pk constraints
from information_schema.referential_constraints as map

-- join unique constraints (e.g. PKs constraints) to ref columns info
inner join information_schema.key_column_usage as ref
    on  ref.constraint_catalog = map.unique_constraint_catalog
    and ref.constraint_schema = map.unique_constraint_schema
    and ref.constraint_name = map.unique_constraint_name

-- optional: to include reference constraint type
left join information_schema.table_constraints as refd
    on  refd.constraint_catalog = ref.constraint_catalog
    and refd.constraint_schema = ref.constraint_schema
    and refd.constraint_name = ref.constraint_name

-- join fk columns to the correct ref columns using ordinal positions
inner join information_schema.key_column_usage as fk
    on  fk.constraint_catalog = map.constraint_catalog
    and fk.constraint_schema = map.constraint_schema
    and fk.constraint_name = map.constraint_name
    and fk.position_in_unique_constraint = ref.ordinal_position --IMPORTANT!

Example

例子

consider the relationship between these to tables.

考虑这些与表之间的关系。

create table foo (
    a int,
    b int,
    primary key (a,b)
);

create table bar (
    c int,
    d int,
    foreign key (c,d) references foo (b,a) -- i flipped a,b to make a point later.
);

If we check the information_schema.table_constraintstable we can see the names of the pk constraint and the fk constraint.

如果我们检查information_schema.table_constraints表格，我们可以看到 pk 约束和 fk 约束的名称。

select * from information_schema.table_constraints where table_name in ('foo','bar');

| constraint_name | table_name | constraint_type |
| --------------- | ---------- | --------------- |
| foo_pkey        | foo        | PRIMARY KEY     |
| bar_c_d_fkey    | bar        | FOREIGN KEY     |

And when we check the information_schema.referential_constraintstable we can see that our foreign key constraint depends on our primary keys unique constraint.

当我们检查information_schema.referential_constraints表时，我们可以看到我们的外键约束依赖于我们的主键唯一约束。

select * from information_schema.referential_constraints where constraint_name in ('bar_c_d_fkey');

| constraint_name | unique_constraint_name |
| --------------- | ---------------------- |
| bar_c_d_fkey    | foo_pkey               |

And finally, we check that information_schema.key_column_usagetable. we can see the the position_in_unique_constraintof the FK columns correctly map to the ordinal_positionof the PK columns.

最后，我们检查该information_schema.key_column_usage表。我们可以看到position_in_unique_constraintFK 列的 正确映射到ordinal_positionPK 列的 。

Notice that dcorrectly maps to aand ccorrectly maps to bper the table definitions above.

请注意，根据上面的表定义d正确映射a并c正确映射到b。

select * from information_schema.key_column_usage where table_name in ('foo','bar');

| constraint_name | table_name | column_name | ordinal_position | position_in_unique_constraint |
| --------------- | ---------- | ----------- | ---------------- | ----------------------------- |
| foo_pkey        | foo        | a           | 1                | null                          |
| foo_pkey        | foo        | b           | 2                | null                          |
| bar_c_d_fkey    | bar        | c           | 1                | 2                             |
| bar_c_d_fkey    | bar        | d           | 2                | 1                             |

Now all that's left is to join them together. The main query above is one way you could do so.

现在剩下的就是将他们联合起来。上面的主要查询是您可以这样做的一种方式。