MySQL 1062 - key 'PRIMARY'重复输入'0'

I have the following table in MySQL version 5.5.24

我在MySQL version 5.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.

它与另外两个表具有多对一关系，具有ondelete= limit和onupdate= limit。

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 -重复输入“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 rowid to a new Primary Key?

我有超过8000行，所以我不能手动更改。有没有办法将rowid分配给一个新的主键?

4 个解决方案

#1

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

它可能是不同的，我只是猜测它可能是什么。如果在'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 TABLE query. 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.

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

#2

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

主键是唯一索引，因此如果它包含重复的索引，则不能将列赋为唯一索引，因此可能需要创建一个新的列

#3

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

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

#4

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

希望它对你也有用。祝你好运

#1