Access 2002: Linking table indices

[George Fowler]

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

 

[Tim Ferguson]

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

 

[Guest]

Thanks Tim. The perfect answer!
G

-----Original Message-----




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



.