MySQL Error 1062: How to Fix Duplicate Primary Key Entries Without Data Loss?

MySQL 1062 Error: Duplicate Entry for PRIMARY Key, How to Resolve Without Losing Data?

Problem:

When attempting to modify the primary key on a MySQL table with existing data and relationships, an error is encountered:

1062 - Duplicate entry '0' for key 'PRIMARY'

The issue arises because a newly added column intended as the primary key contains duplicate values (0), violating the unique constraint.

Root Cause:

This error typically occurs when you have existing data with foreign key relationships referencing the column being modified as the primary key. If the values in the primary key are not unique, the database will reject the change.

Resolution (Without Data Loss):

To resolve this issue without losing data, you can follow these steps:

1. Set Primary Key as Auto-Increment:

   In the table definition, specify the new primary key column as auto-increment:

   `momento_id` INT(11) NOT NULL AUTO_INCREMENT,

   This will automatically generate unique values for the new primary key.

2. Drop Old Primary Key:

   Remove the existing primary key constraint:

   ALTER TABLE `momento_distribution` DROP PRIMARY KEY;

3. Add New Primary Key:

   Add a new primary key constraint on the auto-increment column:

   ALTER TABLE `momento_distribution` ADD PRIMARY KEY (`momento_id`);

This will assign the auto-generated values as unique primary keys without affecting the existing data.

Alternative Approach:

If the "momento_id" column does not need to start from 0, you can consider an alternative approach:

1. Create a New Auto-Increment Column:

   Add a new column with auto-increment enabled:

   ALTER TABLE `momento_distribution` ADD `new_id` INT(11) NOT NULL AUTO_INCREMENT;

2. Update Existing Data:

   Update the existing rows to populate the new column:

   UPDATE `momento_distribution` SET `new_id` = `momento_id`;

3. Drop Old Primary Key and Add New Primary Key:

   Drop the old primary key and add a new primary key on the auto-increment column:

   ALTER TABLE `momento_distribution`
   DROP PRIMARY KEY,
   ADD PRIMARY KEY (`new_id`);

This approach assigns unique auto-increment values to all rows, ensuring the primary key constraint is met.

The above is the detailed content of MySQL Error 1062: How to Fix Duplicate Primary Key Entries Without Data Loss?. For more information, please follow other related articles on the PHP Chinese website!