When you want to get all foreign key references of a table’s id. There are 2 options you can employ when you are using postgres.
1. This script one will only work if you have been assigning unique names to foreign key in your database:
SELECT tc.table_schema, tc.constraint_name, tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name,ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = ‘FOREIGN KEY’ AND ccu.table_name=
the_name_of_the_table
2. This second script will work for all cases. you just have to supply the table in context.
select (select nspname from pg_namespace where oid=f.relnamespace) as foreign_ns,
f.relname as foreign_table,
(select a.attname from pg_attribute a where a.attrelid = f.oid and a.attnum = o.confkey[1] and a.attisdropped = false) as foreign_colname,
(select nspname from pg_namespace where oid=m.relnamespace) as target_ns,
m.relname as target_table,
(select a.attname from pg_attribute a where a.attrelid = m.oid and a.attnum = o.conkey[1] and a.attisdropped = false) as target_colname, o.conname as target_constraint_name from pg_constraint o left join pg_class c on c.oid = o.conrelid
left join pg_class f on f.oid = o.confrelid left join pg_class m on m.oid = o.conrelid
where o.contype = ‘f’ and o.conrelid in (select oid from pg_class c where c.relkind = ‘r’) and f.relname =
the_name_of_the_table
I do not have the luxury of time to explain in details but lemme know if you have any questions or addition. Will be happy to discuss this.
