Change Primary Key Type

[r. howell]

OK. I am belatedly convinced of the advantages of an autonumber primary key,
that it is smart to have a dumb primary key.

How do I do this after the fact? I have been using a combination of fields
as a primary key, and there are several hundred records in the system. (at
least it's not several thousand).

Can I now assign an autonumber primary key? How do I get numbers onto all
the records that are already there? What other things am I going to mess up
while I straighten this out?

Any and all tips will be deeply appreciated.

Thanks for your help

 

[Jeff Boyce]

What happens if you:

make a backup copy of your db
open the table in design mode
remove the primary key index (NOT the fields!)
add an autonumber field
set that new field as the primary key

By the way, if you have ANY related tables that have "inherited" your
current composite (multi-column) primary key as a foreign key, you will need
to add a new foreign key field (type = LongInt) to those tables and update
that field, based on the corresponding "main" table's new primary key.