PostgreSQL 23503 — Foreign Key Violation

PostgreSQL Error 23503: foreign_key_violation

Error 23503 occurs when an operation would break referential integrity defined by a foreign key constraint. Either you are trying to insert a row referencing a non-existent parent, or delete a parent row that still has child references.

Error Formats

Insert violation:

ERROR: insert or update on table "orders" violates foreign key constraint "orders_user_id_fkey"
DETAIL: Key (user_id)=(999) is not present in table "users".

Delete violation:

ERROR: update or delete on table "users" violates foreign key constraint "orders_user_id_fkey"
DETAIL: Key (id)=(1) is still referenced from table "orders".

Solutions

1. Correct insertion order: Always insert parent rows before child rows:

-- First: create the user
INSERT INTO users (id, name) VALUES (1, 'John');

-- Then: create their order
INSERT INTO orders (user_id, total) VALUES (1, 99.99);

2. CASCADE options:

-- Automatically delete child rows when parent is deleted
ALTER TABLE orders
ADD CONSTRAINT orders_user_id_fkey
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE;

-- Set to NULL when parent is deleted
ON DELETE SET NULL

-- Prevent deletion (default)
ON DELETE RESTRICT

-- Set to default value
ON DELETE SET DEFAULT

3. Deferred constraints (for bulk operations):

-- Defer constraint checking to end of transaction
SET CONSTRAINTS orders_user_id_fkey DEFERRED;

-- Or define as deferrable when creating
ALTER TABLE orders
ADD CONSTRAINT orders_user_id_fkey
FOREIGN KEY (user_id) REFERENCES users(id)
DEFERRABLE INITIALLY DEFERRED;

ORM Handling

# SQLAlchemy
from sqlalchemy.exc import IntegrityError

try:
    session.add(order)
    session.commit()
except IntegrityError as e:
    if '23503' in str(e.orig):
        # Foreign key violation
        session.rollback()

Best Practices

1. Design foreign keys to match your data lifecycle
2. Use ON DELETE CASCADE for true parent-child relationships
3. Use ON DELETE SET NULL for optional references
4. Use DEFERRABLE constraints for complex bulk imports
5. Test deletion cascades in staging before production