SQLSTATE 23001Severity mediumLab verified

Incident brief

RESTRICT foreign-key violation

Deleting or updating a parent row referenced by a child fails when the foreign key uses RESTRICT (or NO ACTION with a still-present child); the referencing rows must go first.

Reproduced on PostgreSQL 18.4Verified 2026-07-24 (isolated lab, PostgreSQL 18.4)Reviewed by Verified against PostgreSQL 18.4 in an isolated lab environment

In 10 seconds

What: RESTRICT foreign-key violation
What triggers it: Create a parent/child foreign key with RESTRICT and insert a referenced parent row plus a child that references it.
The fix: Delete or reassign the referencing child rows before deleting the parent.
Proof: Reproduced on PostgreSQL 18.4 → A DELETE on the parent reproduces SQLSTATE 23001; RESTRICT blocks it and the DETAIL names the referencing child key.

The fix

What to do right now

The immediate, application-level response to this error.

Delete or reassign the referencing child rows before deleting the parent.
Or, if cascade semantics are intended, define the foreign key ON DELETE CASCADE deliberately.

Fix — SQL

-- Remove the referencing child rows first.
DELETE FROM child_r WHERE pid = 1;
DELETE FROM parent_r WHERE id = 1;

Diagnose

See it live on the server

Run these against the affected instance to confirm the diagnosis before you act.

Standard triage — not specific to this error

These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.

This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.

What is running right now

Active backends, how long each has been running, and what it is waiting on.

SELECT pid,
       state,
       wait_event_type,
       wait_event,
       now() - query_start AS running_for,
       left(query, 80)     AS query
FROM   pg_stat_activity
WHERE  state <> 'idle'
  AND  pid <> pg_backend_pid()
ORDER  BY running_for DESC NULLS LAST;

Who is blocking whom

Turn raw blocking PIDs into the actual queries on both sides of the wait.

SELECT blocked.pid    AS blocked_pid,
       blocked.query  AS blocked_query,
       blocking.pid   AS blocking_pid,
       blocking.query AS blocking_query
FROM   pg_stat_activity AS blocked
JOIN   LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN   pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE  cardinality(pg_blocking_pids(blocked.pid)) > 0;

Locks that are still waiting

Every lock a backend has requested but not yet been granted.

SELECT l.pid,
       l.locktype,
       l.mode,
       l.granted,
       COALESCE(c.relname, l.transactionid::text) AS object
FROM   pg_locks l
LEFT   JOIN pg_class c ON c.oid = l.relation
WHERE  NOT l.granted
ORDER  BY l.pid;

Reproduce & verify

A real, single-session PostgreSQL reproduction

A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.

1Create a parent/child foreign key with RESTRICT and insert a referenced parent row plus a child that references it.
2DELETE the parent while the child row still references it.
3PostgreSQL enforces RESTRICT and aborts the delete with SQLSTATE 23001.

A cleanup deleted a parent record while child rows still pointed at it, and the RESTRICT foreign key refused the delete.

Schema setup

CREATE TABLE parent_r(id int primary key);
CREATE TABLE child_r(id int, pid int REFERENCES parent_r(id) ON DELETE RESTRICT);
INSERT INTO parent_r VALUES (1);
INSERT INTO child_r VALUES (10, 1);

Deleting a referenced parent under RESTRICT

DELETE FROM parent_r WHERE id = 1;

The session continues normally

SELECT 'ok' AS session_after_error;

What PostgreSQL actually returned

Schema setup output

CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1

Deleting a referenced parent under RESTRICT output

ERROR:  update or delete on table "parent_r" violates RESTRICT setting of foreign key constraint "child_r_pid_fkey" on table "child_r"
DETAIL:  Key (id)=(1) is referenced from table "child_r".

The session continues normally output

 session_after_error 
---------------------
 ok
(1 row)

Related & next steps

Follow the thread

Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.

Verification

PG 18.4

Last verified: 2026-07-24 (isolated lab, PostgreSQL 18.4)
Reviewed by: Verified against PostgreSQL 18.4 in an isolated lab environment
Audit status: reviewed