PostgreSQL 23503 foreign_key_violation: broken table reference

Note: Follow the documented order. An error code identifies the affected system, but it does not prove by itself which part has failed.

1. 1

   Inspect Constraint, Table, and Detail fields to determine the failure direction

   No special tools

   Who:

   Your developer or DBA reads the structured ErrorResponse fields from the failed statement.

   How:

   Capture fields `n` (Constraint name), `s` (Schema name), `t` (Table name), and `D` (Detail) using `\set VERBOSITY verbose` in `psql` or your database driver's error object.

   Result that matters:

   Field `n` identifies the exact foreign key constraint and `D` states whether a key is not present in the referenced table or is still referenced from a child table.

   If `D` reports that the key is not present in the referenced table during an `INSERT` or `UPDATE`, that confirms a child write referenced a missing or uncommitted parent row.

   If `D` reports that the key is still referenced from another table during a `DELETE` or `UPDATE`, that confirms existing dependent rows blocked removal of the parent key.

   Evidence to keep:

   Keep the verbatim `n`, `s`, `t`, and redacted `D` fields with the statement type (`INSERT`, `UPDATE`, or `DELETE`).

   Evidence: PostgreSQL 18 Appendix A: Error Codes · PostgreSQL error and notice fields · PostgreSQL bug reporting guidelines

2. 2

   Check pg_constraint for referenced table, referential actions, and deferrability

   Tools required

   Who:

   Your DBA or developer runs a read-only catalog query on `pg_constraint`.

   How:

   Execute `SELECT conname, conrelid::regclass AS child_table, confrelid::regclass AS parent_table, confupdtype, confdeltype, confmatchtype, condeferrable, condeferred, pg_get_constraintdef(oid, true) FROM pg_constraint WHERE conname = '<constraint_name>' AND contype = 'f';`.

   Result that matters:

   `pg_constraint` records `confrelid` (referenced table), `confupdtype` and `confdeltype` (`a` = no action, `r` = restrict, `c` = cascade, `n` = set null, `d` = set default), `confmatchtype` (`f` = full, `s` = simple), and deferrability.

   If `confdeltype` is `a` or `r`, that confirms PostgreSQL will reject any `DELETE` on `parent_table` while matching rows remain in `child_table`.

   If `condeferrable` is `true` and `confdeltype` is `a` (NO ACTION), that indicates the check can be deferred until commit with `SET CONSTRAINTS`, whereas `r` (RESTRICT) never allows deferral.

   Evidence to keep:

   Save the `pg_constraint` query row including `pg_get_constraintdef` output.

   Run read-only catalog `SELECT` queries only; do not drop foreign keys or delete child rows before verifying business retention rules.

   Evidence: PostgreSQL constraints · PostgreSQL pg_constraint catalog · PostgreSQL 18 system information functions

3. 3

   Verify parent-before-child ordering or deferred constraint mode inside the transaction

   No special tools

   Who:

   Your developer traces the order of SQL statements inside the transaction that raised 23503.

   How:

   Check whether the child `INSERT` ran before the parent `INSERT`, whether the parent transaction rolled back earlier, or whether cyclic foreign keys require `SET CONSTRAINTS <name> DEFERRED`.

   Result that matters:

   `REFERENCES` (foreign key) constraints are checked at the end of each statement by default (`NOT DEFERRABLE` or `INITIALLY IMMEDIATE`), and can only be postponed to transaction commit when declared `DEFERRABLE`.

   If the child row is inserted before its parent on a `NOT DEFERRABLE` foreign key, that confirms statement-order timing triggered 23503.

   If the parent row was supposedly created in a separate session that rolled back or has not committed yet, that isolates the problem to cross-session visibility or aborted work.

   Evidence to keep:

   Record the exact sequence of statements and transaction boundaries leading to the 23503 error.

   Evidence: PostgreSQL 18 SET CONSTRAINTS · PostgreSQL 18 CREATE TABLE

A code is meaningful only inside the right product and version context.

Appendix A of the PostgreSQL documentation assigns SQLSTATE 23503 to `foreign_key_violation` in Class 23 (Integrity Constraint Violation). A foreign key constraint requires values in the referencing column(s) to match values in a primary key or unique constraint of the referenced table. Violation 23503 happens in two directions: inserting or updating a child row whose key has no matching parent row, or deleting/updating a referenced parent row while dependent child rows still exist under `NO ACTION` (the default) or `RESTRICT`. By itself, code 23503 does not prove which direction failed or whether transaction ordering, a rolled-back parent insert, or a `MATCH FULL` rule caused the mismatch.

• Highsource verified

  Never switch a foreign key to `ON DELETE CASCADE` or mark it `NOT ENFORCED` without reviewing what the tables represent; Section 5.5.5 warns that cascading a deletion across independent objects (such as deleting order history when a product is removed) can silently destroy critical records.

  Evidence: PostgreSQL constraints · PostgreSQL 18 CREATE TABLE

• Lowsource verified

  Do not attempt to post raw `Detail` messages containing customer identifiers or keys to external issue trackers without redacting sensitive values first.

  Evidence: PostgreSQL error and notice fields · PostgreSQL bug reporting guidelines

1. 1

   PostgreSQL 18 Appendix A: Error Codes

   PostgreSQL Global Development Group · 2026 · official_doc

2. 2

   PostgreSQL constraints

   PostgreSQL Global Development Group · 2026 · official_doc