foreign_key_violation insert or update on table “…” violates foreign key constraint “…” — 23503

Symptoms

You tried to insert or update a row whose foreign-key column points at a parent row that does not exist. The child value has no matching key in the referenced table.

• The write is rejected and rolled back with SQLSTATE 23503.
• The DETAIL line names the missing key and the referenced table.
• Often caused by loading children before parents, or a stale FK value.

What the server log shows

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(999) is not present in table "customers".
STATEMENT:  INSERT INTO orders (customer_id) VALUES (999);

Why PostgreSQL raises this — what the manual says

Section 5.5.5 Foreign Keys:

“A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table.”

On insert/update of the child, PostgreSQL checks that the referenced key exists in the parent table. If not, 23503 is raised. The reverse direction (deleting/updating a parent that still has children) raises the sibling message update or delete on table … violates foreign key constraint.

Common causes

• Inserting a child before its parent row exists.
• A wrong or stale foreign-key value in the child row.
• Loading data in the wrong order (children before parents).
• The parent row was deleted out from under the child.

How to fix it

1. Insert the parent row first, then the child.
2. Verify the referenced key exists: SELECT 1 FROM customers WHERE id = 999;.
3. Load tables parent-first, or defer the constraint inside a transaction if it is DEFERRABLE.
4. Correct the foreign-key value in the child row.

Diagnostic query

-- Find child rows with no matching parent
SELECT o.*
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

These orphan rows are the ones the constraint will reject.

Related & next steps

• SQLSTATE 23503 — Foreign Key Violation (full reference)
• Message: update or delete violates foreign key (parent side)
• Message: no unique constraint matching given keys

Reference: PostgreSQL 18 Section 5.4.5 “Foreign Keys”.