Primary key vs. Indexed

[ump38]

Probably a dumb question, but....

Using Access version 7.0 (Office '95)

I have a field ("SSN_Sequence&quot in a table containing 40,000+ records which will eventually have 60,000+ records. This field contains both a 9-digit SSN plus an ascending sequence code (i.e., -1, -2, etc) for each like SSN. To further explain... if SS# "123456789" appears in the table twice, the first occurrence of this person's record would contain "123456789-1", the next "123456789-2".

This field when created was set up as: Indexed - "Yes (No Duplicates)". Later, it was also set as the primary key. When I look now at the indexes related to this table, there are actually 2 entries - one called PrimaryKey and the other called SSN_Sequence and the field name for both of these is obviously "SSN_Sequence".

So here's the question: Is Access actually maintaining 2 indexes for the same field? And would this be unadvisable? I'd think this is something I probably need to clean up and simply have set as Primary Key.

 

[Darrylles]

Hi Ump,

As far as I read it, any field defined as the key is automatically indexed.

You can also create an 'index' from any (and all) fields within a table that you choose (key or not) - if you intend to search on that field then create an index.

It seems from your explanation that an index had been set up in the past for both a field called 'Primary_Key' and also 'SSN_Sequence'.

These fields existed at some point, and indexes created for them (manually, or they were PK). If these fields no longer exist, then the indexes aren't needed.

Your main question is perdy techical - I think that the 2nd index is a 'legacy' thing - not cleaned up, or intentionally left there but useless.

Regards,

Darrylle
"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."