CODEWITHSUNDEEP
sql-serverdatabasenullunique-constraint
mohaidar
mohaidar

Reputation: 4251

duplicate null value violation on UNIQUE KEY constraint in Mssql

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?

Upvotes: 19

Views: 20125

Answers (1)

GuidoG
GuidoG

Reputation: 12014

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;

Upvotes: 39

Related Questions