如何创建支持指定列仅单个NULL值的多列唯一约束

需求说明 #

需要在col3、col4列上设置唯一性约束，特殊校验规则为：col4列全局最多仅允许出现1个NULL值，非NULL值的组合唯一性保持常规校验逻辑。

普通的数据库唯一约束无法满足该需求，根据SQL标准，NULL值之间不做相等比较，常规唯一索引会默认允许多个NULL值共存，无法限制NULL的数量。
注：该实现方式依赖数据库的部分索引能力（即允许创建带过滤条件的索引），适用于PostgreSQL、SQLite等支持该特性的数据库；如果使用不支持部分索引的数据库（如MySQL），可通过创建虚拟生成列的方式实现等价的拆分校验逻辑，核心思路保持一致。

实现思路 #

拆分两类唯一性校验场景，通过两个独立的部分唯一索引实现全部规则：

• 针对col4为非NULL的场景，创建覆盖col3、col4的唯一索引，保证非NULL场景下的组合唯一性
• 针对col4为NULL的场景，创建全局唯一的约束，强制仅能存在1条col4为NULL的记录

具体索引创建语句 #

1. 非NULL值场景的唯一索引

   CREATE UNIQUE INDEX idx_col3_col4_notnull ON 你的表名(col3, col4) WHERE col4 IS NOT NULL;
   

   该索引只会收录col4非空的行，只要存在重复的(col3, col4)非空组合，就会触发唯一冲突报错。
2. 单个NULL值的全局约束索引

   CREATE UNIQUE INDEX idx_col4_single_null ON 你的表名((1)) WHERE col4 IS NULL;
   

   该索引的原理是：所有满足col4 IS NULL的行，对应的索引键值都是固定常量1，由于唯一索引不允许重复键值存在，因此整个表最多只能收录1条col4为NULL的记录，刚好匹配“仅允许1个NULL”的要求。

规则匹配验证 #

• 可正常插入的合法数据（原示例中列头重复标注为col4属于笔误，实际为相邻其他列）：

  col1	col2	col3	col4	笔误列值
  1	A1	S1	V1	P1
  2	A1	S1	V1	NULL

  以上数据可以正常插入：第一行的col4非空，对应的索引键无重复；第二行的col4为NULL，全局仅存在1条NULL记录，不会触发索引冲突。
• 会被拒绝的非法数据：

  col1	col2	col3	col4	笔误列值
  3	A1	S1	V1	NULL
  3	A1	S1	V1	P1

  冲突触发逻辑：
  • 如果新增第二条col4为NULL的记录，idx_col4_single_null索引会检测到重复的固定键值1，直接抛出唯一约束错误
  • 如果新增的非空(col3, col4)组合和已有记录重复，idx_col3_col4_notnull索引会抛出唯一约束错误

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