If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Primary Key in a Linking Table
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? |
#2
|
|||
|
|||
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. -- Good luck Jeff Boyce Access MVP "Dkline" wrote in message ... 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? |
#3
|
|||
|
|||
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. "Dkline" wrote in message ... 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? |
#4
|
|||
|
|||
On Fri, 22 Oct 2004 19:48:33 -0400, "Sirocco"
wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Design for multiple Unions | Dkline | Database Design | 6 | October 21st, 2004 02:20 PM |
Are three primary keys less effecient than two? | Dale | Database Design | 4 | October 5th, 2004 05:33 AM |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |
Keeping Records in Linking Table Accurate (Long Post) | Ben Johnson | Running & Setting Up Queries | 0 | June 14th, 2004 08:13 AM |