Technipelago AB

Technipelago Blog Stuff that we learned...

How to drop a foreign key in MySQL

Publicerad den 19 Jun 2010

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:



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.

Tags: mysql

« Back