是否有一种方法使用SQL列出给定表的所有外键?我知道表名/模式,我可以把它插入。


当前回答

这个查询也适用于组合键:

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

其他回答

PSQL就是这样做的,如果你用:

psql -E

它将准确地显示执行了哪些查询。在查找外键的情况下,它是:

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

在这种情况下,16485是我正在寻找的表的oid -你可以通过将你的表名转换为regclass来获得它:

WHERE r.conrelid = 'mytable'::regclass

如果表名不是唯一的(或者是搜索路径中的第一个),则对表名进行模式限定:

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

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

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

选择的答案不为我工作,所以张贴我的sql工作。

select 
    con.conname as constraint_name,
    src_schema.nspname as source_schema,
    source.relname as source_table,
    source_col.attname as source_column,
    trg_schema.nspname as target_schema,
    target.relname as target_table,
    target_col.attname as target_column
from 
    pg_constraint con
inner join 
    pg_class source on source.oid = con.conrelid
inner join
    pg_attribute source_col on source_col.attrelid = con.conrelid and source_col.attnum = con.conkey[1] and source_col.attisdropped = false
inner join
    pg_namespace src_schema on src_schema.oid = source.relnamespace
inner join 
    pg_class target on target.oid = con.confrelid
inner join
    pg_attribute target_col on target_col.attrelid = con.confrelid and target_col.attnum = con.confkey[1] and source_col.attisdropped = false    
inner join
    pg_namespace trg_schema on trg_schema.oid = target.relnamespace 
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

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