Index question

  • Thread starter Thread starter Neil
  • Start date Start date
N

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.
 
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.
 
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.
 
Back
Top