Can a table have two primary keys? – SQLServerCentral Forums (2024)

More actions

August 7, 2012 at 2:47 am

#279921

More actions

August 7, 2012 at 2:51 am

#1522444

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

More actions

August 7, 2012 at 2:52 am

#1522445

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.

More actions

August 7, 2012 at 6:41 am

#1522537

More actions

August 7, 2012 at 7:28 am

#1522579

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.

More actions

August 7, 2012 at 9:15 am

#1522668

Sounds like an interview or a homework question to me. 😀

More actions

August 7, 2012 at 9:44 am

#1522685

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. 🙂

More actions

August 7, 2012 at 7:25 pm

#1522929

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.

More actions

August 12, 2013 at 6:39 am

#1640546

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.

More actions

August 12, 2013 at 12:31 pm

#1640669

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

More actions

August 12, 2013 at 11:08 pm

#1640785

Technically speaking and by definition, I absolutely agree. I think the OP was really asking if it were possible to have more than one non-null unique index on a table. Of course, the answer there is "Yes" and it's a fairly common practice when it comes to things like ISO standard lookup tables.