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

"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

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



.