How to Detect Missing Foreign Keys in a Postgres Schema

A column called customer_id looks like a foreign key. It probably means to be one. But unless a REFERENCES constraint actually exists in the schema, Postgres will happily let you insert a customer_id that points at nothing. No error, no warning — just a row that silently stops being trustworthy.

This is one of the most common gaps in schemas that grew organically: someone added the column, the application layer enforced the relationship for a while, and the actual database constraint never got written. Months later nobody remembers which _id columns are real foreign keys and which are just named like one.

Why this matters beyond "it's best practice"

Without an enforced foreign key, three things stop being guaranteed:

• Referential integrity. Deleting a parent row doesn't cascade, restrict, or warn — child rows just become orphaned, silently.
• Query planner accuracy. Postgres uses foreign keys to improve join selectivity estimates in some query plans. Missing constraints can produce worse plans on joins that look like they should be cheap.
• Tooling that reads your schema. ORMs, ERD generators, and migration tools that infer relationships from information_schema constraints — not column names — simply won't see the relationship at all.

Finding declared foreign keys

Start with what Postgres actually knows about. This lists every foreign key constraint already declared in the current schema:

SELECT
  tc.table_name,
  kcu.column_name,
  ccu.table_name  AS references_table,
  ccu.column_name AS references_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
 AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage ccu
  ON tc.constraint_name = ccu.constraint_name
 AND tc.table_schema = ccu.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'public';

Finding columns that look like foreign keys but aren't

Now the actual gap-finding query: every column that matches a common foreign-key naming convention (_id suffix), where a table with the matching name exists, but no constraint from the first query covers it.

WITH declared_fks AS (
  SELECT kcu.table_name, kcu.column_name
  FROM information_schema.table_constraints tc
  JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
  WHERE tc.constraint_type = 'FOREIGN KEY'
    AND tc.table_schema = 'public'
)
SELECT
  c.table_name,
  c.column_name,
  regexp_replace(c.column_name, '_id$', '') AS implied_table_singular
FROM information_schema.columns c
WHERE c.table_schema = 'public'
  AND c.column_name LIKE '%\_id'
  AND c.column_name <> 'id'
  AND NOT EXISTS (
    SELECT 1 FROM declared_fks d
    WHERE d.table_name = c.table_name
      AND d.column_name = c.column_name
  )
ORDER BY c.table_name, c.column_name;

This won't be perfect — it's a naming heuristic, not a semantic one. You'll get false positives (a genuinely standalone external_id that references a third-party system, not a local table) and false negatives (a real relationship whose column doesn't follow the convention, like author instead of author_id). Treat the output as a review list, not a to-do list to blindly apply ALTER TABLE ... ADD CONSTRAINT against.

What to actually do with the results

For each flagged column, check three things before adding a constraint: whether a matching table actually exists to reference, whether existing data already violates the relationship (orphaned rows that predate the fix), and whether you want ON DELETE CASCADE, RESTRICT, or SET NULL semantics — this is also the moment to decide that, since it's easy to defer and then never revisit.

-- check for existing orphans before adding the constraint
SELECT o.*
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id IS NOT NULL AND c.id IS NULL;

-- then add the constraint
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer_id
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  ON DELETE RESTRICT;

Doing this without hand-writing the query every time

The anti-join above is the kind of thing you end up re-running against every schema you inspect, tweaking the naming pattern each time. ERD Factory runs this same convention-based check as part of its structural analysis — paste in a CREATE TABLE dump and every implied-but-missing foreign key shows up as a flagged gap on the diagram, alongside missing indexes and orphan tables, without writing SQL against information_schema by hand. See AI Schema Analysis for how the structural checks combine with an optional AI pass over naming and normalization.