查找外键约束的引用字段 [英] Find referenced field(s) of foreign key constraint

问题描述

我有一个表名为 org_id 的字段的用户，它是表 organization 的外键，主键字段 organisation_id 。知道表名（用户）和字段名称（ users.org_id ），是否有一个查询可以告诉我名称和字段 org_id 引用？

我找到了 Stackoverflow后与此类似的地方提供查询来确定引用的表名称，但我也需要知道字段名称被引用：
$ b $ pre $ SELECT c.confrelid :: regclass :: text AS referenced_table
，c.conname AS fk_name
，pg_get_constraintdef（c.oid）AS fk_definition
FROM pg_attribute a
JOIN pg_constraint c ON（c.conrelid，c.conkey [1]）=（a.attrelid，a.attnum）
WHERE a.attrelid ='Schema。Users'：regclass - 表名
AND a.attname ='org_id' - 列名
AND c.contype =' f'
ORDER BY conrelid :: regclass :: text ，contype DESC;因此，上面的查询将返回表的名称（ organization


$> code>），fk名称和fk定义。有没有办法也得到被引用的字段的名称？我知道我可能可能执行另一个查询来确定给定的表的pk的名称，但我想避免执行此多个查询。

解决方案

这个查询为外键约束添加引用列：
$ b $ pre code> SELECT c.confrelid :: regclass :: text AS referenced_table
，string_agg（f.attname，'，'）AS referenced_columns
，c.conname AS fk_name
，pg_get_constraintdef（c.oid）AS fk_definition
FROM pg_attribute a
JOIN pg_constraint c ON（c.conrelid，c.conkey [1]）=（a.attrelid，a.attnum）
JOIN pg_attribute f ON f.attrelid = c.confrelid
AND f.attnum = ANY（confkey）
WHERE a.attrelid ='Schema。Users'：regclass - 表名
AND a.attname ='org_id' - 列名称
和c.contype ='f'
GROUP BY c.confrelid，c.conname，c.oid;


一个fk约束可以引用多个列。这是聚合函数的原因 string_agg（） 。


I have a table Users with a field called org_id which is a foreign key to a table organisation, with primary key field organisation_id. Knowing the table name (users) and the field name (users.org_id), is there a query that can tell me the name and field that org_id references?

I've found a Stackoverflow post similar to this where a query was provided to determine the referenced table name, but I also need to know the field name that is referenced:

SELECT c.confrelid::regclass::text AS referenced_table
  ,c.conname AS fk_name
  ,pg_get_constraintdef(c.oid) AS fk_definition
  FROM   pg_attribute a 
  JOIN   pg_constraint c ON (c.conrelid, c.conkey[1]) = (a.attrelid, a.attnum)
  WHERE  a.attrelid = '"Schema"."Users"'::regclass   -- table name
  AND    a.attname  = 'org_id'                           -- column name  
  AND    c.contype  = 'f'
  ORDER  BY conrelid::regclass::text, contype DESC;

So the above query would return the name of the table (organisation), the fk name and fk definition. Is there a way to also get the name of the field that is referenced? I know I could probably perform another query to determine the name of pk given a table but I would like to avoid performing multiple queries for this.

解决方案

This query adds the referenced column(s) for the foreign key constraint:

SELECT c.confrelid::regclass::text AS referenced_table
      ,string_agg(f.attname, ', ') AS referenced_columns
      ,c.conname AS fk_name
      ,pg_get_constraintdef(c.oid) AS fk_definition
FROM   pg_attribute  a 
JOIN   pg_constraint c ON (c.conrelid, c.conkey[1]) = (a.attrelid, a.attnum)
JOIN   pg_attribute  f ON f.attrelid = c.confrelid
                      AND f.attnum = ANY (confkey)
WHERE  a.attrelid = '"Schema"."Users"'::regclass   -- table name
AND    a.attname  = 'org_id'                       -- column name  
AND    c.contype  = 'f'
GROUP  BY c.confrelid, c.conname, c.oid;

A fk constraint can reference multiple columns. That's the reason for the aggregate function string_agg() in the query.

这篇关于查找外键约束的引用字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！