MySQL 1062 - 键“PRIMARY"的重复条目“0"
我在 MySQL 5.5.24 版本中有下表
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;
它有很多数据与另外两个表具有多对一关系，ondelete=restrict 和 onupdate=restrict.
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 - 键PRIMARY"的重复条目0"
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?
通过查看数据，我发现新创建的列中的值为0".由于重复记录(在新的主键中)，这可能不允许更改主键
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)
我有超过 8,000 行，所以我无法手动更改.有没有办法将 rowid 分配给新的主键?
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?
推荐答案
在 mysql 控制台中运行以下查询:
Run the following query in the mysql console:
SHOW CREATE TABLE momento_distribution
检查看起来像的行
CONSTRAINT `momento_distribution_FK_1` FOREIGN KEY (`momento_id`) REFERENCES `momento` (`id`)
它可能会有所不同，我只是猜测它可能是什么.如果您在两个momento_id"上都有外键&'momento_idmember'，您将获得两个外键名称.下一步是删除外键.运行以下查询:
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
务必将外键名称更改为您从 CREATE TABLE 查询中获得的名称.现在您没有任何外键，因此您可以轻松删除 主键.请尝试以下操作:
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
如下添加所需的列:
ALTER TABLE `momento_distribution` ADD `id` INT( 11 ) NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST
此查询还会添加数字，因此您无需依赖@rowid.现在您需要将外键添加回前面的列.为此，首先创建这些索引:
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.
相关文章