PostgreSQL error: violates foreign key constraint

SQLSTATE 23503 (foreign_key_violation) · Updated July 9, 2026

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(42) is not present in table "customers".

-- direzione opposta:
ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (id)=(42) is still referenced from table "orders".

A foreign key failed in one of its two directions: a child row points at a parent that is not there, or a parent row is being removed while children still reference it. The wording tells you which — "is not present in" versus "is still referenced from" — and the DETAIL names the exact key value.

What this error means

A foreign key is a promise checked at both ends:

• "insert or update on child": you wrote a child row whose referencing value has no matching parent row. The write was rejected.
• "update or delete on parent": removing (or re-keying) the parent would orphan existing children. By default (NO ACTION) PostgreSQL refuses; the constraint can instead declare ON DELETE CASCADE, SET NULL, etc. — a schema decision made at constraint creation, not query time.

Common causes

• Wrong or stale ID from the application — the parent was deleted, or the ID came from another environment.
• Ordering across transactions: the parent INSERT ran in a different transaction that has not committed yet; the child insert cannot see it.
• Bulk loads in the wrong order (children before parents), or partial loads.
• Deleting a parent without a strategy for its children — the second message.

How to diagnose it

The DETAIL gives you the constraint name, the key value, and both table names. From there:

-- Il vincolo, per esteso:
\d orders

-- Orfani già presenti (per capire l'ampiezza del problema nei load):
SELECT o.*
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

How to fix it

• Child direction: fix the value or the ordering — create the parent first, in the same transaction or an earlier committed one.
• Bulk loads: load parents before children; if the data genuinely arrives interleaved, make the constraint deferrable and check it at commit:

  ALTER TABLE orders
    ALTER CONSTRAINT orders_customer_id_fkey DEFERRABLE INITIALLY DEFERRED;

• Parent direction: delete the children first, or declare the intent in the schema (ON DELETE CASCADE or SET NULL). Treat CASCADE with respect: one DELETE can silently fan out to a lot of rows — make it a conscious design choice, not a quick fix.
• Many systems sidestep the parent direction entirely with soft deletes (a deleted_at column) for entities other data hangs off.