2

For a Bridge Table I have the PK from 2 other tables. What are the pros and cons of making a PK field for the bridge table or making a composite/compound between the two fields.

I want to make sure I am following best practices.

Some Links I am reading over:

https://dba.stackexchange.com/questions/3134/in-sql-is-it-composite-or-compound-keys

http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/9d3cfd17-e596-4411-b3d8-66e0ec8bfdc7/

http://www.ben-morris.com/identity-surrogate-vs-composite-keys-in-sql-server

Composite primary keys versus unique object ID field

Community
  • 1
  • 1
KeyboardFriendly
  • 1,798
  • 5
  • 32
  • 42

1 Answers1

9

You have to enforce a unique constraint of some kind on the two foreign keys. The easiest way to do that is with a primary key constraint.

An additional, surrogate ID number isn't really useful. Some people use it because it makes foreign key constraints and joins to the "bridge" table easier to write. I think that, if you think it's hard to make a join using two integers, you shouldn't be working with databases in the first place.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185