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 , Nullyou 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 , 1256Null , 1256In the tableWould 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
|
| ThanksITM |
|
|
|
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