Unique key problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Unique key problem

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-16 : 09:20:55

Hello everyone,

Why unique key allows only one null value.
Means why unique keys are having such characterstics.

Can anyone give me real example of actual use of Unique key.

Thanks

Vaibhav T

If I cant go back, I want to go fast...

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-16 : 09:29:07

Because then it wouldn't be unique.....

Primary keys.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-16 : 09:33:06

quote:
Originally posted by TimSman

Because then it wouldn't be unique.....

Primary keys.

I know that but my question is different.

If you are asked that
"As primary key can also uniquely identify the rows
then what is the use of unique key?"

then answer will be
"because unique key allows one null values and primary key does not".

So question is what is the actual use of allowing only one null value.

Vaibhav T

If I cant go back, I want to go fast...

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-16 : 15:14:21

>> So question is what is the actual use of allowing only one NULL value. <<

This has to do with the two types of equivalent operations in SQL. One is equality (=) and the other is grouping (GROUP BY, PARTITION BY, IS [NOT] DISTINCT FROM, etc).

Equality treats NULLs as distinct and not comparable. Grouping treats NULLs as identical.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-16 : 16:52:34

quote:
Originally posted by vaibhavktiwari83
[brSo question is what is the actual use of allowing only one null value.

I'm not sure if this answers your question, but if you have a column that is declared as NOT NULL, then obviously the Unique Constraint functions similarly to a PK. But, in some scenario a NULL or unknown value may be a valid. But, you still want to enforce uniqueniess, thus you are allowed to have a NULL value (assuming one column in the constraint). If you want to have unique values but allow multiple NULL values, then that's a whole different can of worms.

Sachin.Nand

2937 Posts

Posted - 2010-11-16 : 23:41:30

I to never understood this.

Just imagine a scenario in which I have a employee table with a columns EmployeeId and PassportNo.
Its not necessary that each Employee should necessarily have a Passport so the column PassportNo should be unique and can have multiple NULL values.

Instead of having a single unique index on PassportNo I ended up with having a unique index on both the coulmns Employeeid and PassportNo.

Also according to BOL "No two null values are equal".So I just wonder how does SQL decides on uniqueness for NULL values.

PBUH

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-17 : 02:52:18

Thanks for everyone for replying.
Thanks to sachin for Example which help me lot to understand actual use of unique key.
and you again created one more question

according to BOL "No two null values are equal".So I just wonder how does SQL decides on uniqueness for NULL value

Vaibhav T

If I cant go back, I want to go fast...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-17 : 11:26:10

quote:
Originally posted by Sachin.Nand

Instead of having a single unique index on PassportNo I ended up with having a unique index on both the coulmns Employeeid and PassportNo.

Unfortunatly, your unique index is kind of worthless as all that prevents is the same person from having two passports that are the same.

It would be nice if Micrsoft adhered to tha SQL ANSI standard and allowed mutiple NULLs. I assume this limitation is because they decided to implement this constraint with an index, but that is another issue.

There are several way to get around this limitation. One way to get around this is to use a computed column.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-17 : 11:27:52

quote:
Originally posted by Sachin.Nand

Also according to BOL "No two null values are equal".So I just wonder how does SQL decides on uniqueness for NULL values.

Read what Joe posted above. That is the crux of how SQL determines uniqueness.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources