Mssql 中 UNIQUE KEY 约束上的重复空值冲突

duplicate null value violation on UNIQUE KEY constraint in Mssql(Mssql 中 UNIQUE KEY 约束上的重复空值冲突)

问题描述

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 约束上的重复空值冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！