Violation of UNIQUE KEY constraint on null

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.

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;

If you want a Unique Key that ignores Nulls it isn't possible in SQL Server. However this article describes how you can get that behavior via

• An indexed -- view with a where clause that removes nulls
• computed column -- Constraint is on a case statement that converts nulls into the PK
• a trigger -- the trigger does was a unique constraint does but ignores nulls
• normalization -- Put the data in another table where a row doesn't exist for Nulls

Additionally AlexKuznestov's answer notes that in SQL 2008 you can provide a filtered index.

The best of these is an indexed view but like a trigger its existence is not always apparent. The computed column is interesting but you would need to be able to guarantee that the actual data never clashes with the column you're "casing" on.

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

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.

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

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