Incident brief
Insert or update violates foreign key constraint
A row referenced a value in another table through a FOREIGN KEY, but that value doesn't exist there. PostgreSQL rejects the row to keep the two tables consistent.
In 10 seconds
- What
- Insert or update violates foreign key constraint
- What triggers it
- Create a parent table and a child table with a FOREIGN KEY referencing the parent.
- The fix
- Insert the parent row first, then the child row that references it.
- Proof
- Reproduced on PostgreSQL 16.14 → The INSERT referencing a non-existent parent id (999) was rejected with SQLSTATE 23503 and the row was never stored.
The fix
What to do right now
The immediate, application-level response to this error.
- Insert the parent row first, then the child row that references it.
- Double-check the referenced id actually exists before inserting.
- Decide an ON DELETE policy (CASCADE, RESTRICT, SET NULL) up front so parent deletes behave the way you expect.
CREATE TABLE shop.orders (id integer primary key);
CREATE TABLE shop.order_items (
id integer primary key,
order_id integer REFERENCES shop.orders(id)
);
-- insert the parent first
INSERT INTO shop.orders (id) VALUES (1);
-- now the child insert has a matching parent row
INSERT INTO shop.order_items (id, order_id) VALUES (1, 1);Diagnose
See it live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Standard triage — not specific to this error
These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.
What is running right now
Active backends, how long each has been running, and what it is waiting on.
SELECT pid,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;Who is blocking whom
Turn raw blocking PIDs into the actual queries on both sides of the wait.
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;Locks that are still waiting
Every lock a backend has requested but not yet been granted.
SELECT l.pid,
l.locktype,
l.mode,
l.granted,
COALESCE(c.relname, l.transactionid::text) AS object
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE NOT l.granted
ORDER BY l.pid;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual — not paraphrased.
PostgreSQL 16 Documentation — §5.4.5 Foreign Keys
NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything.Read the full section on postgresql.org →
The insert that violates the foreign key
PostgreSQL checked the foreign key before storing the row and found no matching id=999 in shop.orders. The DETAIL line names the exact missing key.Checking the child table afterward
The child table is empty. The rejected insert left no partial or orphaned row behind.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.
- 1Create a parent table and a child table with a FOREIGN KEY referencing the parent.
- 2Try to insert a child row whose foreign key value has no matching parent row.
- 3PostgreSQL rejects the row with SQLSTATE 23503.
One client: create parent/child tables linked by a FOREIGN KEY, try to insert a child row pointing at a parent id that doesn't exist.
DROP TABLE IF EXISTS shop.order_items, shop.orders;
CREATE TABLE shop.orders (
id integer primary key
);
CREATE TABLE shop.order_items (
id integer primary key,
order_id integer REFERENCES shop.orders(id)
);INSERT INTO shop.order_items (id, order_id) VALUES (1, 999);SELECT * FROM shop.order_items;What PostgreSQL actually returned
DROP TABLE
CREATE TABLE
CREATE TABLEERROR: insert or update on table "order_items" violates foreign key constraint "order_items_order_id_fkey"
DETAIL: Key (order_id)=(999) is not present in table "orders". id | order_id
----+----------
(0 rows)ON DELETE CASCADE was proven, not just described: the same parent delete that fails by default was re-run against a child table declared with ON DELETE CASCADE, and the parent row and its child row both went away together.
First, confirm the default behavior (NO ACTION) blocks deleting a still-referenced parent. Then declare the same relationship with ON DELETE CASCADE and delete the parent again.
Without this
Before: the default foreign key (NO ACTION) blocks deleting a still-referenced parent row.
With this, tested
After: the same delete, but the child's foreign key was declared ON DELETE CASCADE — the parent delete now succeeds and removes the child row too.
What Pro unlocks here
- The exact prevention SQL — copy-paste ready
- Raw psql output captured from the Docker lab
- A senior-DBA action list to take it further
- Live monitoring queries to catch it in production
- The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence
Related & next steps
Follow the thread
Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.
Verification
- Last verified
- 2026-07-15 (Docker lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed