How to Fix MySQL Error 1062: Duplicate Entry '0' for Primary Key?

Troubleshooting MySQL Error: Duplicate Entry '0' for Primary Key

You have encountered an error when attempting to modify the primary key of the momento_distribution table in MySQL. The error message "1062 - Duplicate entry '0' for key 'PRIMARY'" indicates that the new primary key column contains duplicate values.

Analysis

Upon examination, you discovered that the newly created id column has the value '0' in all rows. This conflict prevents the assignment of a unique primary key due to the presence of duplicate records.

Solution

To resolve this issue, you need to ensure that the primary key column contains unique values. The most common approach is to make the column auto-increment, which will automatically generate unique values for each new row.

Modify Table Creation:

CREATE TABLE `momento_distribution`
  (
     `momento_id`       INT(11) NOT NULL AUTO_INCREMENT,
     `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 (`momento_id`, `momento_idmember`),
     KEY `momento_distribution_FI_2` (`momento_idmember`),
     KEY `accepted` (`accepted`, `ext_member`)
  )
ENGINE=InnoDB
DEFAULT CHARSET=latin1$$

Modify Existing Table:

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

Note: This assumes that the id column already exists. If it does not, you will need to create it using the ALTER statement.

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