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

Question

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

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' & '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 & the error code(s) that you are getting.

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

this step work perfectly for me.. you can try it

1. Add indexing
2. Add Autoincrement
3. Add Primary Key

hope it work for you too. good luck

Set your PRIMARY KEY as AUTO_INCREMENT.