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.
| Author |
Topic |
|
itmasterw
Yak Posting Veteran
90 Posts |
 Posted - 2012-08-22 : 13:30:46
|
Hi,
Can someone tell me please if I am right here. If you are looking to have a unique key, and you take two columns to make a unique key, but one of th ecolumns have nulls in it; then it is not a unique key.
Example:
ID number, Reference number
Null , 1256
10456 , Null
you could not say this is not a unique could you?
Thank you
ITM |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-08-22 : 13:37:20
|
| If both columns are part of the key, then that example would still be unique, because different columns are null. |
 |
|
|
itmasterw
Yak Posting Veteran
90 Posts |
Posted - 2012-08-22 : 13:54:41
|
Thanks for getting back to me so fast.
What if, as I am see a number occrences of, there are multiple cases of this:
Null , 1256
Null , 1256
Null , 1256
In the table
Would this still be unique?
ITM |
|
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-08-22 : 13:56:09
|
This is of it like this:
- unique key = unique index +null (possible)
- primary key = unique index + not null constraint
--------------------------
Joins are what RDBMS's do for a living |
|
|
|
itmasterw
Yak Posting Veteran
90 Posts |
Posted - 2012-08-22 : 14:28:14
|
Thanks
ITM |
|
|
|
dportas
Yak Posting Veteran
53 Posts |
Posted - 2012-09-03 : 14:50:53
|
The set of columns in a UNIQUE constraint isn't necessarily a key. Keys by definition don't permit nulls and so a column that permits nulls can't be part of any key.
SQL Server's UNIQUE constraint behaves differently from UNIQUE constraints in standard SQL. In ISO Standard SQL, a UNIQUE constraint actually permits duplicate rows if any of its columns includes a null. SQL Server UNIQUE constraints do not permit duplicate rows - nulls are instead treated as equal values for the purpose of evaluating whether the constraint is violated or not.
I highly recommend you avoid nulls in UNIQUE constraints. You can always redesign it without the nulls by creating the constraint on a new table and then only populating that table with the non-nullable values. |
|
|
|
|
|
|
Use of Nulls in a composit key