Changing a Primary Key/Foreign Key by Adding an Autonumber to existing database

[Ranger7913]

I created an extensive database to track employees. I originally linked a unique employee ID assigned by the company as the primary key in the main employee table. All of the supporting tables, i.e, training, inspections, etc are linked by the employee ID as the foreign key in the supporting tables. I would like to change the main primary key to an auto number in the employee field. I assume I will need to add a field in the supporting tables to link the employee to the auto number. Am I on the right track? Is there an easier way to accomplish this. We have realized that we have a lot of data which needs to be entered prior to receiving the assigned employee number. As it stands now, we can not create a new employee file until we receive the assigned employee file number. Any suggestions will be greatly appreciated. I have about 700 employee records already in the database so I know it will be labor intensive.

[June7]

There is no easy way to accomplish changing PK/FK once data exists.

Add new field to each related table. Run UPDATE queries to populate new FK fields with the autonumber PK.

If you have established Relationships, will have to rebuild.

Will have to modify queries and any form/subform (and report/subreport) arrangements that use the old PK/FK association.

Once it all works, remove the old FK fields.

[John_G]

My suggestion -

First, define relationships in your database (Database Tools - Relationships) between the main employee table and all the others it is related to using employee number. For all these relationships, turn ON the Cascade Update option; this allows you to change the employee number in the main table, and have the change propagate automatically to all the others.

Then assign a temporary number (maybe a negative number - that's the way I do it) to each new employee record, and use that until you get a permanent one; all you need to do is change it once (you might need a little VBA if there are some instances of employee number that are not included in relationships).

MY opinion only, but you don't need an autonumber PK - you already have a PK. I know some sages say never change the value of a PK, but this is one time it would be perfectly OK.

[NTC]

No matter the approach, it is tricky and requires good database skills. Here is 1 approach to consider: add your new AutoNumber field to the Employee table. Leave the existing primary key field and all the dependencies on it through out the database.

then - set it up to copy/write the AutoNumber value into the existing primary key field; you'll need to use typically the AfterUpdate Event of a field in the form where you enter a new employee's data such as the Name field.

Everything then will work as it did before - but your key management task on this approach is to be sure that the auto number will never generate a duplicate of any existing primary key. If you are sure that it won't then you are golden. If it might - then you have to bump up the autonumber value to be above your highest primary key value....