↑↓ 选择 ↵ 打开 ⌫ 改范围 完整检索页

pgsql.cc 提供对 postgresql.org 官网内容的中文翻译，由 Pigsty 团队维护。

百科 / 错误代码 / Class 23 完整性约束冲突

23001 restrict_violation

RESTRICT 约束冲突

ERROR 已实测 详解 实测通过

类别
Class 23 完整性约束冲突
严重等级
ERROR
条件名
restrict_violation
宏名称
ERRCODE_RESTRICT_VIOLATION
启用版本
7.4
状态
活跃

版本覆盖

  1. 18
  2. 17
  3. 16
  4. 15
  5. 14
  1. 13
  2. 12
  3. 11
  4. 10
  1. 19 beta
  2. 20 dev

速览

23001 是立即执行的外键 RESTRICT 动作导致的专用冲突。18.6 案例删除仍被引用的父行时返回该码；10.21 对同一操作返回 23503，必须记录版本和完整诊断。另一个 PostgreSQL 17.11 对照案例对该 RESTRICT 操作也返回 23503，不计作 23001 覆盖。

含义

ON DELETE/UPDATE RESTRICT 会立即检查引用行，且不能延迟；可延迟的 NO ACTION 则可以把检查推迟到相应提交点。源码根据父表、外键、子表和可见键值动态组装报文。

诊断

记录精确 SQLSTATE、constraint_name、表名和 DETAIL，先检查子表。显式事务删除失败后连接为 INERROR，必须 ROLLBACK，再在新的 BEGIN/COMMIT 中删除子行和父行。

处理

按完整性语义选择删除/改派依赖行、放弃父操作或重新设计 FK 动作。不要为了迁移方便把 RESTRICT 偷换成可延迟 NO ACTION。

可复现案例

在一次性实例上执行过的场景。其中 1 个附有可执行 SQL，正文相应小节里给出。

restrict_delete_referenced_parent PG 18 有 SQL

前置条件

  • A child row references a parent through ON DELETE RESTRICT
  • The parent delete is attempted while the child remains

触发

Delete the referenced parent row.

断言

  • SQLSTATE is 23001
  • The RESTRICT constraint and referencing table are identified
  • Deleting the child first permits an explicit parent repair

处置

Resolve the dependent row according to business rules, then delete or retain the parent; changing RESTRICT to another action is a schema decision, not an error fix.

清理

Drop the case schema with an owner connection.

实测诊断

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；最终计数 [0, 0]。该边界案例不计作 23001 覆盖。

报文模板

源码里的格式串，不是某一次运行的输出。%s 之类是占位符，实际报文会填入对象名与取值。适用范围一栏是核验时留下的原始英文记录，未经翻译。

主消息 update or delete on table "%s" violates RESTRICT setting of foreign key constraint "%s" on table "%s"
DETAIL Key (%s)=(%s) is referenced from table "%s".

来源：src/backend/utils/adt/ri_triggers.c @ REL_18_6

适用范围：The privilege-aware branch can instead emit the source detail template without key values.

代表案例

运行器从 verify/cases/23001/snippets.json（SHA-256 6086c1ce982afa5438bbcd29b86ec4cd0cbe0c76fa6152c1eb4a330a7706d5c8）读取下列片段，并为临时 schema 替换表名；完整 setup、断言与清理见 案例导出。

-- 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);

本案例对应的 SQLSTATE、诊断、事务状态和修复断言均来自上述共享 registry；结构化证据 · 案例导出。

作者证据 ID：identity、restrict-path、no-action-boundary、runtime、runtime.pg17-boundary。选定基础运行记录：runtime.23001-batch1-latest2-20260909.latest、runtime.23001-batch1-pg10b-20260909.pg10。单独的边界记录：runtime.23001-boundary-pg17-final-20260909.pg17。

版本与边界

锁定目录在 7.4 已观察到该条件，并在列出的正式快照中均存在。选定基础案例在 18.6 观察到 23001，在 10.21 对同一 RESTRICT 删除观察到 23503。另一个 PG17.11 边界对照也返回 23503，并保持 INERROR 到 IDLE 的恢复边界；该记录单独保留，不能推广为所有 PostgreSQL 11–17 小版本的普遍结果。

来源

证据

断言

每条断言都写明了是怎么核实的，以及它不覆盖什么。这一层是核验时留下的原始英文记录，照原样呈现，未经翻译。

  • 23001 is restrict_violation, a specific Class 23 condition.

    核实方式Read the condition definition and macro.

    不覆盖The code is narrower than the general foreign-key violation code 23503.

    来源src/backend/utils/errcodes.txt

  • The RI trigger reports 23001 when an immediate ON DELETE or ON UPDATE RESTRICT action finds a referencing row.

    核实方式Trace the fixed ri_ReportViolation call and compare the documented action timing.

    不覆盖The exact DETAIL depends on privileges and key values; do not treat one rendered DETAIL as a static format string.

    来源src/backend/utils/adt/ri_triggers.c · raw/calls/REL_18_6.jsonl · doc/src/sgml/ddl.sgml

  • RESTRICT is checked immediately and is not deferrable; NO ACTION can be deferred when the constraint is declared DEFERRABLE, and PostgreSQL 10 uses the general 23503 path for the tested RESTRICT delete.

    核实方式Read action semantics and the two selected runs.

    不覆盖The PG10 result is a version/path boundary, not evidence that the user operation succeeded.

    来源src/backend/utils/adt/ri_triggers.c · doc/src/sgml/ddl.sgml

  • The selected PG18 run returned 23001, entered INERROR, recovered after ROLLBACK, and committed deletion after removing the dependent row; PG10 returned 23503 for the same mechanism.

    核实方式Compare summaries and raw diagnostics with the shared registry.

    不覆盖Only the PG18 result is an observed runtime for code 23001; the PG10 case is explicitly not_applicable for that code.

  • On the fixed PG17.11 boundary case, the same referenced-parent ON DELETE RESTRICT operation returned the general foreign_key_violation 23503 rather than dedicated 23001; the failed transaction moved from INERROR to IDLE after ROLLBACK and cleanup assertions passed.

    核实方式Read the PG17 boundary summary for the exact primary/detail, SQLSTATE, transaction statuses, and final counts.

    不覆盖This is one PG17.11 comparison point and is not a universal claim for every PostgreSQL 11–17 minor release. It is not an observed 23001 runtime.

运行记录

目标服务器版本结果覆盖案例
latest 18.6 (Homebrew) passed restrict_delete_referenced_parent
pg10 10.21 (Debian 10.21-1.pgdg90+1) not_applicable restrict_delete_referenced_parent
pg17 17.11 (Debian 17.11-1.pgdg13+2) not_applicable restrict_delete_referenced_parent

同类错误代码

Class 23 完整性约束冲突 下的其他成员。