问题描述
我有一个表名为 org_id
的字段的用户,它是表 organization
的外键,主键字段 organisation_id
。知道表名(用户)和字段名称( users.org_id
),是否有一个查询可以告诉我名称和字段 org_id
引用?
我找到了与此类似的地方提供查询来确定引用的表名称,但我也需要知道字段名称被引用:
$ 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约束可以引用多个列。这是聚合函数的原因。
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.
这篇关于查找外键约束的引用字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!