Postgres error 23503: foreign key violation
insert or update on table "x" violates foreign key constraint "x_y_fkey"
A row points at a parent that does not exist, or a delete would leave children pointing at nothing.
Common causes
- Inserting a child before its parent, or with a wrong parent id.
- Deleting a parent row that still has children, when the foreign key is ON DELETE NO ACTION or RESTRICT.
- Loading tables in the wrong order during a migration or restore.
How to fix it
- Insert parents first, or insert both in one transaction with the constraint DEFERRABLE INITIALLY DEFERRED.
- Decide what deleting a parent should do: ON DELETE CASCADE, SET NULL, or keep it restricted and delete children explicitly.
- During bulk loads, add foreign keys after the data.
alter table comments drop constraint comments_post_id_fkey,
add constraint comments_post_id_fkey foreign key (post_id) references posts (id) on delete cascade;Through a REST API
PostgREST, the REST layer behind supabase-js and Scribase, answers HTTP 409 for this error and returns the SQLSTATE in the code field of the JSON error body. PostgREST answers 409 Conflict. The details field names the missing key.
Questions
What does Postgres error 23503 mean?
23503 is foreign_key_violation in class 23 (Integrity Constraint Violation). A row points at a parent that does not exist, or a delete would leave children pointing at nothing.
How do I fix 23503?
Insert parents first, or insert both in one transaction with the constraint DEFERRABLE INITIALLY DEFERRED. Decide what deleting a parent should do: ON DELETE CASCADE, SET NULL, or keep it restricted and delete children explicitly. During bulk loads, add foreign keys after the data.
What HTTP status does a REST API return for 23503?
PostgREST, the REST layer behind supabase-js, answers 409.