duplicate null value violation on UNIQUE KEY const

Question

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?

Answer 1

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;