question

Naveen Kumar avatar image
Naveen Kumar asked

NULLs and unique key

Hi Team, I know two null values are not the same in SQL Server and a null value is undefined. If so, why is it that unique allows only one null value? As two nulls are not same why won't unique allow a second null? Might be silly one, but I want to know the reason / proper explanation :) Thanks in advance, Naveen.
sql-server-2008-r2nullunique
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image GPO commented ·
Do you mean "unique constraint"?
0 Likes 0 ·

1 Answer

·
KenJ avatar image
KenJ answered
There isn't really a proper explanation other than "that's the way they originally implemented it and they don't want to break applications that now depend upon that behaviour." There is several years' worth of lively debate on just this topic over on connect (apparently, allowing multiple nulls would be compliant with the various SQL standards) - [ https://connect.microsoft.com/SQLServer/feedback/details/299229/change-unique-constraint-to-allow-multiple-null-values][1] It also turns out that two null values *can* be the same in SQL Server; more of that non-ansi compliance trouble... If you set ansi_nulls off, null values can be compared using the = operator - [ http://msdn.microsoft.com/en-us/library/ms188048.aspx][2] As long as SQL Server unique constraints don't have a strict ansi implementation, they need to limit the number of null values so the constraint behaves the same regardless of ansi settings. [1]: https://connect.microsoft.com/SQLServer/feedback/details/299229/change-unique-constraint-to-allow-multiple-null-values [2]: http://msdn.microsoft.com/en-us/library/ms188048.aspx
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image Scot Hauder commented ·
Behaviour? I thought you were from the States...
0 Likes 0 ·
Naveen Kumar avatar image Naveen Kumar commented ·
Thanks for comment Kenj.
0 Likes 0 ·
KenJ avatar image KenJ commented ·
@Scot Hauder - other members' spellings are starting to rub off :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.