Mssql中对UNIQUE KEY约束重复空值冲突 [英] duplicate null value violation on UNIQUE KEY constraint in Mssql

问题描述

MS SQL Server不会忽略空值并将其视为对UNIQUE KEY约束的违反，但是我知道的是UNIQUE KEY与接受空值的主键不同。

MS SQL Server does not ignore the null value and considers it as violation for the UNIQUE KEY constraint but what I know is that the UNIQUE KEY differ from the primary key where it accepts the null value.

Violation of UNIQUE KEY constraint 'AK_UserName'. Cannot insert duplicate key in object 'dbo.users'. The duplicate key value is (<NULL>).
The statement has been terminated.

有人可以帮我解决这个问题吗？

Can anyone help me to solve this problem?

推荐答案

您可以创建一个忽略这样的空值的唯一索引

you can create a unique index that ignores null values like this

CREATE UNIQUE NONCLUSTERED INDEX idx_col1
ON dbo.MyTable(col1)
WHERE col1 IS NOT NULL;

这篇关于Mssql中对UNIQUE KEY约束重复空值冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！