Foreign Keys as Primary keys on related tables

Question

Foreign Keys as Primary keys on related tables

dfillis

SSC Rookie

Points: 25
More actions
December 17, 2003 at 1:52 am

#358778

Hi all
Could someone please comment on the design strategy of making use of related tables primary keys that become foreign keys in their child tables and also form part of the child table primary key. Please specify the pro's and con's of implementing this strategy.
Many thanks
Denzil Fillis

Viewing 8 posts - 1 through 7 (of 7 total)

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

NPeeters SSChampion Points: 12536 More actions · Answer 1

Sounds to me like a logical solution for typical one-to-many relationships.

From a technical point of view, you might consider placing a UNIQUE constraint on the child table (instead of the primary key) and using an identity value as primary key. This might improve performance if you have a multi-column primary key with large fields (eg. chars).

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

It might be that I don't understand your question correctly, but you're talking about one of the fundamental principles of relational databases.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

dfillis SSC Rookie Points: 25 More actions · Answer 3

quote:
Sounds to me like a logical solution for typical one-to-many relationships.
From a technical point of view, you might consider placing a UNIQUE constraint on the child table (instead of the primary key) and using an identity value as primary key. This might improve performance if you have a multi-column primary key with large fields (eg. chars).

Thanks for the reply. This what I thought, the use of foreign keys as part of the primary key in child table could lead to performance problems.

dfillis SSC Rookie Points: 25 More actions · Answer 4

quote:
It might be that I don't understand your question correctly, but you're talking about one of the fundamental principles of relational databases.
Frank
http://www.insidesql.de
http://www.familienzirkus.de

I think u are misunderstanding my question. Is it a good idea to use foreign keys as part of a primary key in a child table eg

Claim table has a composite primary key say ClaimId and Version. Then we have a child table say Doctor with foreign keys from Claim table, ClaimId and Version which together with DoctorId form the composite primary key of the Doctor table. Is this prescribed or is it better to use DoctorId which is unique identifier as the primary key.

Regards

Denzil Fillis

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Yes, you're right.

If I were aware that Noel already answered I wouldn't have posted at all.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Gil_Adi SSCommitted Points: 1669 More actions · Answer 6

Referntial Ontegrity Constraint Applied through Forigen Keys Keep You safe When you are trying to delete rows

But It can be very dangerous when you anble cascading delete.

Prefomarnce degregation are Imminent

Try implement referntial integrity in the applications and stored procedures.

NPeeters SSChampion Points: 12536 More actions · Answer 7

You should clearly make the distinction between a primary key and foreign key. They are in no way related and serve completely different goals ...

A primary key is a way to uniquely identify a record in your table. This can be a composite key, but it does not have to be.

A foreign key is a way to enforce data integrity across tables.

If you have a one-to-many relationship, it is common practice to use the data of the primary key in the parent table as the reference in the child table. After all, the primary key uniquely identifies the record in the parent table.

But you can use just as well any other field (or combination of fields), as long as you are sure it uniquely identifies the record.

In a many-to-many relationship (brrr, the horror of it), you would never use a primary key of the 'source' table as a foreign key of the 'destination' table...