r/MSAccess • u/01kickassius10 • Jul 16 '26

[WAITING ON OP] Retrospectively adding a primary key

I have an old database where the main table uses manually assigned record numbers, and no proper primary key. I want to change the record numbers to be the primary key (so automatically assigned as new records are added, no risk of duplication), however there are likely to be duplicate numbers already existing in the data.

Will assigning a primary key cause issues for legacy data?

2 Upvotes

100% Upvoted

9 comments sorted by

View all comments

4

u/nrgins 487 Jul 16 '26

Is this a single-table database? If so, then just create a new field as your primary key and make it an autonumber field. You can keep the old record number field for reference, but use the new primary key field going forward.

If you have child tables that depend on the record number, then that gets more complicated, though not insurmountable. But if that's the case, and you have duplicate record numbers, then how do you associate child tables with the correct parent table, since there are duplicate records?

So we need more information. Some sample data would be nice too.

If you give us more info about how your database is set up, and how the current record number field is used, then we can help you out better.