MySQL Foreign Key Night-mare
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. I can never understand why developers will create foreign keys, however then not write their code properly to prevent errors, or at least attempt to catch those errors.
The code below will quickly create a script that removes all Foreign Keys from a database. Many folks will simply change the engine type to MyIsam and then back to InnoDB. This may work, but it is very slow if you already have a lot of data in your tables.
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.
Quite useful. And I completely agree. The sad thing is when you have tables with several million records that have an FK that is completely useless that only corrupts performance. And removing the FK is an hours-long process!