23001

At a glance

23001 is the specific RESTRICT violation condition. In the selected PostgreSQL 18.6 case, deleting a referenced parent row raised it with the child constraint and key in the diagnostic. PostgreSQL 10 reported the same tested operation through 23503, so version and server diagnostics must be recorded. A separate PostgreSQL 17.11 comparison also returned 23503 for this RESTRICT operation; it is not 23001 coverage.

Meaning

An ON DELETE RESTRICT or ON UPDATE RESTRICT action checks the referencing row immediately and refuses the parent change. This differs from NO ACTION: a DEFERRABLE NO ACTION constraint can postpone its check until the relevant commit, while RESTRICT is not deferrable. The source builds a dynamic primary/detail message from the parent table, foreign-key constraint, child table, and key visibility.

Diagnosis

Record the exact SQLSTATE and constraint_name, table_name, schema_name, and detail. Check the child rows before changing the parent. In an explicit transaction the failed delete leaves the connection INERROR; ROLLBACK is required before the repair transaction can delete the child and then the parent.

Response

Choose the action deliberately: remove or reassign dependent rows, change the parent operation, or redesign the FK action after reviewing its integrity meaning. Do not turn RESTRICT into a deferred NO ACTION merely to make a migration pass. The selected repair deletes the dependent row in a new BEGIN/COMMIT sequence and then deletes the parent.

Observed diagnostics

18.6 (Homebrew) / latest:SQLSTATE 23001; primary update or delete on table "parents" violates RESTRICT setting of foreign key constraint "children_parent_id_fkey" on table "children"; DETAIL Key (id)=(1) is referenced from table "children".; after_error INERROR; after_rollback IDLE. 10.21 (Debian 10.21-1.pgdg90+1) / pg10:SQLSTATE 23503; primary update or delete on table "parents" violates foreign key constraint "children_parent_id_fkey" on table "children"; DETAIL Key (id)=(1) is still referenced from table "children".; after_error INERROR; after_rollback IDLE. 17.11 (pg17) / boundary:SQLSTATE 23503; primary update or delete on table "parents" violates foreign key constraint "children_parent_id_fkey" on table "children"; DETAIL Key (id)=(1) is still referenced from table "children".; after_error INERROR; after_rollback IDLE; final counts [0, 0]. This boundary case is not 23001 coverage.

Representative case

The runner reads these statements from verify/cases/23001/snippets.json (SHA-256 6086c1ce982afa5438bbcd29b86ec4cd0cbe0c76fa6152c1eb4a330a7706d5c8) and qualifies the temporary table names; the complete setup, assertions, and cleanup are in the case export.

-- create_parent
CREATE TABLE parents(id integer PRIMARY KEY);
-- create_child
CREATE TABLE children(id integer PRIMARY KEY, parent_id integer NOT NULL REFERENCES parents(id) ON DELETE RESTRICT);
-- seed_parent
INSERT INTO parents VALUES (1);
-- seed_child
INSERT INTO children VALUES (10, 1);
-- begin
BEGIN;
-- trigger
DELETE FROM parents WHERE id = 1;
-- rollback
ROLLBACK;
-- repair_begin
BEGIN;
-- repair_child
DELETE FROM children WHERE id = 10;
-- repair_parent
DELETE FROM parents WHERE id = 1;
-- commit
COMMIT;
-- verify
SELECT (SELECT count(*) FROM parents), (SELECT count(*) FROM children);

The SQLSTATE, diagnostic, transaction-state, and repair assertions for this excerpt are produced from the shared registry; structured evidence · case export.

Authored evidence IDs: identity, restrict-path, no-action-boundary, runtime, runtime.pg17-boundary. Selected base runtime records: runtime.23001-batch1-latest2-20260909.latest, runtime.23001-batch1-pg10b-20260909.pg10. Separate boundary record: runtime.23001-boundary-pg17-final-20260909.pg17.

Versions

The locked catalogue observes the condition by 7.4 and in all listed formal snapshots. The selected base cases observe 23001 on 18.6 and 23503 on 10.21 for the same RESTRICT delete. A separate PG17.11 boundary comparison also returned 23503, with the same INERROR to IDLE recovery; it is retained as a comparison and does not establish a universal result for every PostgreSQL 11–17 minor release.

Related

Compare 23503 foreign-key violation, 23505 unique violation, and 23514 CHECK violation.

Sources

• src.errcodes.18.6 (SHA-256 6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba)
• src.calls.REL_18_6 (SHA-256 9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf)
• src.ri_triggers.18.6 (SHA-256 a3fed49fbda88fe0fe5bcf35eb019fced28596ae4046233f2acb2eb9f90ab1da)
• doc.ddl-constraints.18.6 (SHA-256 ce1919d9236f2e71672660e1a347146472e966e4d19b77fde5ae345dd1db6ec7) · official documentation

Source evidence

Evidence belongs to the frozen source and runtime versions listed here. It is not a runtime verification of the selected manual version.

• src/backend/utils/errcodes.txt · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• raw/calls/REL_18_6.jsonl · ·
• src/backend/utils/adt/ri_triggers.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• doc/src/sgml/ddl.sgml · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7

Message templates

update or delete on table "%s" violates RESTRICT setting of foreign key constraint "%s" on table "%s"

Key (%s)=(%s) is referenced from table "%s".

Reproduction & repair cases

Recorded runtime evidence

{
  "detail": "Key (id)=(1) is referenced from table \"children\".",
  "primary": "update or delete on table \"parents\" violates RESTRICT setting of foreign key constraint \"children_parent_id_fkey\" on table \"children\"",
  "sqlstate": "23001",
  "after_error": "INERROR",
  "final_counts": [
    0,
    0
  ],
  "after_rollback": "IDLE"
}

{
  "detail": "Key (id)=(1) is still referenced from table \"children\".",
  "primary": "update or delete on table \"parents\" violates foreign key constraint \"children_parent_id_fkey\" on table \"children\"",
  "sqlstate": "23503",
  "after_error": "INERROR",
  "final_counts": [
    0,
    0
  ],
  "after_rollback": "IDLE"
}

{
  "detail": "Key (id)=(1) is still referenced from table \"children\".",
  "primary": "update or delete on table \"parents\" violates foreign key constraint \"children_parent_id_fkey\" on table \"children\"",
  "severity": "ERROR",
  "sqlstate": "23503",
  "after_error": "INERROR",
  "final_counts": [
    0,
    0
  ],
  "after_rollback": "IDLE",
  "constraint_name": "children_parent_id_fkey",
  "expected_sqlstate": "23503",
  "natural_23001_observed": false
}