Access 2002: Linking table indices

  • Thread starter Thread starter George Fowler
  • Start date Start date
G

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
 
For example, do I need any index besides a Primary index in Table 3
below?

Table 1: Contacts
ContactID
Click to expand...
Table 2: Committees
CommitteeID
Click to expand...
Table 3: ContactCommittee
CntComID
ContactID
CommitteeID
Click to expand...

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
 
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



.
Click to expand...
 
Back
Top