SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Use of Nulls in a composit key
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

itmasterw
Yak Posting Veteran

90 Posts

Posted - 08/22/2012 :  13:30:46  Show Profile  Reply with Quote
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

USA
15655 Posts

Posted - 08/22/2012 :  13:37:20  Show Profile  Visit robvolk's Homepage  Reply with Quote
If both columns are part of the key, then that example would still be unique, because different columns are null.
Go to Top of Page

itmasterw
Yak Posting Veteran

90 Posts

Posted - 08/22/2012 :  13:54:41  Show Profile  Reply with Quote
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
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 08/22/2012 :  13:56:09  Show Profile  Reply with Quote
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
Go to Top of Page

itmasterw
Yak Posting Veteran

90 Posts

Posted - 08/22/2012 :  14:28:14  Show Profile  Reply with Quote
Thanks



ITM
Go to Top of Page

dportas
Yak Posting Veteran

United Kingdom
53 Posts

Posted - 09/03/2012 :  14:50:53  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000