Violation of UNIQUE KEY constraint on null

Question

Exception:

Violation of UNIQUE KEY constraint 'UQ__user_dat__AB6E61641273C1CD'. Cannot insert duplicate key in object 'dbo.user_data'.
The statement has been terminated.

AB6E61641273C1CD = email which is unique and NULL

My SQL statement does NOT insert a value for email. So i assume it will have the default as null (although i never wrote a default(null) statement when creating table).

What is the problem? The code worked in sqlite perfectly.

Answer 1

a Unique Constraint only allows 1 null value in SQL Server, Oracle for example allows many NULLS

example

create table test (id int)


CREATE UNIQUE NONCLUSTERED INDEX [IX_test] ON [dbo].[test] 


insert test values(NULL)

--will fail
insert test values(NULL)

Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'dbo.test' with unique index 'IX_test'. The statement has been terminated.

Answer 2

You probably already have a row with null in the email column.

Answer 3

SQL Menace is 100% correct if you put a unique key on a column but allow nulls only one null is allowed in that column for all the rows in the table.

Example:

Column with unique key
1
2
3
NULL
4

To keep the column unique I can enter anything but 1,2,3,4,or NULL

Answer 4

On SQL Server 2008 you can use a filtered index instead. SQL Server 2005, use an indexed view.

Answer 5

If you're running SQL 2008 you can use a filtered index as suggested here.

create unique nonclustered index idx on dbo.DimCustomer(emailAddress)
where EmailAddress is not null;

Answer 6

If you want a Unique Key that ignores Nulls it isn't possible in SQL Server. However this article http://www.sql-server-performance.com/faq/access_ignore_null_p1.aspx describes how you can get that behavior through via indexed views, computed columns, a trigger or normalization.