Index question

[Neil]

Hello All,

I have noticed in my Database that my tables all have two index's for the
Primary Key field. For example if i have a table and the Primary Key field
is called lngDescriptionID. I have the following Index's:

Index Name Field Name Sort Order
lngDescriptionID lngDescriptionID Ascending
PrimaryKey lngDescriptionID Ascending

In another table the ID field could be lngMachineID and this would have the
following Index's:

Index Name Field Name Sort Order
lngMachineID lngMachineID Ascending
PrimaryKey lngMachineID Ascending

Does this mean that I have 2 index's here on the same field and that I could
delete the field index and keep the PrimaryKey index or do I need to keep
both? Bit confused here as to why i have 2 index's on the same field??

I am using WindowsXP SP1 and Access 2003 if it is relevant.


TIA,

Neil.

 

[Allen Browne]

Yes, delete the index with the field name, and just keep the PrimaryKey
index.

The reason you got the duplicated indexes, is your setting under:
Tools | Options | Tables/Queries | AutoIndex on Import/Create
Delete all the entries there, and Access will not add the spurious indexes.

When you create a relation between 2 tables in the Relationships window, and
you check the box for Relational Integrity, Access also creates a hidden
index on the foreign key field to manage the relation. That means that you
should not index your foreign key fields.

 

[Neil]

Hello Allen,

Thanks for the confirmation and the solution to this. Thought i was going
nuts for a minute there!

I also didnt know that Access creates a hidden index on the foreign key
fields which is handy to know. Oh well, go through all my tables again and
clear the foreign key index's now .

Thanks for your help!

Neil.