Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

PRIMARY KEY VS UNIQUE KEY* Expand / Collapse
Author
Message
Posted Monday, July 29, 2013 3:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 12:05 PM
Points: 2, Visits: 75
Hi everyone,

Does anybody know any cases/example that we need to use UNIQUE KEY instead of PRIMARY KEY? I understand that UNIQUE KEY can allow one NULL value in the data, but I don't know the reason for that. Why do we need that extra NULL VALUE? And, When do we need it?

Thanks!
Kaz
Post #1478763
Posted Monday, July 29, 2013 5:07 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
Kazarf (7/29/2013)
Does anybody know any cases/example that we need to use UNIQUE KEY instead of PRIMARY KEY? I understand that UNIQUE KEY can allow one NULL value in the data, but I don't know the reason for that. Why do we need that extra NULL VALUE? And, When do we need it?


A Primary Key is a Referential Integrity constraint, that's why it should never allow a Null value.

On the other hand, an Unique Index is an index that doesn't allow for duplicate values but it is not a Referential Integrity constraint.

In my opinion, you do not "need" and extra Null Value - it is just allowed to be there even if I would never ever allow for Null Values on a Unique Index, it is not elegant and a symptom that something is wrong with the way data was modeled.


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1478771
Posted Monday, July 29, 2013 6:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 12:05 PM
Points: 2, Visits: 75
Thank You so much! ^^
Post #1478789
Posted Tuesday, July 30, 2013 3:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:47 AM
Points: 1,926, Visits: 2,352
Good one Paul


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1478890
Posted Tuesday, July 30, 2013 3:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 13,368, Visits: 11,148
You can also create multiple unique indexes on the same table, but you can have only one primary key.
A unique index can also be filtered, while a PK cannot.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1478895
Posted Tuesday, July 30, 2013 4:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:47 AM
Points: 1,926, Visits: 2,352
Koen Verbeeck (7/30/2013)
You can also create multiple unique indexes on the same table, but you can have only one primary key.
A unique index can also be filtered, while a PK cannot.


I am not clear with this statement_

A unique index can also be filtered, while a PK cannot



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1478899
Posted Tuesday, July 30, 2013 4:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
http://msdn.microsoft.com/en-us/library/cc280372%28v=sql.100%29.aspx


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1478903
Posted Tuesday, July 30, 2013 4:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:47 AM
Points: 1,926, Visits: 2,352

Thanks Gail for the useful link



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1478915
Posted Tuesday, July 30, 2013 5:01 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 1:36 AM
Points: 608, Visits: 1,050

On the other hand, an Unique Index is an index that doesn't allow for duplicate values but it is not a Referential Integrity constraint.

Paul,what exactly you mean by that..

As we can have referential integrity constraint column referring on unique key columns also...


Pramod
SQL Server DBA | MCSA SQL Server 2012

in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
Post #1478923
Posted Tuesday, July 30, 2013 5:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 13,368, Visits: 11,148
psingla (7/30/2013)

On the other hand, an Unique Index is an index that doesn't allow for duplicate values but it is not a Referential Integrity constraint.

Paul,what exactly you mean by that..

As we can have referential integrity constraint column referring on unique key columns also...


Yes, but then you are creating a foreign key on top of some columns who happen to be part of unique index. The index itself has nothing to do with referential integrity.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1478930
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse