Why Am I Getting MySQL Error 1062: Duplicate Entry for a New Primary Key?

MySQL Error 1062: Duplicate Entry for New Primary Key

When altering a MySQL table to change its primary key, it's possible to encounter error code 1062: "Duplicate entry '0' for key 'PRIMARY.'" This occurs when the newly designated primary key column contains duplicate values.

Cause:

The issue arises because the existing table data may have duplicate values in the column that is being converted into the primary key. The PRIMARY KEY constraint ensures that all values in the column are unique. When adding the PRIMARY KEY constraint, MySQL checks for duplicates and fails if they exist.

Solution:

To avoid data loss and address the error, follow these steps:

1. Add Auto-Increment to New Primary Key Column:

Ensure that the new primary key column has the AUTO_INCREMENT property:

CREATE TABLE `momento_distribution`
(
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `momento_id` INT(11) NOT NULL,
    `momento_idmember` INT(11) NOT NULL,
    `created_at` DATETIME DEFAULT NULL,
    `updated_at` DATETIME DEFAULT NULL,
    `unread` TINYINT(1) DEFAULT '1',
    `accepted` VARCHAR(10) NOT NULL DEFAULT 'pending',
    `ext_member` VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`id`, `momento_idmember`),
    KEY `momento_distribution_FI_2` (`momento_idmember`),
    KEY `accepted` (`accepted`, `ext_member`)
)
ENGINE=InnoDB
DEFAULT CHARSET=latin1;

By assigning the AUTO_INCREMENT property, MySQL will automatically generate unique values for the new primary key column, avoiding potential duplicates.

2. Change the Existing Column:

If you have already created the new primary key column without the AUTO_INCREMENT property, you can modify it using the following query:

ALTER TABLE `momento_distribution`
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`);

This query will alter the existing id column to have the AUTO_INCREMENT property, drop the old primary key constraint, and add a new primary key constraint on the modified id column.

The above is the detailed content of Why Am I Getting MySQL Error 1062: Duplicate Entry for a New Primary Key?. For more information, please follow other related articles on the PHP Chinese website!