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:
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!
