Z-Car

MySQL – How to drop all Foreign Keys on a table

Hot Girl Fixing MySQL

MySQL – How to drop all Foreign Keys on a table

I recently was working with a 3rd party DB that was designed well and used lots of foreign keys to enforce referential integrity. Unfortunately, the application that used the DB structure was very poorly written and caused all sorts of problems when the foreign keys were applied. I had to quickly remove all the foreign keys from about 160 tables. The code below will quickly create a script that removes all Foreign Keys from a database.

select concat(‘alter table ‘,table_schema,’.’,table_name,’ DROP FOREIGN KEY ‘,constraint_name,’;’) from information_schema.table_constraints
where constraint_type=’FOREIGN KEY’;

You can limit by schema by adding the line : AND table_schema rlike ‘Schema Name’

Remember, Foreign keys are only valid when using Innodb, MyISAM does not support foreign keys.

Thanks to Prodromus for providing some of this information.  They have a great site that lists a ton of MySQL tips and tricks.  And, if you are a non-profit, they even perform pro-bono MySQL DBA work.  Look them up.

4 thoughts on “MySQL – How to drop all Foreign Keys on a table

  1. Pablo Alfaro

    Thanks a lot mate. This is just what I needed! You saved me hours of manual deletion.
    Cheers
    Pablo

  2. Alex M.

    Thanks a lot mate. I kept on wanting to drop all foreign keys on a table but had no idea what to wear in the process so I always abandoned the idea. But I tried doing it in panties like your article shows and it works! Awesome!

  3. Rinnert

    Awesome script, though you have some syntax errors in it (single quotes), but many thanks 🙂
    This is what I ended up if it doesn’t mess up the quotations:
    SELECT
    concat(‘alter table ‘, table_schema, ‘.’, table_name, ‘ DROP FOREIGN KEY ‘, constraint_name, ‘;’)
    FROM
    information_schema.table_constraints
    WHERE
    constraint_type=’FOREIGN KEY’
    AND table_schema rlike ‘your_db_name’;

Leave a Reply and Let me know what you think...