Technipelago Blog Stuff that we learned...
How to drop a foreign key in MySQL
Often when trying to drop a foreign key in MySQL I get an error message.
ERROR 1025: Error on rename of ‘./dbname/#sql-110_1′ to ‘./dbname/tablename’ (errno: 150)
The procedure I take when this happens is this:
MySQL> show engine innodb status;
Scroll up to find this section:
LATEST FOREIGN KEY ERROR
100623 16:15:14 Error in foreign key constraint of table devdb/foo:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON … SET NULL columns is declared NOT NULL.
CONSTRAINT “FK335CD11BA9A1557E” FOREIGN KEY (”bar_id”) REFERENCES “bar” (”id”)
MySQL> alter table tablename drop foreign key FK335CD11BA9A1557E; MySQL> alter table tablename drop bar_id;
Sometimes I have to repeat the steps twice because there are two foreign keys to drop.