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

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

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

23514 check_violation

CHECK 约束冲突

ERROR 已实测 详解 实测通过

类别
Class 23 完整性约束冲突
严重等级
ERROR
条件名
check_violation
宏名称
ERRCODE_CHECK_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

速览

23514 表示 CHECK 或相关行约束计算为假。本案例中的约束名为 amount_positive，报文带出表、约束和失败行。

含义

普通表行由执行器计算 CHECK 表达式，结果为假时报告 23514；CHECK 表达式为 NULL 时在 PostgreSQL 中通过，若 NULL 本身不允许，需要另加 NOT NULL。分区路由可能返回 no partition of relation ... found for row 及分区键 DETAIL，分区约束校验则使用分区约束报文；域校验有“values that violate the new constraint”模板，空的 WITHOUT OVERLAPS 值也有独立源码路径。它们使用同一 SQLSTATE，但机制和报文不同。

诊断

记录约束名、表名和失败行或分区键 DETAIL，按实际类型、隐式类型转换、触发器改写和 NULL 规则重算表达式。遇到分区错误时检查分区边界及该键的路由结果；域或校验时错误则确认实际执行的是哪个模式对象规则。本案例自动提交错误后为 IDLE；显式事务仍须按调用边界回滚。

处理

修正值或业务规则后重试。修改约束前先校验既有数据，不要为了掩盖坏数据而禁用 CHECK；分区场景应选择允许该值的分区，而不是把错误当作普通重复提交。

可复现案例

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

check_constraint_insert PG 10 / 18 有 SQL

前置条件

  • The target table has a named CHECK constraint
  • The inserted value fails that predicate

触发

Insert a row whose value violates the CHECK expression.

断言

  • SQLSTATE is 23514
  • The named relation and constraint are identified
  • A valid row can be inserted after the error

处置

Correct the value or deliberately revise the constraint after reviewing existing data; do not disable validation to hide a contract violation.

清理

Drop the case schema with an owner connection.

实测诊断

18.6 (Homebrew) / latest：SQLSTATE 23514；primary new row for relation "items" violates check constraint "amount_positive"；DETAIL Failing row contains (2, -1).；status_after_error IDLE。 10.21 (Debian 10.21-1.pgdg90+1) / pg10：SQLSTATE 23514；primary new row for relation "items" violates check constraint "amount_positive"；DETAIL Failing row contains (2, -1).；status_after_error IDLE。

报文模板

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

主消息 new row for relation "%s" violates check constraint "%s"
DETAIL Failing row contains %s.

来源：src/backend/executor/execMain.c @ REL_18_6

适用范围：The failing-row detail is conditional and can be suppressed by context or configuration.

主消息 no partition of relation "%s" found for row
DETAIL Partition key of the failing row contains %s.

来源：src/backend/executor/execPartition.c @ REL_18_6

适用范围：The partition-key substitution is dynamically built; the path is not the named CHECK template.

主消息 new row for relation "%s" violates partition constraint
DETAIL Failing row contains %s.

来源：src/backend/executor/execMain.c @ REL_18_6

适用范围：The relation and failing-row description are dynamic.

主消息 column "%s" of table "%s" contains values that violate the new constraint

来源：src/backend/commands/typecmds.c @ REL_18_6

适用范围：The column and table identify the existing domain use; this is validation of a new domain constraint.

主消息 empty WITHOUT OVERLAPS value found in column "%s" in relation "%s"

来源：src/backend/executor/execIndexing.c @ REL_18_6

适用范围：The column and relation are dynamic; this is the empty-value guard, distinct from a conflict with an existing key.

代表案例

本例第一次插入因 amount 为 -1 违反命名 CHECK；改为 1 即为具体修复。完整 setup、断言与清理见案例导出：

-- create
CREATE TABLE items(id integer PRIMARY KEY, amount integer CONSTRAINT amount_positive CHECK (amount > 0));
-- seed
INSERT INTO items VALUES (1, 10);
-- trigger
INSERT INTO items VALUES (2, -1);
-- repair
INSERT INTO items VALUES (2, 1);
-- verify
SELECT id, amount FROM items ORDER BY id;

本案例对应的 SQLSTATE、诊断、事务状态和修复断言均来自经核对的案例 registry；见结构化证据和案例导出。

作者证据 ID：identity, row-path, schema-path, runtime。选定运行记录：runtime.23514-batch1-latest-20260909.latest, runtime.23514-batch1-pg10-20260909.pg10。

版本与边界

锁定目录在 7.4 已观察到该条件，并在列出的正式快照中均存在。选定运行覆盖 18.6 与 10.21 的立即 named CHECK INSERT；未覆盖分区路由、域校验、校验时错误或 WITHOUT OVERLAPS 路径。固定 DDL 文档说明 CHECK 在表达式为真或 NULL 时通过，没有定义 DEFERRABLE CHECK 路径。

来源

证据

断言

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

  • 23514 is check_violation in Class 23.

    核实方式Read the fixed errcodes row.

    不覆盖It denotes a failed CHECK or related partition/default constraint path, not only a named table CHECK.

    来源src/backend/utils/errcodes.txt

  • The executor reports 23514 with the relation and failed CHECK name when a row evaluates to false.

    核实方式Trace the executor report group and errtableconstraint metadata.

    不覆盖A CHECK expression that evaluates to NULL is accepted by PostgreSQL; use NOT NULL when NULL itself is invalid.

    来源src/backend/executor/execMain.c · raw/calls/REL_18_6.jsonl

  • Constraint validation, partition routing, and domain checks can produce different 23514 templates.

    核实方式Compare the fixed source call groups.

    不覆盖Repairing data may require a migration or a different partition route; do not assume every 23514 is a duplicate or syntax problem.

    来源src/backend/commands/tablecmds.c · raw/calls/REL_18_6.jsonl · src/backend/executor/execPartition.c · src/backend/commands/typecmds.c · src/backend/executor/execIndexing.c

  • The selected named CHECK case returned 23514 on PG18.6 and PG10.21, identified amount_positive/items, stayed IDLE, and committed a valid row.

    核实方式Read the selected summaries and raw diagnostics.

    不覆盖The case does not cover deferred checks, partition routing, domain checks, or validation-time errors.

运行记录

目标服务器版本结果覆盖案例
latest 18.6 (Homebrew) passed check_constraint_insert
pg10 10.21 (Debian 10.21-1.pgdg90+1) passed check_constraint_insert

同类错误代码

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