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»»

Can a table have two primary keys? Expand / Collapse
Author
Message
Posted Tuesday, August 07, 2012 2:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 18, 2013 4:56 AM
Points: 17, Visits: 69
Can a table have two primary keys
Post #1341071
Posted Tuesday, August 07, 2012 2:51 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 3:28 AM
Points: 672, Visits: 2,624
No

You can have a composite PK that consists of two (or more) keys but not two PK's



==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1341076
Posted Tuesday, August 07, 2012 2:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
Two individual primary keys - No

A primary key which spans multiple columns - Yes

What is it your trying to acheive?

Could get cleaver and put in a UNIQUE NONCLUSTERED index on the second column, just ensure that the column cannot accept nulls, and you get more or less the same result.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1341077
Posted Tuesday, August 07, 2012 6:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 07, 2012 6:48 AM
Points: 14, Visits: 20
No.You can't add 2 primary key in a table.Instead u can add Unique key which accept null value in it.
Post #1341198
Posted Tuesday, August 07, 2012 7:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
rajprabuit (8/7/2012)
No.You can't add 2 primary key in a table.Instead u can add Unique key which accept null value in it.


But if the OP wants it to act like a primary key, you would want to ensure that the column is not nullable as a PK won't accept a NULL value.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1341245
Posted Tuesday, August 07, 2012 9:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 11,949, Visits: 10,982
Sounds like an interview or a homework question to me.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1341346
Posted Tuesday, August 07, 2012 9:44 AM


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: 2 days ago @ 12:05 PM
Points: 3,566, Visits: 72,411
Could be a test question.

A decade ago when I taught Database Theory, my tests were take home week long projects.
25 T/F
25 Multiple Choice
25 Fill in the blank
25 point Essay question.

During each week long test, it was open book, open notes, open internet. If they had come here for help, I was fine with it. Real world, we get to use google. I didn't see the harm in it. Still don't. I think the most annoying thing about MCITP exams is the need to memorize a bunch of stuff I could look up in little to no time at all.

I seriously think the number of questions should be increased dramatically, but the internet should be availalbe. If you can get enough correct in 2 hours or whatever, then you get the cert. :)




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1341365
Posted Tuesday, August 07, 2012 7:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:21 PM
Points: 35,951, Visits: 30,235
seshu67 (8/7/2012)
Can a table have two primary keys


By absolute definition, no. That's why it's called a "Primary" key and there can only be one primary.

In all practicality, yes. You can create a UNIQUE constraint on a NOT NULL column and for all practical intent and purposes it works the same way as a Primary Key. It's what some folks call an "AK" or "Alternate Key".

If this is for an interview, be sure to mention that the PK does NOT have to be the clustered index. You can only have one clustered index.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1341647
Posted Monday, August 12, 2013 6:39 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:51 AM
Points: 429, Visits: 3,083
seshu67 (8/7/2012)
Can a table have two primary keys

In principle, yes. In practice it depends on what special functions or properties you want to assign to a primary key.

In the relational model a key is a minimal superkey - irreducibly unique and not permitting nulls. You can have more than one of those per relation. By convention when there is more than one such key then one of the keys is designated a "primary" one. But that primary designation doesn't make it "special" in any prescribed or fundamental respect. The choice and the difference (if any) between a primary key and a non-primary key is up to you.

Perhaps what you meant to ask is whether SQL's PRIMARY KEY constraint can be used more than once per table. The answer is no. It is an in-built limitation of SQL that PRIMARY KEY can only be used once per table. As Jeff says, you can use a UNIQUE constraint instead and that usually achieves the same things so the restriction on only having one PRIMARY KEY constraint per table isn't necessarily a serious limitation.


David
Post #1483266
Posted Monday, August 12, 2013 12:31 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:59 PM
Points: 1,734, Visits: 2,534
seshu67 (8/7/2012)
Can a table have two primary keys


Absolutely no, period. The fact that separate unique constraints can be defined does not make them primary keys as well.

Similarly, a table could have 999 unique constraints that didn't allow nulls but not have a primary key.


SQL DBA,SQL Server MVP('07, '08, '09)
I'm not fat, I'm gravity challenged.
Post #1483416
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse