List all foreign keys constraints in PostgreSQL

Knowledge-base/Databases-queries/PostgreSQL queries

Mars 07th, 2023


To list all foreign key constraints in the PostgreSQL database. We will show you some ways below.

1. Using SQL Query

For those familiar with SQL, you can utilize this script to display all the foreign key constraints present in your database.

SELECT key_column_usage.constraint_schema, key_column_usage.constraint_name 
FROM information_schema.table_constraints 
JOIN information_schema.key_column_usage ON table_constraints.constraint_schema = key_column_usage.constraint_schema 
     AND table_constraints.constraint_name = key_column_usage.constraint_name 
JOIN information_schema.referential_constraints ON table_constraints.constraint_schema = referential_constraints.constraint_schema
     AND table_constraints.constraint_name = referential_constraints.constraint_name 
JOIN information_schema.table_constraints AS table_constraints1 ON referential_constraints.unique_constraint_schema = table_constraints1.constraint_schema 
     AND referential_constraints.unique_constraint_name = table_constraints1.constraint_name 
WHERE table_constraints.constraint_type = 'FOREIGN KEY' ORDER BY key_column_usage.constraint_name 

Sample result

The output of the SQL query:


Sample result list all foreign key constraints in postgresql database



3. List all foreign key constraints in PostgreSQL using ERBuilder

Alternatively,  you can list all foreign key constraints in PostgreSQL using ERBuilder. With ERBuilder you can also visualize your database structures from schema, tables, and all their details (columns, constraints, indexes, triggers, keys ..), relationships, and more.

Once you have reverse-engineered your PostgreSQL database using ERBuilder, you can easily locate all foreign key constraints in the database by accessing the treeview on the right side of the screen. The screenshot below illustrates this.

ERBuilder allows you to obtain comprehensive information about a constraint by simply double-clicking on its name. This includes details such as joins, parent table, and child table of the relationship, options, notes, as well as the SQL script of the constraint.

Start modeling your database with us today

Subscribe To Our Newsletter

Subscribe to our email newsletter today to receive updates of the latest news, tutorials and special offers!

Choose a product

Choose a product and get 15 Day fully-functional free trial. No credit card required