这是我的表:
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
我想删除列photo_id
,可能还需要删除外键约束和索引.
问题是当我尝试删除列时出现错误:
ERROR 1025 (HY000): Error on rename of '.\dbname\#sql-670_c5c' to '.\dbname\alums_alumphoto' (errno: 150)
...当我尝试删除索引(与上面相同)时,以及当我尝试删除外键约束时:
ERROR 1091 (42000): Can't DROP 'photo_id_refs_id_63282119'; check that column/key exists)
我应该做什么顺序呢?我应该使用哪些精确命令?
准确地说,试试这个:
首先删除外键或约束:
ALTER TABLE `alums_alumphoto` DROP FOREIGN KEY `photo_id_refs_id_63282119`;
上一个命令删除列上的外键约束.现在你可以删除列了photo_id
(删除列时MySQL删除了索引):
ALTER TABLE `alums_alumphoto` DROP COLUMN `photo_id`;
或者,您可以将这两个操作合并为一个:
ALTER TABLE `alums_alumphoto` DROP FOREIGN KEY `photo_id_refs_id_63282119` , DROP COLUMN `photo_id`;
肯定的是制作一个重复的表格.
> 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;
如果执行RENAME TABLE时出错,则其他一些表可能具有引用此表的外键约束,在这种情况下,整个方法都是愚蠢的.:)