Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 736
nmjk Avatar asked Dec 03 '08 22:12

nmjk


People also ask

How do I delete a foreign key constraint in MySQL?

You can drop a foreign key constraint using the following ALTER TABLE syntax: ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; If the FOREIGN KEY clause defined a CONSTRAINT name when you created the constraint, you can refer to that name to drop the foreign key constraint.

Do we need to create index on foreign key MySQL?

MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created. Information about foreign keys on InnoDB tables can also be found in the INNODB_FOREIGN and INNODB_FOREIGN_COLS tables, in the INFORMATION_SCHEMA database.

How do you drop a foreign key constraint?

To drop a foreign key from a table, use the ALTER TABLE clause with the name of the table (in our example, student ) followed by the clause DROP CONSTRAINT with the name of the foreign key constraint.


2 Answers

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`;
like image 56
Rishi Agarwal Avatar answered Oct 19 '22 04:10

Rishi Agarwal


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

like image 7
yogman Avatar answered Oct 19 '22 06:10

yogman