| Author |
Topic |
|
Sachin.Nand
2937 Posts |
 Posted - 2010-06-03 : 05:49:20
|
Microsoft says one of the differences between a primary key constraint and a unique key constraint is that null values are allowed in a column with a unique constraint.But when you try to insert duplicate NULL values it shows up an error.So my question is are any two NULL values same?If yes then when you have a query like this Select * from table where NULL=NULL It doesnt return anything as the condition becomes falseIf NULL values are not the same then why does not a Unique constraint allow multiple NULL values.Cannot get my head around this.Any suggestions?PBUH |
|
|
vaibhavktiwari83
Aged Yak Warrior
843 Posts |
Posted - 2010-06-03 : 06:00:43
|
| Try this - Select * from table where NULL IS NULLIt returns all the rows or table.by default NULL is being used with IS operator instead of =Vaibhav TTo walk FAST walk ALONE To walk FAR walk TOGETHER |
 |
|
|
GopiMuluka
Starting Member
12 Posts |
Posted - 2010-06-03 : 06:03:27
|
| -- Below query returns all rows SET ANSI_NULLS OFFGOSelect * from table where NULL=NULLGO-- Below Query returns NO rowsSET ANSI_NULLS ONGOSelect * from table where NULL=NULLAlso From BOLIn a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an errorUnique Constraint is ANSI Compliant |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-06-03 : 06:25:04
|
| Hi Idera,I think you need to look into triple state logic. You obviously don't have a good understanding of the NULL concept.A NULL in SQL is a placeholder for either a missing piece of data, or a value that we cannot quantify.Becuase of this we cannot say whether a NULL value equals any other value (even another NULL value)EXAMPLE :What should the result of the statement Does NULL equal NULL be?This cannot be given as TRUE (because the two values those NULL's represent may be different). Equally it cannot be FALSE (because the items those nulls represent may be equivalent)Therefore we have to give the answer : NULL (unknown).For a really basic intro start here: http://en.wikipedia.org/wiki/Ternary_logic |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-03 : 06:52:36
|
Thx for all the replies.Its not about the understanding of how NULL values behave.My simple question is why does Unique Key not allow multiple NULL values.Why does it throws an error when you try to insert duplicate NULL values if it is a simple placeholder?Vaibhax thx for that query it totally skipped my mind to use Is Null part for comparision.TCYou saidquote:
This cannot be given as TRUE (because the two values those NULL's represent may be different). Equally it cannot be FALSE (because the items those nulls represent may be equivalent)
Then how does the Unique key goes about to decide that the NULL value already exists in the table and prevents inserting a new NULL value as a duplicate.PBUH |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-06-03 : 07:02:40
|
It's a design decision of the UNIQUE constraint. ( I believe )It allows 1 (and only) 1 instance of a NULL in the column(s) of the constraint.From BOL:Also, unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column. This is more of an implementation decision rather than a logical one (I think). Actually -- logically should a UNIQUE constraint even allow a NULL at all? there's no way to be sure that a NULL isn't equivalent to one of the other values in your column so.....Maybe someone more versed in sql internals would be able to tell you more but I think that all NULLS stored in the database may be pheysically stored as some same value (in binary) and the UNIQUE constraint may simply check that to enforce the unique-ness of the column(s).Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-03 : 07:29:15
|
| Well I dont see any advantage of allowing just one NULL value for a column?Let me give an example.If I have an employee table with PassportNo as a column and there might be some employees in my organization having a passport & some of them without.I define PassportNo as a unique key(as there should not be 2 passport no's of the same value).But what about the employees who do not have a Passport.I shoud store NULL values for them.But unique key prevents me having multiple NULL values for the passport column.Then how to go about this.I feel it is some kind of a bug.PBUH |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-06-03 : 07:36:58
|
| Edited because what I wrote was rubbishYou could write a custom check constraint if you wanted on that column. Just look up check constraints in BOL.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-03 : 08:02:22
|
quote:
Originally posted by Transact Charlie
Edited because what I wrote was rubbishYou could write a custom check constraint if you wanted on that column. Just look up check constraints in BOL.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Thats what I am saying.By defining the comparision between Unique Key and Primary Key as "Unique key allows NULL values and Primary Key does not" really does not make any sense.I wonder how to other SQL products like Oracle,Mysql,PostgreSQL handle this.Surely their behaviour has to be different.PBUH |
|
|
|
|
Unique Keys