This query returns all tables which are referencing by FK to specified table.
select distinct fk_tco.table_schema || '.' || fk_tco.table_name as foreign_table, '>-' as rel, pk_tco.table_schema || '.' || pk_tco.table_name as primary_table from information_schema.referential_constraints rco join information_schema.table_constraints fk_tco on rco.constraint_name = fk_tco.constraint_name and rco.constraint_schema = fk_tco.table_schema join information_schema.table_constraints pk_tco on rco.unique_constraint_name = pk_tco.constraint_name and rco.unique_constraint_schema = pk_tco.table_schema where pk_tco.table_name = 'table_name' -- enter table name here --and pk_tco.table_schema = 'schema_name' order by foreign_table;
- foreign_table - foreign table schema name followed by table name - table we look for
- ref - relationship symbol implicating FK and direction
- primary_table - referenced schema name followed by table name - table provided as a parameter
- One row represents one referencing table
- Scope of rows: tables which are referencing to table provided as parametr
- Ordered by referencing table schema with table name
Tables which are referencing with FK public.staff table in pagila database.