MySQL – How to drop all Foreign Keys on a table
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.
Thanks a lot mate. This is just what I needed! You saved me hours of manual deletion.
Cheers
Pablo
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!
Glad we could help mate, and thanks for the chuckle.
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’;