SoFunction
Updated on 2025-03-04

MySQL primary key batch modification pit and solution

introduction

In daily development, we may encounter situations where we need to batch modify the primary key of the MySQL data table. At first glance, modifying the primary key ID may seem like a simple operation, but if it is not handled properly, you will encounter a "Duplicate entry … for key 'PRIMARY'" error, resulting in the operation failure or even data loss. This article will analyze the causes of the problem in detail and summarize a variety of safe and efficient solutions to help you easily deal with similar scenarios.

Problem phenomenon

Suppose there is the following data table category, where id is the primary key:

id | name
---|------
1  | A
2  | B
3  | C
4  | D

The goal is to set the primary keyidBulk modification to a new value, for example:

id | name
---|------
5  | A
6  | B
7  | C
8  | D

Execute the following SQL directly:

UPDATE category SET id = 5 WHERE id = 1;
UPDATE category SET id = 6 WHERE id = 2;
UPDATE category SET id = 7 WHERE id = 3;
UPDATE category SET id = 8 WHERE id = 4;

The following error occurred:

ERROR 1062: Duplicate entry '5' for key ''

Why is there an error?

MySQL is executing each itemUPDATEWhen  , check the primary key constraint immediately. whenid = 1Updated to5When, if the primary key value already exists in the table5, will trigger a conflict.

The primary key is the unique index, and operations that violate primary key constraints will be blocked by MySQL.

Solution

To avoid primary key conflicts, we can adopt the following solutions:

Solution 1: Use temporary values ​​to avoid conflicts

This method updates the primary key in two steps by introducing temporary values:

  • WillidModify to temporary value to ensure that it does not conflict with the target value.
  • Then change the temporary value to the target value.

SQL example:

-- first step：Will id Modify to a temporary value
UPDATE category SET id = id + 1000;

-- Step 2：Will临时值更新为目标值
UPDATE category SET id = id - 995; -- Assuming the goal id It's current id + 5

Operation process:

  • Initial data:
id | name
---|------
1  | A
2  | B
3  | C
4  | D
  • After the first step update:
id | name
---|------
1001 | A
1002 | B
1003 | C
1004 | D
  • After the second step update:
id | name
---|------
5  | A
6  | B
7  | C
8  | D

This method is suitable for any ID batch update scenario, simple and reliable.

Solution 2: Use intermediate tables to migrate data

If the update logic is more complicated, you can use the intermediate table to complete the primary key update to avoid conflicts caused by direct operations.

Operation steps:

  • Create a temporary table (intermediate table).
  • Import the original table data into the intermediate table and modify the primary key value when importing.
  • Delete the original table and rename the temporary table to the original table name.

SQL example:

-- first step：Create an intermediate table
CREATE TABLE category_temp LIKE category;

-- Step 2：Insert data into intermediate table，and modify the primary key value
INSERT INTO category_temp (id, name)
SELECT id + 5, name FROM category; -- Assume that the target value is current id + 5

-- Step 3：Delete the original table
DROP TABLE category;

-- Step 4：Rename the intermediate table to the original table name
RENAME TABLE category_temp TO category;

This method is particularly suitable for use in scenarios with large batch update volumes or complex business logic.

Scheme 3: Update the primary keys in order

If the target primary key has a small range, you can update in order to ensure that each update does not trigger a conflict.

Assume that the current ID range is1 ~ 4, the target ID range is5 ~ 8, can be updated in the following order:

  1. Updates starting at the maximum value to avoid conflicts with smaller IDs.
  2. Recover order after the update is completed.

SQL example:

-- Update in descending order，Avoid conflicts
UPDATE category SET id = id + 5 WHERE id = 4;
UPDATE category SET id = id + 5 WHERE id = 3;
UPDATE category SET id = id + 5 WHERE id = 2;
UPDATE category SET id = id + 5 WHERE id = 1;

-- If needed id Restore to a range，Perform an update again
UPDATE category SET id = id - 5;

Operation process:

  • Initial data:
id | name
---|------
1  | A
2  | B
3  | C
4  | D

  • Update process:

    • id = 4Updated to9；
    • id = 3Updated to8；
    • id = 2Updated to7；
    • id = 1Updated to6。

  • Final data:

id | name
---|------
5  | A
6  | B
7  | C
8  | D

Plan 4: Transaction rollback ensures security

Regardless of the method you choose, it is recommended to enable transaction management before performing an update to ensure that the operation can be rolled back if it fails.

SQL example:

-- Start a transaction
START TRANSACTION;

-- Update with temporary values
UPDATE category SET id = id + 1000;

-- Updated to target value
UPDATE category SET id = id - 995;

-- Submit transactions
COMMIT;

-- If there is an error in the middle，Can roll back
ROLLBACK;

Things to note

  • Backup data: Before modifying the primary key, be sure to back up the original data to avoid data loss due to operational errors.

mysqldump -u username -p database_name >

  • Foreign key dependency: If there is a foreign key constraint, when batch modifying the primary key, the relevant records in the foreign key table must be updated at the same time.

  • Target primary key uniqueness: Ensure that the target primary key value does not conflict with existing primary keys or intermediate values.

  • Performance optimization of large data volume: For million-level data tables, it is recommended to update batchwise to reduce the risk of locking tables. For example:

-- Every update 1000 strip
UPDATE category SET id = id + 1000 LIMIT 1000;

Summarize

In MySQL, the uniqueness and non-repeatability of primary keys determine that direct batch modification of primary keys may lead to conflicts. Depending on actual needs, the following solutions can be selected:

  • Use temporary values ​​to avoid conflicts: simple operation and strong applicability.
  • Migrate data with the help of intermediate tables: suitable for complex scenarios, safe and reliable.
  • Update primary keys in order: Suitable for batch updates with smaller ranges.
  • Transaction rollback ensures security: suitable for scenarios that cannot be interrupted before and after operations.

Through these methods, primary key modification can be completed efficiently and safely while avoiding common errors.

This is the end of this article about the pitfalls and solutions for batch modification of MySQL primary keys. For more related content on batch modification of MySQL primary keys, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!