↑↓ 选择↵ 打开⌫ 切换范围完整搜索

PG.CENTER 连接 PostgreSQL 文档、百科与生态知识。由 Pigsty 维护。

Wiki / SQLSTATE

23514 — CHECK 约束冲突（check_violation）

CHECK 约束计算为假，写入的行不满足条件。

英文手册覆盖始于 PostgreSQL 8.1. 来源历史与运行验证各自保留独立版本边界。

当前阅读 PG 18·选择有来源记录的版本

8.18.28.38.49.09.19.29.39.49.59.61011121314151617181920
aliases
未知
class name
Integrity Constraint Violation
condition name
check_violation
lang
en
sqlstate
23514

版本定义 PG 18

23514 — CHECK 约束冲突

速览 {#at-a-glance}

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

含义 {#meaning}

普通表行由执行器计算 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，但机制和报文不同。

诊断 {#diagnosis}

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

处理 {#response}

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

实测诊断 {#messages}

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。

代表案例 {#case}

本例第一次插入因 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。

版本与边界 {#versions}

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

相关 {#related}

对比 23502 非空约束冲突、23P01 排除约束冲突 和 23505 唯一约束冲突。

来源 {#sources}

  • src.errcodes.18.6 (SHA-256 6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba)
  • src.calls.REL_18_6 (SHA-256 9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf)
  • src.execMain.18.6 (SHA-256 33b97337fa23a649c5e7a092e1bd405a54e8503529236c62c9d5bb93a1774a8d)
  • src.tablecmds.18.6 (SHA-256 422dc8e833df4940755973c757e338295822ba3e4c7266c40669f49f96eb15a9)
  • src.execPartition.18.6 (SHA-256 97951428f673d4eb6dd23141817874c29640aaac0d6dc74015c993cbd0636dd6)
  • src.typecmds.18.6 (SHA-256 60d1e9754646e100f6b74aa367ad8c2c52386c400df721707423d329209c47eb)
  • src.execIndexing.18.6 (SHA-256 24c80553ab4b28d7d2a288dd9196c8db9459c7f308853cd8c8c939485e15f199)
  • doc.ddl-constraints.18.6 (SHA-256 ce1919d9236f2e71672660e1a347146472e966e4d19b77fde5ae345dd1db6ec7) · 官方文档

比较版本

SQLSTATE 证据与已记录的运行版本

cases · check_constraint_insert
{
  "assertions": [
    "SQLSTATE is 23514",
    "The named relation and constraint are identified",
    "A valid row can be inserted after the error"
  ],
  "case_id": "check_constraint_insert",
  "cleanup": "Drop the case schema with an owner connection.",
  "has_snippet": true,
  "position": 0,
  "preconditions": [
    "The target table has a named CHECK constraint",
    "The inserted value fails that predicate"
  ],
  "repair": "Correct the value or deliberately revise the constraint after reviewing existing data; do not disable validation to hide a contract violation.",
  "trigger": "Insert a row whose value violates the CHECK expression.",
  "versions": [
    "10",
    "18"
  ]
}
claims · identity
{
  "claim_id": "identity",
  "limits": "It denotes a failed CHECK or related partition/default constraint path, not only a named table CHECK.",
  "method": "Read the fixed errcodes row.",
  "position": 0,
  "runtime": [],
  "sources": [
    "src.errcodes.18.6"
  ],
  "statement": "23514 is check_violation in Class 23."
}
claims · row-path
{
  "claim_id": "row-path",
  "limits": "A CHECK expression that evaluates to NULL is accepted by PostgreSQL; use NOT NULL when NULL itself is invalid.",
  "method": "Trace the executor report group and errtableconstraint metadata.",
  "position": 1,
  "runtime": [],
  "sources": [
    "src.execMain.18.6",
    "src.calls.REL_18_6"
  ],
  "statement": "The executor reports 23514 with the relation and failed CHECK name when a row evaluates to false."
}
claims · schema-path
{
  "claim_id": "schema-path",
  "limits": "Repairing data may require a migration or a different partition route; do not assume every 23514 is a duplicate or syntax problem.",
  "method": "Compare the fixed source call groups.",
  "position": 2,
  "runtime": [],
  "sources": [
    "src.tablecmds.18.6",
    "src.calls.REL_18_6",
    "src.execPartition.18.6",
    "src.typecmds.18.6",
    "src.execIndexing.18.6"
  ],
  "statement": "Constraint validation, partition routing, and domain checks can produce different 23514 templates."
}
claims · runtime
{
  "claim_id": "runtime",
  "limits": "The case does not cover deferred checks, partition routing, domain checks, or validation-time errors.",
  "method": "Read the selected summaries and raw diagnostics.",
  "position": 3,
  "runtime": [],
  "sources": [
    "runtime.23514-batch1-latest-20260909.latest",
    "runtime.23514-batch1-pg10-20260909.pg10"
  ],
  "statement": "The selected named CHECK case returned 23514 on PG18.6 and PG10.21, identified amount_positive/items, stayed IDLE, and committed a valid row."
}
messages · row
{
  "limits": "The failing-row detail is conditional and can be suppressed by context or configuration.",
  "message_id": "row",
  "path": "",
  "position": 0,
  "raw": {
    "detail_template": "Failing row contains %s.",
    "id": "row",
    "limits": "The failing-row detail is conditional and can be suppressed by context or configuration.",
    "primary_template": "new row for relation \"%s\" violates check constraint \"%s\"",
    "severity_source": "ERROR",
    "sources": [
      "src.execMain.18.6"
    ],
    "sqlstate": "23514"
  },
  "severity": "ERROR",
  "sources": [
    "src.execMain.18.6"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "new row for relation \" \" violates check constraint \" \"",
      "position": 0,
      "role": "",
      "template": "new row for relation \"%s\" violates check constraint \"%s\""
    },
    {
      "kind": "detail",
      "literal": "Failing row contains .",
      "position": 1,
      "role": "",
      "template": "Failing row contains %s."
    }
  ]
}
messages · partition-no-route
{
  "limits": "The partition-key substitution is dynamically built; the path is not the named CHECK template.",
  "message_id": "partition-no-route",
  "path": "",
  "position": 1,
  "raw": {
    "detail_template": "Partition key of the failing row contains %s.",
    "id": "partition-no-route",
    "limits": "The partition-key substitution is dynamically built; the path is not the named CHECK template.",
    "primary_template": "no partition of relation \"%s\" found for row",
    "severity_source": "ERROR",
    "sources": [
      "src.execPartition.18.6"
    ],
    "sqlstate": "23514"
  },
  "severity": "ERROR",
  "sources": [
    "src.execPartition.18.6"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "no partition of relation \" \" found for row",
      "position": 0,
      "role": "",
      "template": "no partition of relation \"%s\" found for row"
    },
    {
      "kind": "detail",
      "literal": "Partition key of the failing row contains .",
      "position": 1,
      "role": "",
      "template": "Partition key of the failing row contains %s."
    }
  ]
}
messages · partition-constraint
{
  "limits": "The relation and failing-row description are dynamic.",
  "message_id": "partition-constraint",
  "path": "",
  "position": 2,
  "raw": {
    "detail_template": "Failing row contains %s.",
    "id": "partition-constraint",
    "limits": "The relation and failing-row description are dynamic.",
    "primary_template": "new row for relation \"%s\" violates partition constraint",
    "severity_source": "ERROR",
    "sources": [
      "src.execMain.18.6"
    ],
    "sqlstate": "23514"
  },
  "severity": "ERROR",
  "sources": [
    "src.execMain.18.6"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "new row for relation \" \" violates partition constraint",
      "position": 0,
      "role": "",
      "template": "new row for relation \"%s\" violates partition constraint"
    },
    {
      "kind": "detail",
      "literal": "Failing row contains .",
      "position": 1,
      "role": "",
      "template": "Failing row contains %s."
    }
  ]
}
messages · domain-validation
{
  "limits": "The column and table identify the existing domain use; this is validation of a new domain constraint.",
  "message_id": "domain-validation",
  "path": "",
  "position": 3,
  "raw": {
    "id": "domain-validation",
    "limits": "The column and table identify the existing domain use; this is validation of a new domain constraint.",
    "primary_template": "column \"%s\" of table \"%s\" contains values that violate the new constraint",
    "severity_source": "ERROR",
    "sources": [
      "src.typecmds.18.6"
    ],
    "sqlstate": "23514"
  },
  "severity": "ERROR",
  "sources": [
    "src.typecmds.18.6"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "column \" \" of table \" \" contains values that violate the new constraint",
      "position": 0,
      "role": "",
      "template": "column \"%s\" of table \"%s\" contains values that violate the new constraint"
    }
  ]
}
messages · without-overlaps-empty
{
  "limits": "The column and relation are dynamic; this is the empty-value guard, distinct from a conflict with an existing key.",
  "message_id": "without-overlaps-empty",
  "path": "",
  "position": 4,
  "raw": {
    "id": "without-overlaps-empty",
    "limits": "The column and relation are dynamic; this is the empty-value guard, distinct from a conflict with an existing key.",
    "primary_template": "empty WITHOUT OVERLAPS value found in column \"%s\" in relation \"%s\"",
    "severity_source": "ERROR",
    "sources": [
      "src.execIndexing.18.6"
    ],
    "sqlstate": "23514"
  },
  "severity": "ERROR",
  "sources": [
    "src.execIndexing.18.6"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "empty WITHOUT OVERLAPS value found in column \" \" in relation \" \"",
      "position": 0,
      "role": "",
      "template": "empty WITHOUT OVERLAPS value found in column \"%s\" in relation \"%s\""
    }
  ]
}
runtimes · runtime.23514-batch1-latest-20260909.latest · 18.6 (Homebrew) · passed
{
  "cases": [
    "check_constraint_insert"
  ],
  "limits": "",
  "position": 0,
  "raw": {
    "case_manifest": {
      "path": "verify/cases/23514/cases.json",
      "sha256": "86b43f2a10da9ff67b7b53b05fe3d16121b922f713be9f02f7c8caf558f73012"
    },
    "cases": [
      "check_constraint_insert"
    ],
    "id": "runtime.23514-batch1-latest-20260909.latest",
    "observed": {
      "constraint": "amount_positive",
      "detail": "Failing row contains (2, -1).",
      "primary": "new row for relation \"items\" violates check constraint \"amount_positive\"",
      "rows": [
        [
          1,
          10
        ],
        [
          2,
          1
        ]
      ],
      "sqlstate": "23514",
      "status_after_error": "IDLE",
      "table": "items"
    },
    "raw": "verify/results/23514-batch1-latest-20260909/latest/raw.jsonl",
    "raw_sha256": "11302e6a32ae75c2f4938099bcbcfeeb774ad58e3f404574c56b0c81ea828012",
    "run_id": "23514-batch1-latest-20260909",
    "server_version": "18.6 (Homebrew)",
    "server_version_num": 180006,
    "snippet_registry": {
      "path": "verify/cases/23514/snippets.json",
      "sha256": "0dcd48b5f9b3c4264c7843f77edae204d2a37b55a2b41bb2570febfdf6391d83"
    },
    "status": "passed",
    "summary": "verify/results/23514-batch1-latest-20260909/latest/summary.json",
    "summary_sha256": "ea15d7fcfc868444fd08a27f5a428e75f5d129e53732fa0ac19449bcc0e589a4",
    "target": "latest"
  },
  "run_id": "23514-batch1-latest-20260909",
  "runtime_id": "runtime.23514-batch1-latest-20260909.latest",
  "server_version": "18.6 (Homebrew)",
  "status": "passed",
  "target": "latest"
}
runtimes · runtime.23514-batch1-pg10-20260909.pg10 · 10.21 (Debian 10.21-1.pgdg90+1) · passed
{
  "cases": [
    "check_constraint_insert"
  ],
  "limits": "",
  "position": 1,
  "raw": {
    "case_manifest": {
      "path": "verify/cases/23514/cases.json",
      "sha256": "86b43f2a10da9ff67b7b53b05fe3d16121b922f713be9f02f7c8caf558f73012"
    },
    "cases": [
      "check_constraint_insert"
    ],
    "id": "runtime.23514-batch1-pg10-20260909.pg10",
    "observed": {
      "constraint": "amount_positive",
      "detail": "Failing row contains (2, -1).",
      "primary": "new row for relation \"items\" violates check constraint \"amount_positive\"",
      "rows": [
        [
          1,
          10
        ],
        [
          2,
          1
        ]
      ],
      "sqlstate": "23514",
      "status_after_error": "IDLE",
      "table": "items"
    },
    "raw": "verify/results/23514-batch1-pg10-20260909/pg10/raw.jsonl",
    "raw_sha256": "e0a09ba6b64cc6ba16b2038fb8fee8541d55b047369068ccca8b2406c5700699",
    "run_id": "23514-batch1-pg10-20260909",
    "server_version": "10.21 (Debian 10.21-1.pgdg90+1)",
    "server_version_num": 100021,
    "snippet_registry": {
      "path": "verify/cases/23514/snippets.json",
      "sha256": "0dcd48b5f9b3c4264c7843f77edae204d2a37b55a2b41bb2570febfdf6391d83"
    },
    "status": "passed",
    "summary": "verify/results/23514-batch1-pg10-20260909/pg10/summary.json",
    "summary_sha256": "599d10014ead75a34e9a0f59a7485ba0cdbfc350fa01456201c8bda00b29aa10",
    "target": "pg10"
  },
  "run_id": "23514-batch1-pg10-20260909",
  "runtime_id": "runtime.23514-batch1-pg10-20260909.pg10",
  "server_version": "10.21 (Debian 10.21-1.pgdg90+1)",
  "status": "passed",
  "target": "pg10"
}
sources · src.errcodes.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/errcodes.txt",
  "position": 0,
  "sha256": "6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba",
  "source_id": "src.errcodes.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt"
}
sources · src.calls.REL_18_6
{
  "commit": "",
  "docs_url": "",
  "kind": "local_artifact",
  "location": "",
  "path": "raw/calls/REL_18_6.jsonl",
  "position": 1,
  "sha256": "9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf",
  "source_id": "src.calls.REL_18_6",
  "tag": "",
  "url": ""
}
sources · src.execMain.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/executor/execMain.c",
  "position": 2,
  "sha256": "33b97337fa23a649c5e7a092e1bd405a54e8503529236c62c9d5bb93a1774a8d",
  "source_id": "src.execMain.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/executor/execMain.c#L2076-L2081"
}
sources · src.tablecmds.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/commands/tablecmds.c",
  "position": 3,
  "sha256": "422dc8e833df4940755973c757e338295822ba3e4c7266c40669f49f96eb15a9",
  "source_id": "src.tablecmds.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/tablecmds.c#L6502-L6532"
}
sources · doc.ddl-constraints.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "https://www.postgresql.org/docs/18/ddl-constraints.html",
  "kind": "upstream_source",
  "location": "",
  "path": "doc/src/sgml/ddl.sgml",
  "position": 4,
  "sha256": "ce1919d9236f2e71672660e1a347146472e966e4d19b77fde5ae345dd1db6ec7",
  "source_id": "doc.ddl-constraints.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/doc/src/sgml/ddl.sgml"
}
sources · src.execPartition.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/executor/execPartition.c",
  "position": 5,
  "sha256": "97951428f673d4eb6dd23141817874c29640aaac0d6dc74015c993cbd0636dd6",
  "source_id": "src.execPartition.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/executor/execPartition.c#L328-L335"
}
sources · src.typecmds.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/commands/typecmds.c",
  "position": 6,
  "sha256": "60d1e9754646e100f6b74aa367ad8c2c52386c400df721707423d329209c47eb",
  "source_id": "src.typecmds.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/typecmds.c#L3288-L3293"
}
sources · src.execIndexing.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/executor/execIndexing.c",
  "position": 7,
  "sha256": "24c80553ab4b28d7d2a288dd9196c8db9459c7f308853cd8c8c939485e15f199",
  "source_id": "src.execIndexing.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/executor/execIndexing.c#L1176-L1181"
}

来源引用

完整定义与证据 JSON

定义来源

center · PostgreSQL 18 · english-manuals:b011581c87322615fa999fcacb52ea1485b2352d3b24aca109a54334f3d12a9e

正文语言: zh-Hans · english-manuals:b011581c87322615fa999fcacb52ea1485b2352d3b24aca109a54334f3d12a9e