duplicate null value violation on UNIQUE KEY constraint in Mssql

sql-server database null unique-constraint
Mo Haidar picture Mo Haidar · Aug 11, 2015 · Viewed 11.3k times · Source

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

GuidoG picture GuidoG · Aug 11, 2015

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;

Related questions

Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have …

Read More
How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
How to replace a string in a SQL Server Table Column

I have a table (SQL Sever) which references paths (UNC or otherwise), but now the path is going to change. In the path column, I have many records and I need to change just a portion of the path, but …

Read More