Primary Key and Foreign key on the same column

Question

Primary Key and Foreign key on the same column

lianvh 89542

SSCommitted

Points: 1561
More actions
September 6, 2013 at 2:44 am

#282149

Good Day
I did some investigation on the database system I inherited (SQL Server 2008 R2 with 2005 Compatibility Mode) and found that the Primary Key and Foreign key both reffence the same column in one table . Could that cause unnecessary overhead on the system ? Is it a valid solution ? Is it common practice ? Any ideas ?
Thanks .
Lian

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

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

pietlinden SSC Guru Points: 63434 More actions · Answer 1

Could you post at least a partial table definition? It's hard to tell without an example.... so throw in a few insert statements too... enough so we know what you're talking about and can play around with it. Don't need more than a handful of records, though.

lianvh 89542 SSCommitted Points: 1561 More actions · Answer 2

lianvh 89542

SSCommitted

Points: 1561

September 6, 2013 at 3:02 am

#1647955

Unfortunately I cannot do that due to NDA .

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

Are both defined on just one column?

In that case I would question the design, but there is a possibility that the table was split up into two tables because of rowlength restricitons or for some other reason.

[font="Verdana"]Markus Bohse[/font]

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 4

lianvh 89542 (9/6/2013)
Unfortunately I cannot do that due to NDA .

You can always make up a table with sample data with the same structure as the actual table.

You need to help us before we can help you.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Grant Fritchey SSC Guru Points: 398696 More actions · Answer 5

I do stuff like that all the time. Let's say we're building a system for storing personal information. You have a table to define the person

PersonID FirstName LastName

With the primary key on the PersonID. We also have a table called addresses because for a given person they can have more than one address associated with them and because more than one person can be associated with an address.

AddressID Address

Then, to associate the person to the address we do this

PersonID AddressID

Both columns have a foreign key constraint back to their parent table and both are part of a compound primary key.

Yes, there is some overhead here, as there is with any foreign key, but it's not bad and won't cause any undue headaches or problems.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning