Primary Key and Unique Key on a column

Question

Post reply

Primary Key and Unique Key on a column

kanthikumar.kota-671866

SSC Enthusiast

Points: 150
More actions
March 11, 2008 at 12:17 am

#379164

Can i have primary key and unique key defined on a single column of a table? If so what are its advantages or disadvantages?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

You don't need both... Primary Key is a unique index that doesn't allow nulls.

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kanthikumar.kota-671866 SSC Enthusiast Points: 150 More actions · Answer 2

Hi Jeff, Ya i dont need the both at a time. I think it wont give any error while defining a unique key on a column which is already a primary key for that table..So i want to know whether i will have any performance advantages in doing so??

Ian Yates SSCoach Points: 19738 More actions · Answer 3

There is absolutely no performance advantage to be gained from having a unique index on only your primary key column(s).

The only case where it could help is where you had additional columns in the table that you wanted to include in some sort of covering index if your clustered index was not the same as your primary key. Since the index would have the same columns as your pkey you know it is unique and thus could flag it as such. I'm not sure if this would perform better or worse than a non-unique index in the same circumstances.

The short answer is therefore that you shouldn't do it unless you have a specific indexing need to do with covering indices - in which case you'd probably know this already 🙂

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 4

kanthikumar.kota (3/11/2008)
Can i have primary key and unique key defined on a single column of a table? If so what are its advantages or disadvantages?

The short answer is that you cannot do this :):

Jeff and Ian are right about the reasons, and luckily SQL Server thinks the same way about it:

CREATE TABLE foo1

( a INT PRIMARY KEY

UNIQUE

, b INT )

GO

The result is:

Msg 8151, Level 16, State 0, Line 1

Both a PRIMARY KEY and UNIQUE constraint have been defined for column 'a', table 'foo1'. Only one is allowed.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software