MySQL (InnoDB)：需要删除列，并附带外键约束和索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/338978/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL (InnoDB): need to delete column, and accompanying foreign key constraint and index
提问by nmjk
Here's my table:
这是我的表：
CREATE TABLE `alums_alumphoto` (
`id` int(11) NOT NULL auto_increment,
`alum_id` int(11) NOT NULL,
`photo_id` int(11) default NULL,
`media_id` int(11) default NULL,
`updated` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `alums_alumphoto_alum_id` (`alum_id`),
KEY `alums_alumphoto_photo_id` (`photo_id`),
KEY `alums_alumphoto_media_id` (`media_id`),
CONSTRAINT `alums_alumphoto_ibfk_1` FOREIGN KEY (`media_id`) REFERENCES `media_mediaitem` (`id`),
CONSTRAINT `alum_id_refs_id_706915ea` FOREIGN KEY (`alum_id`) REFERENCES `alums_alum` (`id`),
CONSTRAINT `photo_id_refs_id_63282119` FOREIGN KEY (`photo_id`) REFERENCES `media_mediaitem` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=utf8
I want to delete the column photo_id, which presumably will also require deleting the foreign key constraint and the index.
我想删除列photo_id，这大概还需要删除外键约束和索引。
The problem is that I get errors when I try to drop the column:
问题是当我尝试删除列时出现错误：
ERROR 1025 (HY000): Error on rename of '.\dbname\#sql-670_c5c' to '.\dbname\alums_alumphoto' (errno: 150)
... when I try to drop the index (same as above), and when I try to drop the foreign key constraint:
...当我尝试删除索引（同上），以及当我尝试删除外键约束时：
ERROR 1091 (42000): Can't DROP 'photo_id_refs_id_63282119'; check that column/key exists)
What order should I be doing all of this in? What precise commands should I be using?
我应该按照什么顺序做这一切？我应该使用哪些精确的命令？
采纳答案by yogman
The sure thing is to make a duplicate table.
可以肯定的是制作一个重复的表格。
> CREATE TABLE alums_alumphoto_new LIKE alums_alumphoto;
> ALTER TABLE .... // Drop constraint
> ALTER TABLE .... // Drop KEY
> ALTER TABLE .... // Drop the column
> INSERT INTO alums_alumphoto_new (SELECT id, alum_id, photo_id, media_id, updated FROM alums_alumphoto);
> RENAME TABLE alums_alumphoto TO alums_alumphoto_old, alums_alumphoto_new TO alums_alumphoto;
If there's an error executing RENAME TABLE, some other tables might have foreign key constraints referencing this table, in which case this whole approach is stupid. :)
如果执行 RENAME TABLE 时出错，则其他一些表可能具有引用此表的外键约束，在这种情况下，整个方法是愚蠢的。:)
回答by Rishi Agarwal
Precisely, try this :
准确地说，试试这个：
First drop the Foreign Key or Constraint :
首先删除外键或约束：
ALTER TABLE `alums_alumphoto` DROP FOREIGN KEY `photo_id_refs_id_63282119`;
The previous command removes the Foreign Key Constraint on the column. Now you can drop the column photo_id(the index is removed by MySQL on dropping the column) :
上一个命令删除列上的外键约束。现在您可以删除该列photo_id（删除该列时 MySQL 会删除该索引）：
ALTER TABLE `alums_alumphoto` DROP COLUMN `photo_id`;
Aternatively, you could combine these 2 operations into one :
或者，您可以将这两个操作合二为一：
ALTER TABLE `alums_alumphoto`
DROP FOREIGN KEY `photo_id_refs_id_63282119` ,
DROP COLUMN `photo_id`;
回答by Ed Mays
Try combining the DROP KEY and DROP FOREIGN KEY statements.
尝试结合使用 DROP KEY 和 DROP FOREIGN KEY 语句。
ALTER TABLE `alums_alumphoto`
DROP KEY KEY `alums_alumphoto_photo_id`,
DROP FOREIGN KEY `photo_id_refs_id_63282119`;
ALTER TABLE `alums_alumphoto`
DROP COLUMN `photo_id`;
