如何创建accepted=1时生效的条件组合唯一索引

你之前编写的索引语句存在两处语法问题：CASE表达式缺少END闭合，且不符合唯一索引的列定义规则。根据你使用的数据库类型，选择以下任意一种写法即可实现需求：

方案1：部分唯一索引（推荐，支持PostgreSQL、MySQL 8.0+、SQL Server） #

这种写法通过索引过滤条件，仅对accepted=1的已接受记录建立唯一索引，未接受的记录不会进入索引，自然不会触发唯一性校验，索引体积小、校验效率最高：

-- 先删除原有失效的唯一索引
DROP INDEX IF EXISTS uk_index ON my_table;

-- 创建满足新规则的部分唯一索引
CREATE UNIQUE INDEX uk_index ON my_table (nbr, EXTRACT(YEAR FROM date_))
WHERE accepted = 1;

该索引的校验逻辑完全匹配业务规则：

• 所有accepted=1的记录，相同nbr+年份的组合仅允许存在1条，重复会直接触发唯一约束冲突
• 所有accepted!=1的记录，不受该唯一索引约束，nbr和年份重复也可以正常写入

方案2：函数式唯一索引（兼容Oracle、MySQL 5.x等不支持部分索引的数据库） #

如果你的数据库不支持带WHERE条件的部分索引，可以通过补全CASE表达式创建函数索引，给未接受的记录返回全局唯一的主键值，避免触发冲突：

-- 先删除原有失效的唯一索引
DROP INDEX IF EXISTS uk_index ON my_table;

-- 创建基于CASE表达式的函数唯一索引
CREATE UNIQUE INDEX uk_index ON my_table (
  CASE WHEN accepted = 1 THEN nbr ELSE id END,
  CASE WHEN accepted = 1 THEN EXTRACT(YEAR FROM date_) ELSE id END
);

注意：语句中的id请替换为你表中的全局唯一主键字段（比如自增ID、UUID主键均可），保证accepted!=1的记录生成的索引键永远不重复，不会误触发唯一约束。

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