MySQL 1062 - Duplicate entry 0 for key PRIMARY

Question

I have the following table in MySQL version 5 5 24  DROP TABLE IF EXISTS  momento distribution    CREATE TABLE IF NOT EXISTS  momento distribution            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   momento id    momento idmember         KEY  momento distribution FI 2    momento idmember         KEY  accepted    accepted    ext member       ENGINE InnoDB DEFAULT CHARSET latin1    It has lots of data with many-to-one relations with two other tables with ondelete restrict and onupdate restrict   Now  I need to change the structure and introduce separate primary key in the table  while still keeping existing relations and data  For that  I executed the following query   ALTER TABLE   momento distribution  ADD   id  INT  11   NOT NULL FIRST  ALTER TABLE   momento distribution  DROP PRIMARY KEY   ADD PRIMARY KEY     id       Unfortunately  my second query failed with the following error      1062 - Duplicate entry  0  for key  PRIMARY    Can someone please point out the issue  I guess that the issue is the existing relation  but I don t want to lose the existing relation or data  that has several thousand rows  Is there any way to do this without losing data   EDIT  By viewing data  I got that the newly created column has the  value  0  in it  Probably this is not allowing to change the Primary Key due to duplicate records  in new Primary Key   I have more than 8 000 rows  so I can t change it manually  Is there any way to assign rowid to a new Primary Key

User · Answer

You need to specify the primary key as auto-increment

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$$

With regards to comment below, how about:

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

A PRIMARY KEY is a unique index, so if it contains duplicates, you cannot assign the column to be unique index, so you may need to create a new column altogether

User · Answer

Run the following query in the mysql console   SHOW CREATE TABLE momento distribution   Check for the line that looks something like  CONSTRAINT  momento distribution FK 1  FOREIGN KEY   momento id   REFERENCES  momento    id     It may be different  I just put a guess as to what it could be  If you have a foreign key on both  momento id   amp   momento idmember   you will get two foreign key names  The next step is to delete the foreign keys  Run the following queries   ALTER TABLE momento distribution DROP FOREIGN KEY momento distribution FK 1 ALTER TABLE momento distribution DROP FOREIGN KEY momento distribution FK 2   Be sure to change the foreign key name to what you got from the CREATE TABLE query  Now you don t have any foreign keys so you can easily remove the primary key  Try the following   ALTER TABLE   momento distribution  DROP PRIMARY KEY   Add the required column as follows   ALTER TABLE   momento distribution  ADD   id  INT  11   NOT NULL  PRIMARY KEY AUTO INCREMENT FIRST   This query also adds numbers so you won t need to depend on  rowid  Now you need to add the foreign key back to the earlier columns  For that  first make these indexes   ALTER TABLE   momento distribution  ADD INDEX     momento id    ALTER TABLE   momento distribution  ADD INDEX     momento idmember      Now add the foreign keys  Change the Reference Table column as you need   ALTER TABLE   momento distribution  ADD FOREIGN KEY    momento id   REFERENCES   momento    id   ON DELETE RESTRICT ON UPDATE RESTRICT  ALTER TABLE   momento distribution  ADD FOREIGN KEY    momento idmember   REFERENCES   member    id   ON DELETE RESTRICT ON UPDATE RESTRICT    Hope that helps  If you get any errors  please edit the question with the structure of the reference tables  amp  the error code s  that you are getting

User · Answer

check if your field with the primary key is set to auto increment

User · Answer

this step work perfectly for me   you can try it   Add indexing Add Autoincrement Add Primary Key   hope it work for you too  good luck

User · Answer

For me  i forget to add AUTO INCREMENT to my primary field and inserted data without id

User · Answer

Set AUTO INCREMENT to PRIMARY KEY

User · Answer

Set your PRIMARY KEY as AUTO INCREMENT

[mysql] MySQL 1062 - Duplicate entry '0' for key 'PRIMARY'

Examples related to mysql

Examples related to sql

Examples related to mysql5