How to Remove Foreign Keys in MySQL
I ran into a situation a while back, where I'd designed a MySQL databses in InnoDB format on a test server, and implemented foreign key constraints with cascaded updates and deletes. However, when I uploaded the database to my web host the whole thing just crashed. Apparently the host didn't support InnoDB format, and therefore didn't support foreign keys. Here's how I fixed it.
Basically the database had to be downgraded, all the tables converted back to ISAM format. In order to do that, I would have to remove the foreign key constraints.
The first thing you need to do is find out what the foreign keys are called by name. They are named numerically. Here's the SELECT statement to list the foreign keys.
SHOW TABLE STATUS FROM db_name LIKE 'table_name';
The foreign key constraints are listed in the Comment column of the output. Pick the name of the one(s) you want and type the following (assume the foreign key we picked is called 0_172):
alter table page_titles drop foreign key 0_172
That's it. Once they're dropped, you can convert to ISAM if necessary
on February 6th, 2009 at 1:47 am
Generate your alters with this:
select concat('alter table ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name,';') from information_schema.table_constraints;