PostgreSQL 如何用约束和 ON CONFLICT 保证数据一致性？

简化版

PostgreSQL 可以通过主键、唯一约束、外键、检查约束、非空约束保证基础数据一致性。INSERT ... ON CONFLICT 用来处理唯一冲突，实现插入或更新，也就是 UPSERT。真正可靠的一致性应该优先交给数据库约束兜底，应用层校验只能作为前置体验优化，不能替代约束。

详细版

常见约束包括：

PRIMARY KEY：唯一标识一行；
UNIQUE：保证某列或多列组合不重复；
NOT NULL：字段不能为空；
CHECK：保证字段满足条件；
FOREIGN KEY：保证引用关系有效；
EXCLUDE：处理范围重叠等更复杂约束。

ON CONFLICT 常见写法：

INSERT INTO users(email, name)
VALUES ('a@example.com', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

它依赖唯一索引或唯一约束识别冲突，适合幂等写入、同步数据、计数更新等场景。

完整版教学

一、为什么数据库约束比应用校验更可靠

应用层可以先查：

SELECT * FROM users WHERE email = 'a@example.com';

如果不存在再插入。但并发场景下，两个请求可能同时查到不存在，然后同时插入。没有唯一约束时，重复数据就产生了。

数据库约束处在最终写入点，能在并发下兜住底线。应用层校验适合提前给用户友好提示，但不能替代数据库约束。

二、主键和唯一约束保证不重复

主键用于标识一行，唯一约束用于表达业务唯一性。例如：

CREATE TABLE users (
  id bigserial PRIMARY KEY,
  email text NOT NULL UNIQUE
);

如果业务要求同一租户内用户名唯一，可以建组合唯一：

CREATE UNIQUE INDEX uk_tenant_username
ON users(tenant_id, username);

面试时要强调：主键解决行身份，唯一约束解决业务规则，两者不是完全等价的概念。

三、外键保证引用关系

外键可以保证子表引用的父表记录存在：

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users(id)
);

它能防止订单引用一个不存在的用户。是否使用外键要结合业务规模和团队治理能力判断，但如果不用数据库外键，也必须有清晰的应用层和数据巡检方案。

不能把“不用外键”理解为“不需要引用一致性”，只是把一致性维护责任从数据库转移到了业务系统。

四、CHECK 约束表达字段规则

CHECK 可以把简单业务规则放进数据库：

CREATE TABLE account (
  id bigserial PRIMARY KEY,
  balance numeric NOT NULL CHECK (balance >= 0)
);

这样即使应用代码出现漏洞，数据库也不会接受负余额。

适合放入 CHECK 的规则通常是字段自身或同一行内能判断的规则。跨表复杂规则不一定适合 CHECK，需要事务、锁、触发器或更强隔离级别配合。

五、ON CONFLICT 处理并发插入冲突

ON CONFLICT 可以实现插入时冲突处理：

INSERT INTO users(email, name)
VALUES ('a@example.com', 'Alice')
ON CONFLICT (email)
DO NOTHING;

或者冲突时更新：

INSERT INTO users(email, name)
VALUES ('a@example.com', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

其中 EXCLUDED 表示本次准备插入但发生冲突的那行数据。

它适合：

幂等创建用户；
同步外部系统数据；
写入唯一业务单据；
插入不存在则创建，存在则更新。

六、ON CONFLICT 的使用边界

ON CONFLICT 必须依赖唯一约束、唯一索引或排他约束来判断冲突。没有冲突目标，数据库不知道什么叫“重复”。

使用时还要注意：

更新逻辑是否会覆盖旧值；
是否需要条件更新；
并发下是否会造成热点行竞争；
是否应该返回最终行；
是否需要配合业务幂等号。

对于金额、库存这类字段，不要随意用新值覆盖旧值，通常要用更明确的增量更新和条件约束。

七、常见误区与追问

机制	解决的问题	常见边界
`UNIQUE`	防重复业务键	并发下最终兜底
`CHECK`	同一行字段合法性	不适合复杂跨表规则
`ON CONFLICT DO UPDATE`	冲突时更新已有行	可能覆盖旧值或制造热点

记忆钩子：应用层校验是“提前提醒”，数据库约束是“最后防线”；ON CONFLICT 不是魔法，它必须依赖唯一索引、唯一约束或排他约束定义冲突。

一个典型并发例子是：同一秒 100 个请求都想创建 email='a@example.com' 的用户。没有唯一约束时，100 个请求都可能先查不到再插入；有 UNIQUE(email) 时，最多 1 条插入成功，其余 99 条只能进入冲突处理。ON CONFLICT DO NOTHING 适合“已有就忽略”，DO UPDATE 适合“已有就合并新值”，但更新字段要非常克制。

误区：应用层先查再插就能保证唯一。 并发下两个事务可能同时查不到，最终必须靠数据库唯一约束兜底。
误区：ON CONFLICT 不需要唯一索引也能判断重复。 PostgreSQL 需要冲突目标，没有唯一或排他约束就不知道哪种冲突应被处理。
误区：DO UPDATE 可以随便覆盖已有字段。 同步外部数据时可能可以覆盖，金额、库存、状态这类字段要用条件更新或状态机保护。
追问：EXCLUDED 是什么？ 它表示本次准备插入但发生冲突的那行新数据，可在 DO UPDATE 里引用。
追问：CHECK 适合写多复杂的业务规则？ 适合同一行内可判断的规则，如金额非负；跨表、跨行约束通常要靠唯一约束、外键、事务锁或触发器。
追问：高并发 UPSERT 有什么风险？ 冲突都集中到同一行时会产生热点更新和锁等待，计数器、库存类场景要评估吞吐和重试策略。

八、加强记忆

PostgreSQL 数据一致性要先想约束：主键管身份，唯一管不重复，外键管引用，CHECK 管字段规则，ON CONFLICT 管并发插入冲突。应用层校验负责体验，数据库约束负责兜底。

简化版

详细版