PostgreSQL: SQL script to get a list of all tables that have a particular column as foreign key
Solution 1
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 = 'id' AND
u.table_catalog = 'db_name' AND
u.table_schema = 'public' AND
u.table_name = 'table_a'
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.
The query lists all tables that have a foreign key constraint against the column 'a' in table 'd'
Solution 2
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.
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.
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.
The only thing you need plug in is the 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);
Solution 3
This query requires only the referenced table name and 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.
Solution 4
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';
Solution 5
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';
Comments
-
Gaurav Dadhania almost 2 years
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_schema
but I have no idea how to start querying it. -
Anomie about 13 yearsI must be doing something wrong, when I try your query I just get the name of table 'd' repeated over and over.
-
RichardTheKiwi about 13 years@Anomie are you using the updated query? I changed it - yes I had it the wrong way around
-
Anomie about 13 years@cyberkiwi: Yes, same result with both queries.
-
RichardTheKiwi about 13 years@Anomie Try removing all the conditions that test _catalog and _schema.
-
Anomie about 13 yearsI see the problem.
R
should beINFORMATION_SCHEMA.KEY_COLUMN_USAGE
, notINFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
. -
RichardTheKiwi about 13 years@Anomie - ah. ok. I knew there were small differences between different implementations of INFORMATION_SCHEMA - was testing on SQL Server
-
Tony K. over 10 yearsOlder schemas will not work with this, as PostgreSQL used to not name constraints in a unique way. See my PostgreSQL-specific answer.
-
alphanumeric character over 10 yearsI think this is the proper answer - this is a more accurate answer than the one selected.
-
Jmoney38 almost 9 yearsAny way to include the source column(s)? I'm tinkering with the query now, will post if I find a solution.
-
Jmoney38 almost 9 yearsI submitted an edit to include the new selection - For some reason, S.E. doesn't like me submitting the SQL in a comment.
-
dml over 8 years@Tony K. Is there any way to know (by querying the catalog tables) if a specific row is being referenced in other tables, not just the entire column? Thanks.
-
Farrukh Najmi over 7 yearsThis did not work for me. The solution just below worked for me. Thanks.
-
ZPiDER about 7 yearsusing 'left outer join' instead of the shown 'inner join' will speed this query up dramatically (1500ms vs 170ms), you will then need to filter with 'and r.constraint_name is not null'
-
piotrekkr over 6 yearsIf you have tables with same name but in different schemas you need to filter in where condition:
where c.confrelid = ( select oid from pg_class where relname = 'TARGET_TABLE_NAME' AND relnamespace = (select oid from pg_namespace where nspname = 'SCHEMA_NAME'))
-
Tiago Stapenhorst Martins over 3 yearsHow can I define a specific schema name to the filter? That's an important point because two tables with the same name and same unique columns can exist in the same database.