SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can a table have two primary keys?


Can a table have two primary keys?

Author
Message
seshu67
seshu67
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 72
Can a table have two primary keys
Andy Hyslop
Andy Hyslop
SSC Eights!
SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)

Group: General Forum Members
Points: 981 Visits: 3037
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
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10141 Visits: 6367
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
When a question, really isn't a question - Jeff Smith
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


rajprabuit
rajprabuit
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 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.
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10141 Visits: 6367
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
When a question, really isn't a question - Jeff Smith
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


Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26305 Visits: 17553
Sounds like an interview or a homework question to me. :-D

_______________________________________________________________

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 Modens 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)
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4844 Visits: 72518
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. Smile



--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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87112 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sqlvogel
sqlvogel
Right there with Babe
Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)

Group: General Forum Members
Points: 798 Visits: 3706
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.
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7969 Visits: 7160
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search