如何为SQL表添加仅当Status为C时的Geographic ID唯一约束？

标准的UNIQUE CONSTRAINT不支持添加WHERE子句，要实现你需求的「仅当Status为'C'时，Geographic ID唯一」，正确的做法是用筛选（过滤）唯一索引，不同数据库的实现语法略有差异，下面是主流数据库的解决方案：

SQL Server #

直接创建筛选唯一索引：

CREATE UNIQUE NONCLUSTERED INDEX UQ_GeographicID_CurrentStatus
ON YourTableName (GeographicID)
WHERE Status = 'C';

这个索引只会对Status = 'C'的行生效，确保同一个GeographicID最多只有一条「当前」状态的记录，其他状态的行不受限制。

PostgreSQL #

PostgreSQL支持部分唯一索引，语法如下：

CREATE UNIQUE INDEX UQ_GeographicID_CurrentStatus
ON YourTableName (GeographicID)
WHERE Status = 'C';

如果更倾向用约束的形式，PostgreSQL 11及以上版本支持带WHERE条件的唯一约束：

ALTER TABLE YourTableName
ADD CONSTRAINT UQ_GeographicID_CurrentStatus
UNIQUE (GeographicID)
WHERE Status = 'C';

MySQL #

MySQL 8.0.13及以上版本可以通过表达式唯一约束实现，或者用虚拟列配合唯一约束：

方法一：表达式唯一约束 #

ALTER TABLE YourTableName
ADD CONSTRAINT UQ_GeographicID_CurrentStatus
UNIQUE (GeographicID, CASE WHEN Status = 'C' THEN Status ELSE NULL END);

方法二：虚拟列+唯一约束 #

-- 先创建存储型虚拟列，标记是否为当前状态
ALTER TABLE YourTableName
ADD COLUMN IsCurrent BOOLEAN GENERATED ALWAYS AS (Status = 'C') STORED;

-- 针对虚拟列为TRUE的行，强制GeographicID唯一
ALTER TABLE YourTableName
ADD CONSTRAINT UQ_GeographicID_CurrentStatus
UNIQUE (GeographicID, IsCurrent)
WHERE IsCurrent = TRUE;

你之前尝试失败的可能原因 #

1. 语法不匹配：不同数据库的筛选索引/约束语法有差异，比如MySQL早期版本不支持直接带WHERE的唯一索引，用错语法会导致创建失败
2. 现有数据违反约束：创建索引或约束前，表中已经存在同一个GeographicID对应多条Status='C'的记录，这种情况下数据库会拒绝创建，需要先清理重复数据

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