Find referenced field(s) of foreign key constraint

Question

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.

Looks like my code. You should include a link to the question you got that from. Also please add your version of Postgres — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 22, 2013 at 16:17

Erwin Brandstetter · Accepted Answer · 2013-08-22 16:09:25Z

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.

Collectives™ on Stack Overflow

Find referenced field(s) of foreign key constraint

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related