PostgreSQL — error 23503 foreign key constraint violation

Diagnose PostgreSQL 23503 by identifying which referencing row lacks a matching parent and ordering the writes correctly.

beginnerdebuggingpostgressql

Symptoms

ERROR: insert or update on table violates foreign key constraint
The referenced key is not present in the parent table
Deleting a parent row fails because children still reference it
Concurrent transactions deadlock on foreign key checks

Possible causes

The referencing row points at a parent key that does not exist
Insert order writes children before parents
A parent was deleted while children still reference it and no cascade is defined
Bulk load bypassed the parent insert

Diagnosis steps

Read the constraint name and columns

psql -c "\d+ child_table"

Interpretation

The constraint definition shows which columns reference the parent, telling you which key is missing.

Check for the parent row

psql -c "select * from parent where id = <value>"

Interpretation

If the parent is absent, the child insert cannot satisfy the constraint, so the parent must be created first.

Inspect delete rules

psql -c "select confdeltype from pg_constraint where conname = 'child_parent_fk'"

Interpretation

The delete action shows whether children are cascaded, set null or blocked, explaining delete failures.

Root cause

The operation tried to create or keep a child row whose foreign key has no matching parent row, violating referential integrity enforced by the database.

Fix

Insert or restore the parent before the child, order batch operations so parents precede children, and choose an appropriate ON DELETE action such as cascade or set null when children should follow the parent.

Verify the fix

Repeat the write and confirm it succeeds, and confirm the referencing row now points at an existing parent.

Symptom walkthrough

An insert or update fails with a foreign key constraint violation, meaning the child row references a parent key that does not exist. Deletes on the parent side can fail the same way when children still reference it and no cascade is defined. Batch loads that write children before parents are a frequent trigger, as are concurrent transactions that delete parents mid-load.

Fix in practice

Ensure the parent exists before writing the child, and order batch operations so parents precede children. Choose an ON DELETE action that matches the intended semantics, such as cascade to remove children or set null to detach them, rather than leaving the default restrict and hitting failures later.

Verification

Repeat the write and confirm it succeeds, then confirm the child row references an existing parent. A satisfied constraint confirms the ordering and rules are correct.

Diagnostic commands

Check the behavior with commands like these:

psql -c "\d+ child_table"
psql -c "select * from parent where id = <value>"
psql -c "select confdeltype from pg_constraint where conname = 'child_parent_fk'"