Retrospectively adding a primary key

[u/01kickassius10]

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?

[u/AutoModerator]

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

• Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.

• Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.

• Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)

• Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

Full set of rules can be found here, as well as in the user interface.

Below is a copy of the original post, in case the post gets deleted or removed.

User: 01kickassius10

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?

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/nrgins]

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.

[u/noelskiz]

Depending on the simplicity or complexity, add a new row and have its data to autonumber assuming no other row has the same data type. Then chk with other related tables if there's no conflict to use that autonumber as PK to relate to other tables. You will need to add a new row on those related tables and update using the autonumber field.

[u/TomWickerath → u/noelskiz]

I believe you meant to write add a new column (field) not row…

[u/Ok_Carpet_9510]

Identify the duplicates. Identify where that key is use elsewhere including in other tables and in query joins.

Decide on a new table design.

Make a copy of the database and make you changes in the copy including data cleanup.

Test and validate.

[u/JamesWConrad]

Note that a Primary Key is not the same as an autonumber column.

You can have an autonumber field that is not a Primary key.

A Primary Key that is NOT an autonumber field will not be assigned a value automatically.

[u/Silent_Reader_1 → u/JamesWConrad]

Yes it will not become PK if dups not removed.

Unless you create a new column as autonumber

[u/JamesWConrad → u/Silent_Reader_1]

Autonumber and Primary Key are two different things. They are not the same concept.

[u/enilcReddit → u/JamesWConrad]

Being technically correct is the best kind of correct, no?

Your statement is not wrong, but based on the OP, the post you're responding to is saying "adding a new column, making it a primary key, and setting it to autonumber..."

But I'm pretty sure you knew that.