Switching Primary Keys

<< Click to Display Table of Contents >>

Navigation:  Tables >

Switching Primary Keys

 
In this article I am going to show you how to switch the primary Key of a table from a two part real world primary key to the Autonumber field. The result will still maintain the real world key for management of your data.

 

Open the table in design view and display the design ribbon.

 

index1

 

Click on the Indexes button

 

index2

 

Have a look at your indexes, do not be surprised to find indexes that you don't need as per this article. Clean those up first. In this case I am going to remove the customerID index as it is replicated in the Primary key.

 

index3

 

Now I have rearranged the two keys that I am going to work on to the top of the key stack. Important to note here as I have shown in yellow that a dual part key is shown denoted with a blank second line in the column marked Index Name. If you click in the first field in the dual key you will see the 3 options at the bottom of the dialog box. We are going to adjust those.

 

index4

 

I have now changed the primary key to No and the index name to OldPrimaryKey. I have kept the Unique option of the key. Now close and save the table (you cannot add the new primary key yet)

 

index5

 

Open the table in design mode again, find the Index of the field that has the autonumber and change primary key of that to Yes.

 

index6

 

If you do not have a key at all for the Autonumber field, choose the autonumber field and then press the Primary key button at the top.

 

If you press the Primary Key button at the top, always review your indexes and that buttone creates a new index for the table and never cleans up any old ones that could be used for the same purpose. Repeated indexes bloat your database and slow down writing of records as the duplicate index has to be created and stored.

 

index7

 

This is what my adjusted indexes look like now. Save the table and look at it in design mode one more time to make sure that you have got it correct.

 

index8

 

 

See Also

 

Everything About AutoNumbers