Primary Key and Other Indexes

  • Thread starter Thread starter tbl
  • Start date Start date
T

tbl

When a new, simple table is created in Access, with a
typical Primary Key assigned, I notice that two Indexes are
listed in the "Indexes" listing dialog. One is for the
Primary Key, and the other is for the same field, but not
inicating anything about a Primary Key.

Are both required? Beneficial?
 
When a new, simple table is created in Access, with a
typical Primary Key assigned, I notice that two Indexes are
listed in the "Indexes" listing dialog. One is for the
Primary Key, and the other is for the same field, but not
inicating anything about a Primary Key.

Are both required? Beneficial?
Click to expand...

No, and no. Access has a somewhat infuriating misfeature: if you
select Tools... Options... Tables/Queries, you'll see a textbox
labeled "Auto Index on import/create". This will automatically index
fieldnames ending in the listed text strings (ID, Num, ...) - EVEN IF
an index already exists for that field.

I'd suggest clearing this textbox in all databases. If I want an index
- I'll create an index!

John W. Vinson[MVP]
 
Correction: The duplicate index gets created based on a setting in
Tools>Options>Tables/Queries - see the AutoIndex option. If there are entries
there, and your intended key field matches one of them (and it doesn't have
to be an exact match!), two indexes may get created: non-primary as a result
of the "Autoindex on create" option, and primary as a result of clicking Yes
on the primary key dialog. My preference is to turn off autoindexing and
manage indexes by hand.
Click to expand...

Thank you Ted!
 
No, and no. Access has a somewhat infuriating misfeature: if you
select Tools... Options... Tables/Queries, you'll see a textbox
labeled "Auto Index on import/create". This will automatically index
fieldnames ending in the listed text strings (ID, Num, ...) - EVEN IF
an index already exists for that field.

I'd suggest clearing this textbox in all databases. If I want an index
- I'll create an index!

John W. Vinson[MVP]
Click to expand...


Thanks John! Good stuff.
 
Back
Top