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

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

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

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