List Foreign Key Constraints in PostgreSQL

calendar Apr 5, 2026 / · 4 min read · postgresql administration schema database pg_constraint information_schema  ·
Share on: linkedin copy

List Foreign Key Constraints in PostgreSQL

Foreign key constraints enforce referential integrity between tables. They guarantee that a value in one table's column always has a matching row in another table. Knowing which foreign keys exist — and how they are defined — is essential before dropping tables, renaming columns, or loading large datasets.

This SQL query lists every foreign key in the public schema, showing the table that owns the constraint, the constraint name, and the full constraint definition including which columns it spans and which table it references.

SQL Script

1SELECT conrelid::regclass AS table_name,
2       conname AS foreign_key,
3       pg_get_constraintdef(oid)
4FROM   pg_constraint
5WHERE  contype = 'f'
6AND    connamespace = 'public'::regnamespace
7ORDER  BY conrelid::regclass::text, contype DESC;

Notes: Works on PostgreSQL 9.1 and later. Queries pg_constraint directly, which is faster and more complete than querying information_schema. Results are limited to the public schema — change 'public'::regnamespace to audit a different schema.

Code Breakdown

pg_constraint

The pg_constraint system catalog stores every constraint defined in the database: primary keys, unique constraints, check constraints, exclusion constraints, and foreign keys. Each row is one constraint.

contype = 'f'

Filters to foreign key constraints only. The constraint type codes are: p (primary key), u (unique), c (check), x (exclusion), and f (foreign key).

conrelid::regclass AS table_name

conrelid is the OID of the table that owns the constraint. Casting it to regclass converts the OID to a human-readable schema-qualified table name automatically.

conname AS foreign_key

The constraint name as assigned at creation time, or the name PostgreSQL generated automatically. Auto-generated names follow the pattern tablename_columnname_fkey.

pg_get_constraintdef(oid)

A built-in PostgreSQL function that returns the full DDL definition of a constraint as a text string. For a foreign key, this includes the column list, the referenced table, the referenced column list, and any ON DELETE or ON UPDATE actions defined.

connamespace = 'public'::regnamespace

Limits results to constraints in the public schema. Replace 'public' with any schema name to audit that schema instead.

ORDER BY conrelid::regclass::text, contype DESC

Sorts by table name alphabetically so that all foreign keys on the same table appear together.

Key Points

  • Each row is one foreign key constraint. A table with multiple foreign keys produces multiple rows.
  • The pg_get_constraintdef output shows the exact SQL definition, including referenced columns and any cascade or restrict actions.
  • The query covers only the public schema by default. Remove the namespace filter entirely to audit all schemas.
  • An empty result set means the public schema has no foreign key constraints defined.

Insights and Explanations

Foreign key constraints affect several common DBA tasks:

  • Dropping a table: PostgreSQL will refuse to drop a table that is referenced by a foreign key in another table unless you use CASCADE or drop the referencing constraint first. This query shows you which tables reference the one you want to drop.
  • Bulk data loads: Foreign key checks run on every inserted row. Disabling foreign key checks during a large load can dramatically reduce load time, but you must verify integrity afterward.
  • Column renames: Renaming a column that is part of a foreign key requires understanding the dependencies. Check this query before renaming columns.

The pg_get_constraintdef output will include ON DELETE CASCADE, ON DELETE SET NULL, ON UPDATE RESTRICT, or similar clauses if they were defined. A cascade action will silently delete rows in child tables when a parent row is removed.

If referential integrity was not enforced historically, there may be orphaned rows in child tables. After identifying your foreign keys, check for orphans:

1-- Find rows in child_table with no matching parent
2SELECT c.*
3FROM child_table c
4LEFT JOIN parent_table p ON p.id = c.parent_id
5WHERE p.id IS NULL;

Additional Considerations

  • Permissions: Any user can query pg_constraint. No superuser access is required to list constraints.
  • Performance: The query reads only system catalog tables and is instantaneous regardless of data volume.
  • All schemas: To list foreign keys across all user schemas, remove the connamespace filter and add connamespace to the SELECT list to identify which schema each constraint belongs to.
  • information_schema alternative: information_schema.referential_constraints provides similar information in a portable SQL-standard format, but pg_constraint with pg_get_constraintdef returns the complete definition in a single readable line.

References

Posts in this series