SQL Server插入NULL值触发UNIQUE KEY约束重复键报错

SQL Server的普通唯一约束将NULL视为可重复判定的等值，同一唯一约束覆盖的字段下，全表仅允许存在1条NULL值记录，这和ANSI SQL中*NULL不等于任何值（包括另一个NULL）*的查询比较逻辑不一致。
你的dbo.Cliente表在nif字段上创建了普通非聚集唯一约束，当前表中已经存在id=2、nif为NULL的记录，新插入的记录未指定nif值、自动填充为NULL，直接触发唯一键冲突，报错信息中明确标注重复键值为(NULL)，和规则完全匹配。

现存表数据 #

触发报错的插入语句 #

INSERT INTO Cliente (id, nome)  VALUES ('5', N'Arnaldo Magalhães')

该语句未给nif字段传值，数据库会自动为该字段填充默认值NULL。

触发冲突的约束定义 #

从表的完整DDL可以看到冲突来源：

UNIQUE NONCLUSTERED 
(
    [nif] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

完整报错信息 #

Violation of UNIQUE KEY constraint 'UQ__Cliente__DF97D0F220E9258E'. Cannot insert duplicate key in object 'dbo.Cliente'. The duplicate key value is (NULL).
中文翻译：违反唯一键约束UQ__Cliente__DF97D0F220E9258E，无法在dbo.Cliente对象中插入重复键，重复键值为(NULL)。

解决方法 #

根据业务规则选对应方案即可：

• 若业务要求非空的nif值必须唯一，但允许存在多条nif为NULL的记录：删除现有普通唯一约束，改用筛选唯一索引，仅对非NULL的nif值做唯一性校验：

  -- 先删除原有冲突的唯一约束
  ALTER TABLE dbo.Cliente DROP CONSTRAINT UQ__Cliente__DF97D0F220E9258E;
  -- 创建筛选唯一索引，仅校验非空nif的唯一性
  CREATE UNIQUE NONCLUSTERED INDEX UX_Cliente_nif ON dbo.Cliente(nif) WHERE nif IS NOT NULL;
  

• 若业务要求nif字段全表唯一，不允许多条NULL记录存在：插入新记录时为nif字段分配不重复的非空有效值即可。

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