How to Fix PostgreSQL Error: Violates Foreign Key Constraint

ERROR: insert or update on table "<child>" violates foreign key constraint "<name>" (SQLSTATE 23503, condition foreign_key_violation) is raised when an INSERT, UPDATE, or DELETE would leave the database in a state where a child row references a parent row that does not exist. PostgreSQL also raises 23503 on the parent side when a DELETE or UPDATE removes a row that has dependent children and the constraint's referential action is NO ACTION or RESTRICT. The transaction is aborted; nothing is written.

What This Error Means

A foreign key constraint pairs a column (or column set) in a child table with a UNIQUE or PRIMARY KEY column set in a parent table. PostgreSQL evaluates the constraint at the end of each statement by default (or at commit if the constraint is DEFERRABLE INITIALLY DEFERRED). If the row being inserted or updated has a non-NULL key with no matching parent, the statement fails. If the row being deleted or updated in the parent has children and the action is NO ACTION/RESTRICT, the statement also fails.

The error message includes the constraint name and a DETAIL: line such as Key (customer_id)=(123) is not present in table "customers". or Key (id)=(7) is still referenced from table "orders". The detail distinguishes the two directions of the violation, which matters because the fix is different on each side.

Common Causes

Child row inserted before the parent exists - confirm with SELECT 1 FROM <parent> WHERE <pk> = <value>.
Bulk load in the wrong order: children loaded before parents - check the loader's file order.
Parent UPDATE changes a referenced key without ON UPDATE CASCADE - inspect the constraint with \d <child>.
Parent DELETE on a row with children and no cascade - the DETAIL: line names the dependent table.
Application logic deletes a parent in a separate transaction while children still exist.
Restore of a single table broke the relationship - run an orphan check before re-enabling writes.
Type mismatch between key columns (e.g. bigint parent vs integer child) causing implicit truncation - check information_schema.referential_constraints.

How to Fix `foreign_key_violation`

Read the DETAIL: line in the error. It points at the offending key value and the table on the other side. Then check whether that row exists:
```
SELECT * FROM customers WHERE id = 123;
```

Insert the parent before the child, in the same transaction. This avoids race conditions in concurrent loads:

BEGIN;
INSERT INTO customers (id, name) VALUES (123, 'Acme');
INSERT INTO orders (customer_id, total) VALUES (123, 99.99);
COMMIT;

Choose the right referential action for parent deletes. Decide once per relationship and encode it in the constraint:
```
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
         ON UPDATE CASCADE;
```
Options: NO ACTION (default, deferrable check), RESTRICT (immediate, not deferrable), CASCADE, SET NULL, SET DEFAULT.
Defer the constraint when inserts must reference each other within a transaction. Use INITIALLY DEFERRED so PostgreSQL only checks at commit:
```
ALTER TABLE orders
    ALTER CONSTRAINT orders_customer_id_fkey
    DEFERRABLE INITIALLY DEFERRED;
```
Find orphan rows before adding or re-validating a constraint:
```
SELECT o.id, o.customer_id
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;
```
Either backfill the parents or delete/null out the children before continuing.
Use NOT VALID for cheap constraint creation on large tables. PostgreSQL adds the constraint without scanning existing rows, then validate when convenient:
```
ALTER TABLE orders
    ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES customers(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;
```
VALIDATE CONSTRAINT takes only a SHARE UPDATE EXCLUSIVE lock - safer for production than re-adding a fully validated constraint.
Load bulk data in dependency order. Topologically sort tables so parents come first. For very large loads, drop the FK, load, run an orphan check, then re-add with NOT VALID + VALIDATE.

Catch Foreign Key Violations Before They Hit Production

Pulse provides AI-powered observability for PostgreSQL alongside Elasticsearch, ClickHouse, and Kafka. For SQLSTATE 23503 foreign_key_violation errors, Pulse:

Tracks 23503 rates per constraint name in pg_stat_database and server logs, so a spike on a specific *_fkey after a release is immediately attributable to a broken write path
Correlates the violation with recent ORM migrations, schema changes (added/dropped ON DELETE actions), and application releases so root cause is visible without log-grepping
Flags configuration drift across replicas and environments - missing child-side indexes on FK columns, mismatched referential actions, unindexed parent deletes turning into full child scans - before it surfaces as an application error

A non-zero baseline of 23503 errors per constraint is a signal that the write path or referential action is wrong, not that users are sending bad input.

Connect your Postgres cluster to surface issues like this proactively.

Preventive Measures

Always create the foreign key with explicit ON DELETE and ON UPDATE actions. The default NO ACTION rarely matches application intent.
Index the foreign key column on the child side. PostgreSQL does not create this index automatically, and the lack of it makes parent deletes do full child scans.
Run orphan-row checks in CI against staging data after every schema change.
Use pg_dump --disable-triggers only as a last resort for restores; it skips FK checks and can leave the database silently inconsistent.

Frequently Asked Questions

Q: What is SQLSTATE 23503 in PostgreSQL?
A: 23503 is the foreign_key_violation SQLSTATE. PostgreSQL raises it when a statement would create or leave a row whose foreign key has no matching parent, or when a parent delete/update would orphan children under NO ACTION or RESTRICT.

Q: What is the difference between ON DELETE CASCADE and ON DELETE SET NULL?
A: CASCADE deletes the child rows when the parent is deleted. SET NULL keeps the child rows and sets the referencing column(s) to NULL. Use CASCADE when the child has no independent meaning without the parent; use SET NULL when it does.

Q: Why does PostgreSQL not create an index on foreign key columns automatically?
A: PostgreSQL only auto-indexes primary keys and unique constraints. Foreign key columns need a manual CREATE INDEX. Without it, parent deletes and updates that reference the FK become slow because PostgreSQL must scan the child table to enforce the constraint.

Q: Can I temporarily disable a foreign key constraint?
A: PostgreSQL has no "disable" syntax for FKs. The options are: drop and re-add with NOT VALID, mark the constraint DEFERRABLE INITIALLY DEFERRED so it is checked at commit, or (superuser only) ALTER TABLE ... DISABLE TRIGGER ALL, which is unsafe and bypasses validation.

Q: How do I find every table that references a given parent table?
A: Query pg_constraint:

SELECT conrelid::regclass AS child_table, conname
FROM pg_constraint
WHERE contype = 'f' AND confrelid = 'customers'::regclass;

Q: Does a NULL foreign key value violate the constraint?
A: No. A NULL in any column of the referencing key by default makes the constraint pass (MATCH SIMPLE behaviour). Use MATCH FULL if you want a partially-NULL key to be rejected.

Q: How can I detect PostgreSQL foreign key violations before they break the application?
A: Treat the 23503 rate per constraint as an SLI and alert when a specific *_fkey deviates from its baseline. Pulse tracks 23503 rates by constraint name, correlates spikes with recent migrations and ORM changes, and flags missing child-side FK indexes that turn parent deletes into full table scans.

PostgreSQL Check Constraint Violated: the sister error for predicate-based constraints.
PostgreSQL Could Not Serialize Access: serialization failures that can mask FK ordering bugs.
PostgreSQL CREATE TABLE: syntax for declaring foreign keys at table creation.
Common PostgreSQL Errors: index of SQLSTATE codes and conditions.
PostgreSQL Relation Does Not Exist: related schema-resolution failure.
PostgreSQL CTE: CTE-based patterns for atomic parent/child operations.