What is the advantages of only one null for unique constraint is SQL Server?
A:
Sorry, what do you mean?
If it's a unique constraint you can't have null twice, obviously?
Noon Silk
2009-08-15 13:39:21
That may not be obvious, since one of the meanings of null is "unknown". Does one unknown = another? Not in most uses of null in SQL! In other words (null = null) is false.
automatic
2009-08-15 13:45:48
Yes, well then one could argue that SQL is wrong in it's enforcement of the unique constraint (and I would). Though I do happen to that that null = null being false is ridiculous. But my feelings on it won't change it :)
Noon Silk
2009-08-15 13:53:41
Technically (null = null) is NULL, which is interpreted as false.
Ted Elliott
2009-08-15 14:07:08
+1
A:
It is an advantage if you consider NULL to be a value that needs to be unique like other values, however most developers consider NULL to be the absence of a value and thus 2 NULLs are not equal and in this case this is a limitation that needs to be circumvented in SQL(unlike other DBMSs)
Diaa Sami
2009-08-15 13:42:56
I wouldn't say 'most developers' consider that, I'd say 'SQL Server' thinks that. And personally I think it's a big, and very annoying, mistake. null is clearly null and null = null in my book. I have never ever need a value that says 'and make sure this never equals anything else'. But I digress. This is an argument worthy of it's on thread.
Noon Silk
2009-08-15 13:45:45
For me, this particular MSSQL 'feature' is usually a limitation so I have to work around it using one of the known methods, the view method is usually the cleanest.
Diaa Sami
2009-08-15 15:08:19