主键与唯一键约束检查优先级及同步程序适配问题咨询

我开发了一个用于同步两个数据库记录的程序，该程序依赖插入时触发的主键冲突来判断记录已存在，进而执行更新操作，以此避免执行显式SQL查询检查记录是否存在。

但针对某一特定表，该表同时拥有主键（BON_ID）和基于不同列（BON_POSID、BON_NUM）的唯一索引。当插入重复记录时，唯一索引冲突先被触发，主键冲突未发生，导致程序逻辑失效。

请问是否有办法确保主键冲突始终优先触发？或者是否存在明确的约束检查顺序规则？

该表DDL如下：

CREATE TABLE BONS 
(
    BON_ID                 INTEGER NOT NULL,
    BON_OPENTIME           TIMESTAMP NOT NULL,
    BON_CLOSETIME          TIMESTAMP NOT NULL,
    BON_SALESMANID         INTEGER NOT NULL,
    BON_POSID              INTEGER NOT NULL,
    BON_TABLENO            SMALLINT,
    BON_NUM                INTEGER NOT NULL,
    ...
);

ALTER TABLE BONS ADD CONSTRAINT PK_BON_ID PRIMARY KEY (BON_ID);

CREATE UNIQUE INDEX IDX_UNQ_BON_POSID_NO ON BONS (BON_POSID, BON_NUM);

约束检查顺序的本质 #

首先明确：主流数据库（如PostgreSQL、MySQL）并没有定义固定的约束检查顺序，这个顺序可能受约束创建顺序、存储引擎实现、数据库版本等因素影响，完全不可靠，不能作为程序逻辑的依赖。所以不存在“确保主键冲突优先触发”的通用方法。

可行的替代方案 #

1. 使用数据库原生UPSERT语法 #

直接使用数据库提供的INSERT ... ON CONFLICT（PostgreSQL）或INSERT ... ON DUPLICATE KEY UPDATE（MySQL）语法，明确指定以主键作为冲突判断依据，彻底绕开约束检查顺序的问题。

PostgreSQL示例：

INSERT INTO BONS (BON_ID, BON_OPENTIME, BON_CLOSETIME, BON_SALESMANID, BON_POSID, BON_TABLENO, BON_NUM)
VALUES (?, ?, ?, ?, ?, ?, ?)
ON CONFLICT (BON_ID) DO UPDATE
SET BON_OPENTIME = EXCLUDED.BON_OPENTIME,
    BON_CLOSETIME = EXCLUDED.BON_CLOSETIME,
    BON_SALESMANID = EXCLUDED.BON_SALESMANID,
    BON_POSID = EXCLUDED.BON_POSID,
    BON_TABLENO = EXCLUDED.BON_TABLENO,
    BON_NUM = EXCLUDED.BON_NUM;

MySQL示例：

INSERT INTO BONS (BON_ID, BON_OPENTIME, BON_CLOSETIME, BON_SALESMANID, BON_POSID, BON_TABLENO, BON_NUM)
VALUES (?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
BON_OPENTIME = VALUES(BON_OPENTIME),
BON_CLOSETIME = VALUES(BON_CLOSETIME),
BON_SALESMANID = VALUES(BON_SALESMANID),
BON_POSID = VALUES(BON_POSID),
BON_TABLENO = VALUES(BON_TABLENO),
BON_NUM = VALUES(BON_NUM);

这种方式会直接针对主键冲突执行更新，其他唯一索引的冲突只会在主键不存在但唯一键重复时触发报错（符合业务逻辑，因为这种情况属于数据异常）。

2. 捕获冲突错误并区分类型 #

如果无法使用原生UPSERT，可以在程序中捕获唯一约束冲突错误，通过解析错误信息中的约束/索引名称来区分是主键冲突还是唯一索引冲突：

• PostgreSQL：所有唯一约束冲突错误码都是23505，但错误信息会包含触发冲突的约束名（如PK_BON_ID或IDX_UNQ_BON_POSID_NO），可以据此判断。
• MySQL：唯一约束冲突错误码是1062，错误信息会包含触发冲突的索引名，同样可以解析判断。
  根据判断结果，仅在主键冲突时执行更新操作，唯一索引冲突则根据业务需求处理（如报错、忽略）。

3. 业务逻辑层面优化 #

如果业务允许，确保同步时待插入记录的BON_ID是唯一标识，不会出现BON_ID不存在但BON_POSID+BON_NUM重复的情况——这种情况本身属于数据不一致，需要从数据源层面排查解决。

内容的提问来源于stack exchange，提问作者Stan