errorPostgreSQLError 23503

PostgreSQL Error 23503: Foreign Key Violation

Error Message
ERROR:  insert or update on table "orders" violates foreign key constraint "fk_orders_customer"
DETAIL:  Key (customer_id)=(999) is not present in table "customers".

What is PostgreSQL Error 23503?

PostgreSQL SQLSTATE 23503 occurs when an operation violates referential integrity. This happens when: 1) You insert/update a child row pointing to a non-existent parent, or 2) You delete/update a parent row that has active child records.

Common Causes

  • 1

    Inserting a child record before creating its referenced parent record

  • 2

    Deleting a parent row while child tables still hold active references to it

  • 3

    Attempting to modify the referenced primary key of a parent row

  • 4

    A migration script loading dependent tables in the wrong order

Step-by-Step Solutions

1

Ensure the parent row is created first before executing the child insert

2

Check if parent exists: SELECT 1 FROM parent_table WHERE id = <key>;

3

For deletes: delete dependent child records first, or define ON DELETE CASCADE

4

Temporarily defer constraints in a transaction: SET CONSTRAINTS ALL DEFERRED; (if configured as DEFERRABLE)

Prevention Tips

  • Use database transactions to group parent and child insertion queries atomically

  • Use CASCADE options for parent table foreign keys: ON DELETE CASCADE / ON UPDATE CASCADE

  • Define foreign keys as DEFERRABLE INITIALLY DEFERRED for complex multi-row transaction batches

Frequently Asked Questions

How do I temporarily disable foreign key checks in PostgreSQL?
PostgreSQL does not have a simple global switch like MySQL's 'foreign_key_checks'. Instead, you can temporarily disable trigger execution on a specific table: ALTER TABLE table_name DISABLE TRIGGER ALL; (remember to enable: ALTER TABLE table_name ENABLE TRIGGER ALL;). This must be run as a superuser/owner and locked inside a single transaction to prevent concurrent validation leaks.
What are deferred constraints in PostgreSQL?
A deferred constraint checks referential integrity at the end of a transaction (COMMIT) instead of after each individual statement. To use this, declare the constraint as: CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent(id) DEFERRABLE INITIALLY DEFERRED;. This is very helpful when inserting circular or bulk interdependent records.

Related Errors

POSTGRESQL 23505POSTGRESQL 23502POSTGRESQL 23514

Still Stuck?

Ask our AI SQL Assistant or the community — get answers in seconds.

Ask AI Assistant Test in Playground