Primary keys: datafield v. autogen

[sueb]

This database I inherited has an automatically generated primary key, and also a datafield that is really the key to the data: it is unique, and the table is sorted by that field rather than by the primary key.

I'm itching to ditch the primary key and just assign the datafield as the primary key. Are there any pitfalls I should consider first?

[Stewart Ross]

If the table has any 1-many relationships with other tables you won't have values from your other candidate key in those tables. It is the current autonumber PK which will be carried forward as the secondary key into the many-side tables.

To resolve this you would have to add new fields to each many-side table to allow the proper key to be carried forward, then copy the corresponding key value into each record accordingly (keeping the existing key in place whilst you do so, to identify the correct rows for update).

Similarly, if there are any many-side subforms dependent on the current autonumber key you will need to alter the master-child link in the main form to refer to the new key field.

You won't be able to delete the current autonumber key field if it is participating in any relationships with other tables. If the existing relationship is properly defined in the Relationships window you will have to delete that relationship and set up a new one for the new key.

You will also have to be sure that there are no orphaned records which for one reason or another do not have appropriate values for the new candidate key. At present the new candidate key has no defined relationship with any other table, so Access will not have been able to protect relational integrity on the values concerned.

All in all, unless there are compelliing reasons to do away with the current autonumber version I'd leave this one alone and accept that it was not designed in entire accordance with relational best-practice!

-Stewart

[sueb]

Stewart, thank you for such a complete response. It gives me much to think about.

This database does not, currently, have any relationships based on the candidate new key. That, too, is one of the things I'm wanting to fix: the database is entirely flat, with fields that belong to the candidate key, but with three "sets" of fields repeated to contain data that really should be in a separate table (as though it were to keep only three Orders per Customer!). So perhaps, before I normalize the database, I might be able to make this change (there are also only three forms that currently access the data, although there should be more, and more functionally specific, ones).

I'll use your points as I analyze how to accomplish this. Thanks, again!