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.
What lands in your log
ERROR: insert or update on table "order_items" violates foreign key constraint "order_items_order_id_fkey"
In 10 seconds
- What triggers it
- Create a parent table and a child table with a FOREIGN KEY referencing the parent.
- 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.
Fix
What to do right now
Application-level steps for 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.
-- Parent first, then child, tables already exist from setup.
INSERT INTO shop.orders (id) VALUES (1);
INSERT INTO shop.order_items (id, order_id) VALUES (1, 1);
SELECT * FROM shop.order_items;For this error
See this error live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Insert or update violates foreign key constraint is explained by a stored integrity rule. Match the constraint name from PostgreSQL DETAIL to the catalog definition before changing data or code.
Foreign-key definition and actions
Find the constraint named in DETAIL, including parent/child tables and ON UPDATE/DELETE actions.
SELECT conname, conrelid::regclass AS child_table,
confrelid::regclass AS parent_table,
pg_get_constraintdef(oid) AS definition,
convalidated
FROM pg_constraint
WHERE contype = 'f'
ORDER BY conrelid::regclass::text, conname
LIMIT 100;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.
CREATE SCHEMA IF NOT EXISTS shop;
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.
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.
- A second operational test: exact SQL, raw output, measured result, and engineer notes
- Fix that looks safe but silently disables the check: exact SQL, output, and verdict
- A manual-grounded production interpretation of the lab result
Card required. Cancel before day 7 and you are not charged.
Runbook to fix this
The runbook for this incident
Full step-by-step fixes for the condition behind this error: the diagnosis, the exact SQL, and output captured in the lab.
Connected
Everything this error touches
Every page this SQLSTATE connects to: the concept that explains it, the runbooks that fix it, the parameters you tune to prevent it, and the sibling errors it travels with. All real cross-references. Jump straight in, or open the full interactive map.
Understand the concept
Fix it — runbooks
Related errors
Verification
- Last verified
- 2026-07-15 (Docker lab, PostgreSQL 16.14)
- Verification scope
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed
Went further?
Pro unlocks the second lab proof
Free page stops the bleeding. Pro adds the operational test, SQLSTATE audit, and deeper evidence, same error, more certainty.