How do i get table name and column name of the table that has foreign key referencing to master table?

Question

so if i have a table

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)
);

So i wanted to check all the tables that is referencing to customers. Something like this :

TABLE_NAME|COLUMN_NAME|TABLE_REFERENCES|COLUMN_REFERENCES
contacts|customer_id|customers|customer_id

So it would basically tells me that in the table contacts with field customer_id, it is a foreign key reference to table customers with field customer_id. Is there a way to do this?

Answer 1

the solution you're looking for is detailed in this blog post

Basically you'll need to parse the information_schema tables table_constraints, key_column_usage, referential_constraints, table_constraints.

The following query should be a good starting point

select kcu.table_schema || '.' ||kcu.table_name as foreign_table,
       rel_tco.table_schema || '.' || rel_tco.table_name as primary_table,
       kcupk.column_name as pk_column,
       kcu.column_name as fk_column,
       kcu.constraint_name
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu
          on tco.constraint_schema = kcu.constraint_schema
          and tco.constraint_name = kcu.constraint_name
join information_schema.referential_constraints rco
          on tco.constraint_schema = rco.constraint_schema
          and tco.constraint_name = rco.constraint_name
join information_schema.table_constraints rel_tco
          on rco.unique_constraint_schema = rel_tco.constraint_schema
          and rco.unique_constraint_name = rel_tco.constraint_name
join information_schema.key_column_usage kcupk
          on rel_tco.constraint_schema = kcupk.constraint_schema
          and rel_tco.constraint_name = kcupk.constraint_name
where tco.constraint_type = 'FOREIGN KEY'
order by kcu.table_schema,
         kcu.table_name;