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

Symptoms

An UPDATE or DELETE on a parent row would leave child rows pointing at a key that no longer exists. With the default NO ACTION/RESTRICT rule PostgreSQL blocks it and raises SQLSTATE 23503 (foreign_key_violation).

• Deleting a parent row that still has referencing children.
• Updating a parent key that children reference.
• The DETAIL names the child table and key still in use.

What the server log shows

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".

Why PostgreSQL raises this — what the manual says

Section 5.5.5 Foreign Keys:

“But the foreign-key constraint is still required to be satisfied, so this operation will usually result in an error.”

Referential integrity requires every child key to match a live parent key. Removing or changing the parent key while children reference it would orphan them, so the default referential action rejects the change with 23503.

Common causes

• Deleting a parent without first removing or reassigning its children.
• Updating a primary/unique key value that children depend on.
• Wrong delete order in a script across related tables.

How to fix it

1. Delete or reassign the child rows first, then the parent.
2. If cascading is intended, define the FK with ON DELETE CASCADE / ON UPDATE CASCADE.
3. Use ON DELETE SET NULL/SET DEFAULT when orphaned children should be detached, not removed.

Diagnostic query

-- Find children still referencing the parent key
SELECT * FROM orders WHERE customer_id = 42;

Related & next steps

• SQLSTATE 23503 — Foreign Key Violation (full reference)
• Message: insert or update violates foreign key constraint
• Glossary: Foreign key

Reference: PostgreSQL 18 Section 5.5 “Constraints”.