MySQL (InnoDB): need to delete column, and accompanying foreign key constraint and index

Question

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.

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?

Answer 1

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

Answer 2

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

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`;

Answer 3

Try combining the DROP KEY and DROP FOREIGN KEY statements.

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;

Answer 4

I ended up using the suggestion in the first answer and so have not been able to try the other two answers in order to discover whether they work. Thank you all for your assistance, though, and if anyone else tries these answers in a similar circumstance, please post your results!