MySQL 1062 - 键“PRIMARY”的重复条目“0”

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

提问by Kapil Sharma

I have the following table in MySQL version 5.5.24

我在 MySQL 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=restrictand onupdate=restrict.

它有很多数据与其他两个表具有多对一关系，ondelete=restrict并且带有和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'

1062 - 键 'PRIMARY' 的重复条目 '0'

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)

编辑：通过查看数据，我发现新创建的列中的值为“0”。由于重复记录（在新的主键中），这可能不允许更改主键

I have more than 8,000 rows, so I can't change it manually. Is there any way to assign rowidto a new Primary Key?

我有超过 8,000 行，所以我无法手动更改它。有没有办法分配rowid给新的主键？

采纳答案by Kapil Sharma

Run the following query in the mysql console:

在 mysql 控制台中运行以下查询：

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 keyon 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:

它可能会有所不同，我只是猜测它可能是什么。如果您在“momento_id”和“momento_idmember”上都有外键，您将获得两个外键名称。下一步是删除外键。运行以下查询：

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 TABLEquery. Now you don't have any foreign keys so you can easily remove the primary key. Try the following:

请务必将外键名称更改为您从CREATE TABLE查询中获得的名称。现在您没有任何外键，因此您可以轻松删除主键。请尝试以下操作：

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:

此查询还会添加数字，因此您无需依赖 @rowid。现在您需要将外键添加回前面的列。为此，首先创建这些索引：

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.

希望有帮助。如果您遇到任何错误，请使用参考表的结构和您得到的错误代码编辑问题。

回答by Miroslav

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

PRIMARY KEY 是唯一索引，因此如果它包含重复项，则不能将该列指定为唯一索引，因此您可能需要完全创建一个新列

回答by RickPat

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

检查您的主键字段是否设置为自动递增

回答by Bill Tanthowi Jauhari

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

这一步非常适合我..你可以试试

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

1. 添加索引
2. 添加自动增量
3. 添加主键

hope it work for you too. good luck

希望它也适合你。祝你好运

回答by Faizan Khalid

Set your PRIMARY KEY as AUTO_INCREMENT.

将您的 PRIMARY KEY 设置为 AUTO_INCREMENT。

回答by Rohit Dubey

For me, i forget to add AUTO_INCREMENT to my primary field and inserted data without id.

对我来说，我忘记将 AUTO_INCREMENT 添加到我的主要字段并插入没有 id 的数据。

回答by WapShivam

Set AUTO_INCREMENTto PRIMARY KEY

将AUTO_INCREMENT设置为PRIMARY KEY