Skip to content

23001 — restrict_violation

PostgreSQL SQLSTATE 23001: restrict_violation from an immediate foreign-key RESTRICT action.

23001 — restrict_violation

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.

Field Value
SQLSTATE 23001
Condition restrict_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_RESTRICT_VIOLATION
Aliases —

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.

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

Sources