MS-SQL能否实现针对特定状态的多列内容感知唯一约束？

我正在构建流程引擎，需确保同一案例在同一时间仅存在一个处于PENDING（待处理）或ACTIVE（活跃）状态的流程实例，待之前的实例完成后可启动相同流程。为在不可靠网络与响应丢失场景下保障最高可靠性，希望通过数据库约束实现该需求。

我的表包含以下列：

• process_id（PRIMARY KEY，主键）
• case_id（FOREIGN KEY，外键）
• state（取值为PENDING | ACTIVE | COMPLETED）

规则说明 #

• 允许同一case_id对应多条state为COMPLETED（已完成）的记录以支持流程重启
• 禁止同一case_id存在多条PENDING或ACTIVE状态的记录
• 禁止同一case_id同时存在PENDING和ACTIVE状态的记录

允许的场景示例 #

process_id    case_id    state
------------------------------
1             1          COMPLETED
2             1          COMPLETED <-- case_id重复允许对应多条COMPLETED
3             1          PENDING

不允许的场景示例1（同一case_id存在多条PENDING） #

process_id    case_id    state
------------------------------
1             1          COMPLETED
2             1          COMPLETED
3             1          PENDING
4             1          PENDING   <-- 同一case_id不允许存在多条PENDING！

不允许的场景示例2（同一case_id同时存在ACTIVE和PENDING） #

process_id    case_id    state
------------------------------
1             1          COMPLETED
2             1          COMPLETED
3             1          ACTIVE
4             1          PENDING   <-- 同一case_id不允许同时存在ACTIVE和PENDING！

请问MS-SQL是否支持此类内容感知型唯一约束？

MS-SQL支持通过**过滤唯一索引（Filtered Unique Index）**实现这种内容感知型约束，这是数据库层面的原生解决方案，能在不可靠网络环境下保障数据一致性。

创建约束的SQL语句 #

CREATE UNIQUE NONCLUSTERED INDEX IX_Process_CaseId_ActivePending
ON YourTableName (case_id)
WHERE state IN ('PENDING', 'ACTIVE');

原理说明 #

这个索引仅对state为PENDING或ACTIVE的记录生效：

• 当插入或更新记录时，若state属于PENDING/ACTIVE，数据库会强制检查该case_id是否已存在同状态或另一活跃状态的记录，存在则直接抛出约束冲突错误
• 对于state为COMPLETED的记录，该索引不生效，因此允许同一case_id存在多条已完成记录，完全满足流程重启需求

这种方式无需依赖应用层逻辑，是强一致性的保障方案，能有效规避网络异常、响应丢失等场景下的数据不一致问题。

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