Primary Keys on Autonumbers

[winshent]

I've just been looking at how Access creates PK's...

I created a new table, and created a field 'ID' of type Autonumber.. I then clicked the PK button on this field and saved the table.

Now, when i go to the indexes on the table, there are two.. one is named 'ID' on field 'ID'.. the other is named 'PrimaryKey' on field 'ID'...

I am upsizing to SQL, and the SQL Server Migration Assistant raises this as a warning..

Can anyone explain why Access does this and if i need to worry about it?

Vince

 

[jdraw]

For an explanation of the difference between a Primary Key and an index
see http://itknowledgeexchange.techtarget.com/sql-server/difference-between-an-index-and-a-primary-key/

I'm not sure why Access does it that way, but to answer your question,
I don't think it's an issue.

 

[winshent]

Ok.. so in my example above, two indexes are created..

Index 1.....
Index Name: ID
Field Name: ID
Primary: No
Unique: No
Ignore Nulls: No

Index 2.....
Index Name: ID
Field Name: ID
Primary: Yes
Unique: Yes
Ignore Nulls: No

If I delete the index 'PrimaryKey' and set the following properties on index 'ID', whats the difference?

Primary: Yes
Unique: Yes
Ignore Nulls: No

 

[Galaxiom]

Access makes a new index regardless of those that already exist when you set the field as a PK in the table design view using the context menu. That command simply creates or removes the index entry that is marked as Primary against that field.

The Primary property setting in the Indexes window should be used when setting an already indexed field as the Primary Key.

Access has a lot of respect for indexes and does not change their settings or delete redundant indexes by itself. (Remember even when you delete a field with an index it warns you about deleting the index too.)

Indeed you can manually add as many indexes as you like on the same field. However you would definitely want to get rid of redundant ones as they create unnecessary processing and data.