Established table, need to change primary key due to duplication, but want to keep the data in the autonumber field. (1 Viewer)

tucker61

Registered User.
Local time
Yesterday, 23:29
Joined
Jan 13, 2008
Messages
321
What is the best way to do this, i need to add a new primary key to stop some duplication i have noticed, so i have created a duplicate table, then thinking i need to clear out the data, set my primary key's, and then paste the old data back - excluding the duplicated files. - This is the bit i am stuck on as the autonumber field is currently the primary key and the number links to the child fields in other tables.

Do i just start again with a new autonumber and then run a query to update the job number on the child fields to the new master number ?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:29
Joined
Oct 29, 2018
Messages
21,358
Not sure you really need a new primary key. You should be able to also use a unique index to stop the duplicate records.
 

isladogs

MVP / VIP
Local time
Today, 06:29
Joined
Jan 14, 2017
Messages
18,186
Agreed.
First you need to remove existing duplicates
Make a backup copy then delete the duplicate records
Make sure you have referential integrity set with the cascade delete option so that any child table records are automatically deleted at the same time rather than being left behind as orphans.

Once that is done, use a composite index to prevent future duplicates.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:29
Joined
Feb 28, 2001
Messages
27,001
I have questions about the data layout.

1. If you are adding a primary key, what kind are you trying to add? Autonumber? Computed number using a DMax or DCount method? Or some sort of natural-data key based on something else already in the table?

2. Does any other table depend on this table (parent/child relationship, specifically)? Is there a relationship between this table as the independent entry and some other table as dependent data? If so, what would you expect as a ripple effect for this situation i.e. what would be the fix for the child / dependent table?

3. If you are stopping duplication, this implies that the field being duplicated was neither the PK nor was it even indexed/no-dups. So... when you try to add this key to prevent further duplication, what is going to happen to the already duplicated records? If they are still duplicates before you attempt to fix the duplication by imposing a key, you will not be able to do so because Access will stop you.

The issue is that if you try to fix this without a very clear end game, you will make a bigger mess. If we have answers to the above, we might be able to give you a better target and methodology.
 

Cotswold

Active member
Local time
Today, 06:29
Joined
Dec 31, 2020
Messages
521
If you are changing UniqueKeys then make sure you thoroughly test the changes on a copy database before the final run.
Changes to keys can have startling results if you haven't got everything right. Additionally, it can be a impossible returning
the database to its original state to start again. So do a few backups on the BE before you run it the final time on the latest
data. Test, test and test again. Particularly old or archived data that is hardly been looked at recently.

If you have used an Autonumber as a Unique or Primary key then things do not look good. If you can find a reason why
you don't need to do it, then don't. Then in the future never use the Autonumber for anything apart maybe to run an
odd filter on one record only. Never use them in your queries. Removing corruption removes records, and can leave you
with orphaned records. You can not replace a lost Autonumber.

If any issue arises next month or later, regarding the key changes, then you are probably looking at a major and disastrous
nightmare!
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:29
Joined
Feb 19, 2002
Messages
42,981
As several others have suggested, keep the autonumber as the PK and add a unique index to enforce your business rules. Keep using the autonumber as the FK.
 

Users who are viewing this thread

Top Bottom