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?

Ftisiot · Accepted Answer · 2021-06-16 10:03:42Z

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;

answered Jun 16, 2021 at 10:03

Ftisiot

1,8981 gold badge9 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Triaji Setiawan Over a year ago

Works like charm ,thankyou so much!:)) it's really helpful for me :))

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related