SQL Code

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

Share

2 Comments

  1. Generate your alters with this:

    select concat('alter table ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name,';') from information_schema.table_constraints;

  2. Close… Try –
    select concat('alter table ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name,';') from information_schema.table_constraints
    where constraint_type='FOREIGN KEY';

Leave a Reply

Your email address will not be published. Required fields are marked *

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Anti-spam image