CODEWITHSUNDEEP
sql-servert-sqlrdbms
Bhavesh Parmanand Mirani
Bhavesh Parmanand Mirani

Reputation: 11

can a multi column fk reference multiple tables?

I have 3 tables part, build, & buildpart buildpart is m:m. I was wondering if i could include the two columns of buildpart that are referencing part & build respectively in a single fk & make it clustered. I've been searching for about 4 hours now & I think this is not possible for some valid reason. Although I think MacConnel disagrees here http://c2.com/cgi/wiki?ForeignKeysCanOnlyReferenceOneTable.

Upvotes: 1

Views: 69

Answers (2)

nvogel
nvogel

Reputation: 25534

A foreign key can reference multiple tables but the values of that foreign key must then match values in both tables, not just either one. It doesn't seem to make any sense to combine the part and build into one column because that wouldn't represent your many to many relationship.

Upvotes: 1

Bill Gregg
Bill Gregg

Reputation: 7147

No, a foreign key constraint goes between the primary table and the foreign table. So you could create a clustered index on the primary table that covers both columns (part_id and build_id), then 1 foreign key from buildpart to build and one foreign key from buildpart to part.

HTH

Upvotes: 0

Related Questions