PostgreSQL 23503: foreign key constraint violation
A row references a parent key that doesn't exist, or you deleted or changed a parent row that child rows still reference.
- SQLSTATE
23503- Name
foreign_key_violation- Category
- Integrity Constraint Violation
What it means
A foreign key says every value in the child column must exist in the referenced (parent) column. PostgreSQL raises 23503 in two directions: when you insert or update a child row whose key has no parent (is not present in table), and when you delete or update a parent row that child rows still point to (is still referenced from table).
The DETAIL line gives the key value and the other table, so you know which rows to look at.
Common causes
- Inserting child rows before their parent rows, for example loading
ordersbeforecustomers. - A typo or stale id in the child row.
- Deleting a parent row that still has children, without
ON DELETE CASCADEorSET NULLon the foreign key. - Updating a parent's primary key while children reference the old value.
- Two transactions: one inserts a child for a parent another just deleted.
How to fix it
-
Find the missing parent or the remaining children.
SELECT * FROM customers WHERE id = 42; SELECT * FROM orders WHERE customer_id = 42;
-
Insert parents first, or load both tables in one transaction with the constraint deferred (it must be declared
DEFERRABLE).ALTER TABLE orders ALTER CONSTRAINT orders_customer_id_fkey DEFERRABLE INITIALLY IMMEDIATE; BEGIN; SET CONSTRAINTS orders_customer_id_fkey DEFERRED; INSERT INTO orders (id, customer_id) VALUES (1, 42); INSERT INTO customers (id, name) VALUES (42, 'Ada'); COMMIT; -- checked here
-
When deleting a parent, delete or reassign its children first, or change the foreign key's delete rule if that is the behavior you want.
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey, ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE; -
Find orphans before adding a foreign key to existing data.
SELECT o.* FROM orders o LEFT JOIN customers c ON c.id = o.customer_id WHERE o.customer_id IS NOT NULL AND c.id IS NULL;
Reproduce it safely
CREATE TEMP TABLE p (id int PRIMARY KEY); CREATE TEMP TABLE c (p_id int REFERENCES p (id)); INSERT INTO c VALUES (1); -- ERROR: ... violates foreign key constraint "c_p_id_fkey"
Related errors
23505duplicate key violates unique constraint23502null value violates not-null constraint42830invalid foreign key2BP01dependent objects still exist547SQL Server: Statement conflicted with a constraint1452MySQL: Foreign key fails: cannot add child row1451MySQL: Foreign key fails: cannot delete parent rowORA-02291Oracle: integrity constraint violated - no parent keyORA-02292Oracle: integrity constraint violated - child record foundSQLITE_CONSTRAINT_FOREIGNKEYSQLite: FOREIGN KEY constraint failed
More integrity constraint violation errors
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.