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
|
|||
|
|||
Access 2002: Linking table indices
Hi,
MS Access 2002 automatically creates an index when a foreign key (ie the primary key of another table) is added. Is this necessary or (at least) in ones best interest in a linking table? For example, do I need any index besides a Primary index in Table 3 below? Table 1: Contacts ContactID First Last Table 2: Committees CommitteeID CommitteeName Table 3: ContactCommittee CntComID ContactID CommitteeID Thank you kindly, George |
#2
|
|||
|
|||
Access 2002: Linking table indices
"George Fowler" wrote in
: For example, do I need any index besides a Primary index in Table 3 below? Table 1: Contacts ContactID Table 2: Committees CommitteeID Table 3: ContactCommittee CntComID ContactID CommitteeID If the ContactCommittee.CntComID is an artificial key, then I would not even bother keeping the field. If you want uniqueness on (ContactID, CommitteeID) then you will need a Unique Index constraint on the pair of them (and I would make them the PK). You are right that you would not need separate indexes on the individual FK fields, since Jet will create them automatically. On the other hand, there is no particular penalty if you do, as jet only creates a new handle to the existing (automatic) index. TC is the local expert on how the Access team implements indexing. My understanding is that when you run a query, the Rushmore engine will make use of _any_ appropriate index whether it's automatic or user-requested, so again it doesn't matter. Clear as mud? :-) Tim F |
#3
|
|||
|
|||
Access 2002: Linking table indices
Thanks Tim. The perfect answer!
G -----Original Message----- "George Fowler" wrote in : For example, do I need any index besides a Primary index in Table 3 below? Table 1: Contacts ContactID Table 2: Committees CommitteeID Table 3: ContactCommittee CntComID ContactID CommitteeID If the ContactCommittee.CntComID is an artificial key, then I would not even bother keeping the field. If you want uniqueness on (ContactID, CommitteeID) then you will need a Unique Index constraint on the pair of them (and I would make them the PK). You are right that you would not need separate indexes on the individual FK fields, since Jet will create them automatically. On the other hand, there is no particular penalty if you do, as jet only creates a new handle to the existing (automatic) index. TC is the local expert on how the Access team implements indexing. My understanding is that when you run a query, the Rushmore engine will make use of _any_ appropriate index whether it's automatic or user-requested, so again it doesn't matter. Clear as mud? :-) Tim F . |
Thread Tools | |
Display Modes | |
|
|