Apr 21, 2012

Why does SQL Server allow only one NULL value on unique constraint?


Run the following code

declare @t table(i int unique)
insert into @t
select union all
select null union all
select null

You will get the following error

Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'UQ__#A989971__3BD019979D26A3BB'. Cannot insert duplicate key in object'dbo.@t'. The duplicate key value is (<null></null>).
The statement has been terminated.

But other RDBMSs like ORACLE, MySQL, etc will allow multiple NULL values on a column defined as Unique key. It is because that NULL values cannot be eaqual to each other
Why does SQL Server allow only one NULL value on a column that has unique constraint?

No comments:

Post a Comment

Hi,

Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.