Explain Codes LogoExplain Codes Logo

Postgresql: SQL script to get a list of all tables that have a particular column as foreign key

sql
foreign-keys
database-queries
sql-scripts
Nikita BarsukovbyNikita Barsukov·Jan 11, 2025
TLDR

To quickly identify tables that have your target column as a foreign key, utilize this to-the-point script:

SELECT tbl.relname AS "Table", col.attname AS "Column" FROM pg_constraint con JOIN pg_class tbl ON con.conrelid = tbl.oid JOIN pg_attribute col ON col.attrelid = tbl.oid AND col.attnum = ANY(con.conkey) WHERE con.confrelid = (SELECT oid FROM pg_class WHERE relname = 'TargetTable') AND con.contype = 'f';

Remember, replace 'TargetTable' with the name of your specific table.

Unveiling information_schema secrets

The information_schema can prove to be quite the beacon when shining a light on foreign key relationships. By deploying these specific views—table_constraints, referential_constraints, and key_column_usage, and explicitly detailing them to our needs, we engage in accurate, effective querying:

-- Elegant, isn't it? SELECT kcu.table_schema, kcu.table_name, kcu.column_name, rc.update_rule, rc.delete_rule FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.referential_constraints AS rc ON tc.constraint_name = rc.constraint_name WHERE kcu.referenced_column_name = 'TargetColumn' AND tc.constraint_type = 'FOREIGN KEY';

Simply replace 'TargetColumn' with your desired column name.

Focusing the lens

When dealing with vast databases that span multiple schemas, it becomes necessary to adjust our focus. The accuracy of our results can be improved by applying more precise constraints. Let's look at the enhanced sequence:

-- Who needs a magnifying glass when you have SQL? SELECT conname as "Constraint", (SELECT relname FROM pg_class WHERE oid = con.conrelid) as "Table", attname as "Column" FROM pg_constraint con INNER JOIN pg_attribute att ON att.attrelid = con.conrelid AND att.attnum = ANY(con.conkey) WHERE con.confrelid IN (SELECT oid FROM pg_class WHERE relname = 'TargetTable' AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = 'TargetSchema')) AND con.contype = 'f';

Just replace 'TargetTable' and 'TargetSchema' to suit your database.

Constraints: Their unique story

Unique constraints in one table are often the targets for foreign keys in another. Identifying these relationships can prevent unforeseen data model alterations that could violate dependencies.

Merging our tools for precision

When locating dependencies across schemas or excluding particular namespaces, merging our SQL tools proves beneficial. Here's how to combine these elements:

-- All the SQL tools in one! Like Voltron, but for database queries. SELECT nsp.nspname as "Schema", tbl.relname as "Table", att.attname as "Column" FROM pg_attribute att JOIN pg_class tbl ON att.attrelid = tbl.oid JOIN pg_namespace nsp ON tbl.relnamespace = nsp.oid JOIN pg_constraint con ON att.attrelid = con.conrelid AND att.attnum = ANY(con.conkey) WHERE con.contype = 'f' AND EXISTS ( SELECT 1 FROM pg_class ref_tbl JOIN pg_attribute ref_att ON ref_att.attrelid = ref_tbl.oid WHERE ref_tbl.relname = 'TargetTable' AND ref_att.attname = 'TargetColumn' AND ref_tbl.relnamespace = nsp.oid );

This master query searches for all foreign keys across all schemas that point to your targeted column and table, giving you a complete understanding of your database structure.