Primary Key in a Linking Table

[Dkline]

I'm wrestling with a linking table as the centerpiece on a many-to-many.
Since every table should have a Primary Key, do I designate each Foreign Key
as a Primary Key?

 

[Jeff Boyce]

There's a couple approaches (or more!) to consider when you build a
"linking" (resolver, junction) table to resolve a many-to-many relationship.

If you bring in the primary key from each of your (?two) tables as foreign
keys in your resolver table, you could use those two fields together as a
multi-column primary key.

If you are going to refer to the resolver table row in subsequent tables,
you might find it easier to create an autonumber primary key, and a unique
index on the two foreign keys.

 

[Sirocco]

Absolutely not. Each foreign key doesn't have to be a primary key - only if
the field is unique can it be a primary key - but it DEFINITELY should be
indexed. In a 1 to many relationship, the foreign key on the many side
CAN'T be primary, since there will be dupes - but it should certainly be
indexed. On the same note, the foreign key in a subform should
definitely be indexed - loading of the main form is much faster. ALL
FOREIGN KEYS SHOULD BE INDEXED.

 

[John Vinson]

ALL FOREIGN KEYS SHOULD BE INDEXED.

absolutely... just note that when you create a relationship in the
Access relationships window, just such an index is created
automatically. It's not necessary (and in fact just clogs up the
database) to manually create such an index yourself.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps