23503 — foreign_key_violation

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.

Meaning and trigger paths

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

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.

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;

On PostgreSQL 18.6, the natural error was:

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

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

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

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

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

Sources

The structured evidence is recorded in the public evidence 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
• src.ri-triggers.18.6 — ri_triggers.c
• doc.ddl.18.6 — Foreign Keys
• doc.protocol.18 — Error and Notice Message Fields
• Runtime: 23503-fk-manual-final-20260909 (latest and pg10), with structured observations in the public evidence JSON