# 23503 — foreign_key_violation

> PostgreSQL reports SQLSTATE 23503 for a child foreign-key violation and some ordinary parent actions. Identify the action, relation, and constraint, then repair the data or transaction boundary.
---

## At a glance {#at-a-glance}

`23503` is PostgreSQL's `foreign_key_violation` condition in Class 23, `integrity_constraint_violation`. A child insert or update without a matching parent key, or an ordinary parent key action covered by the foreign-key rules, can produce it. In PostgreSQL 18.6, the `RESTRICT` parent branch instead uses `23001`, so classify the action and complete diagnostic before assigning this SQLSTATE.

The most useful diagnostic fields are the SQLSTATE (`C`), primary message (`M`), detail (`D`), and the schema, table, and constraint fields when the server supplies them. In psycopg these are available through `exc.sqlstate` and `exc.diag`. Preserve the complete diagnostic before retrying, because the detail names the missing or still-referenced key when PostgreSQL can show it.

For an immediate foreign-key constraint, the error is raised by the violating statement. A deferred constraint can let the statement finish and raise `23503` at `COMMIT` instead. In autocommit, the connection can execute the next command after an immediate failed statement. In an explicit transaction, an immediate failure leaves the transaction `INERROR`; issue `ROLLBACK` or roll back to a savepoint before sending an unrelated command. A valid parent row and a fresh child write are the repair proof in the representative case.

The case `fk_insert_missing_parent` uses a real `parents`/`children` foreign key. It passed on PostgreSQL 18.6 and the isolated PostgreSQL 10.21 target. The run IDs and per-case assertions are retained in the [public evidence JSON](../data/evidence/23503.json).

<!-- BEGIN SQLSTATE FACTS: generated by scripts/generate.py; do not edit -->

| Field | Value |
| --- | --- |
| SQLSTATE | `23503` |
| Condition | `foreign_key_violation` |
| Status | `active` |
| Known present by | `7.4` |
| Locked snapshots | `9.0.23, 9.1.24, 9.2.24, 9.3.25, 9.4.26, 9.5.25, 9.6.24, 10.23, 11.22, 12.22, 13.23, 14.24, 15.19, 16.15, 17.11, 18.6, 19beta3` |
| Macros | `ERRCODE_FOREIGN_KEY_VIOLATION` |
| Aliases | `—` |

<!-- source facts: data/errcodes/23503.json -->
<!-- END SQLSTATE FACTS -->

## Meaning and trigger paths {#meaning}

A foreign key on the child table points to a primary key or a suitable unique key on the parent table. PostgreSQL checks that relationship on child `INSERT` and key-changing `UPDATE`, and checks the reverse relationship when a parent key is updated or deleted. The exact timing depends on whether the constraint is immediate or deferred.

The column matching rule also matters. With the default `MATCH SIMPLE`, a referencing row with any null key column does not have to match a parent. `MATCH FULL` permits an all-null key or an all-non-null key that matches, but rejects a mixture of null and non-null key columns. The representative case uses one `NOT NULL` column, so it deliberately does not demonstrate either null rule.

For a parent `DELETE` or key-changing `UPDATE`, `ON DELETE/UPDATE NO ACTION` can wait until the constraint's check point when the constraint is deferrable, whereas `RESTRICT` checks immediately and cannot be deferred. In the fixed PostgreSQL 18.6 source, the `RESTRICT` branch uses `23001` (`restrict_violation`) with a dedicated message rather than the `23503` shown by this page's child-write case. The action is part of the relationship contract, so a parent operation cannot be assigned a SQLSTATE by analogy with a missing-parent insert.

The ordinary child-row path in `ri_triggers.c` reports the condition with the template `insert or update on table "%s" violates foreign key constraint "%s"`. Its conditional detail is `Key (%s)=(%s) is not present in table "%s".`; the server also attaches the child table and constraint through the protocol's table and constraint fields. The ordinary parent-key branch can use a different `23503` primary template, while the fixed-version `RESTRICT` branch uses `23001`. This page's runtime evidence covers only the missing-parent child write, not parent actions.

`23503` identifies the relationship failure, not the application fix. A missing parent can mean an ordering bug, an incorrect identifier, an uncommitted parent in another transaction, or a deliberate delete that needs a cascade or another business rule. Inspect the operation and constraint definition before choosing a repair.

## Messages and diagnostics {#messages}

The representative operation creates a parent and child table, inserts no parent with key `99`, and then inserts the child row. The executable excerpt is the same trigger and recovery sequence used by the runner; names are schema-qualified by the disposable harness.

<!-- BEGIN SQLSTATE SNIPPET: fk_insert_missing_parent -->
```sql
CREATE TABLE parents(id integer PRIMARY KEY);
CREATE TABLE children(
    id integer PRIMARY KEY,
    parent_id integer NOT NULL,
    CONSTRAINT children_parent_fk FOREIGN KEY (parent_id) REFERENCES parents(id)
);
BEGIN;
INSERT INTO children VALUES (1, 99);
-- The server reports 23503 and the transaction is now INERROR.
ROLLBACK;
BEGIN;
INSERT INTO parents VALUES (99);
INSERT INTO children VALUES (1, 99);
COMMIT;
```
<!-- END SQLSTATE SNIPPET -->

On PostgreSQL 18.6, the natural error was:

```text
SQLSTATE: 23503
severity: ERROR
message_primary: insert or update on table "children" violates foreign key constraint "children_parent_fk"
message_detail: Key (parent_id)=(99) is not present in table "parents".
schema_name: c23503_fk_insert_missing_parent
table_name: children
constraint_name: children_parent_fk
source: ri_triggers.c / ri_ReportViolation / line 2783
```

PostgreSQL 10.21 produced the same primary and detail text; its corresponding source line was 3266. The detail is conditional on permission to describe the key and may be absent. Do not parse a localized English message as the protocol contract: branch on `23503`, then use the structured fields and the operation context.

## Diagnosis {#diagnosis}

First capture the failing statement, SQLSTATE, severity, primary message, detail, hint, server version, and transaction status. For an explicit transaction, record the status immediately after the error (`INERROR`) and after recovery (`IDLE` or `INTRANS`). A later `25P02` means the client sent a command while the transaction was already failed; it is a follow-on state, not a replacement for `23503`.

Inspect the named constraint and its referenced relation with `pg_constraint` and `pg_get_constraintdef()`. Compare the attempted key with the parent table under the relevant isolation level. If the parent row is being created by another transaction, determine whether the write order and commit ordering are intentional; a fixed delay does not prove that a parent became visible.

For a parent delete or key update, inspect the referencing rows and the action declared by `ON DELETE` or `ON UPDATE`. For a deferred foreign key, the violating statement may succeed and `COMMIT` may be the command that raises `23503`. Preserve that timing in logs and in retry logic.

## Response and repair {#response}

Choose the repair that matches the relationship:

- Create or select the intended parent before retrying the child write, as in the representative case.
- Correct the child identifier when it is stale or malformed; do not disable the constraint to hide a data error.
- For a parent removal, apply the declared cascade, set-null, or restrict policy only when it matches the business rule. Otherwise update or archive the referencing rows first.
- If another transaction owns the parent write, use a transaction design that establishes the intended ordering and isolation. Re-read after a rollback rather than replaying a stale child request blindly.

After a failed explicit transaction, `ROLLBACK` discards its pending work and returns the connection to `IDLE`. A savepoint can retain earlier work when the child operation is optional. A successful repair must include a real parent lookup, a committed child row, and a post-commit read; merely receiving a new connection or issuing `ROLLBACK` is not proof that the relationship is fixed.

## Versions and boundaries {#versions}

The catalogue has a definition-presence observation for `23503` at PostgreSQL 7.4 and through the locked 8.4.22 pre-9.0 definitions, then in every listed formal snapshot through PostgreSQL 18.6 and the PostgreSQL 19 Beta 3 preview. This is a definition-only presence boundary, not an exact implementation introduction or runtime-use claim. No condition definition change is recorded in the scanned range.

The representative case passed on PostgreSQL 18.6 and 10.21. Source line numbers differ between those releases, while SQLSTATE, constraint identity, and the foreign-key diagnostic shape remain the compatibility boundary used here. Deferred timing, permissions, cascading actions, and concurrent parent creation are separate dimensions and are not implied by this one immediate-constraint run.

## Related {#related}

[`23505` — `unique_violation`](../23505/) covers duplicate values in a unique invariant. [`23502` — `not_null_violation`](../23502/) covers a required column receiving `NULL`. [`40001` — `serialization_failure`](../40001/) and [`40P01` — `deadlock_detected`](../40p01/) describe concurrency outcomes that can surround a relationship repair. [`25P02` — `in_failed_sql_transaction`](../25p02/) is the follow-on transaction state after an unhandled error.

## Sources {#sources}

The structured evidence is recorded in the [public evidence JSON](../data/evidence/23503.json). Source records are fixed to PostgreSQL commit `724edf9bde9d356724ad384a2e196edc3c9f80f7`; runtime records retain both target run IDs and their structured observations.

- `src.errcodes.18.6` — [`errcodes.txt`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt#L235-L241)
- `src.ri-triggers.18.6` — [`ri_triggers.c`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/ri_triggers.c#L2761-L2809)
- `doc.ddl.18.6` — [Foreign Keys](https://www.postgresql.org/docs/18/ddl-constraints.html#DDL-CONSTRAINTS-FK)
- `doc.protocol.18` — [Error and Notice Message Fields](https://www.postgresql.org/docs/18/protocol-error-fields.html)
- Runtime: `23503-fk-manual-final-20260909` (latest and pg10), with structured observations in the [public evidence JSON](../data/evidence/23503.json)