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 |
|
prodigy2006
Yak Posting Veteran
66 Posts |
 Posted - 2009-07-14 : 11:03:42
|
| Hi gurus,This question has been troubling me for quite a few days-When we have primary key in a table then why do we need a unique key?Thanks,Sunny. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-14 : 11:32:51
|
I will try an answer but it is hard for me with my poor english  because I am thinking and drinking in German while posting in English (greetings to Don!)  A primary key is used to identify a row in a table and there is no reason that this column has a relation to the data of its row other than being an distinct identifier for this row.An good example is an auto number (identity-column) that is only used like I said.A unique key can be 1 or more columns in combination and it is with much more relation to the data of its the row.An example uk can be LastName+FirstName+Birthdate.Maybe there comes up any better explanation like often when I have done my post
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-07-14 : 11:59:34
|
| I'd suggest that you read up on relational thoery to help better understand the difference and why they are important.The primary difference is that a table can have only one Primay Key and that does not allow nulls. Where as a table can have multiple Unique Constraints and they will allow null values, as long as the combination of columns in unique.Here some stuff from BOL:A table has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key. Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table.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 (combination). |
|
|
|
|
|
|
|
|
Primary key vs Unique key