Force drop mysql bypassing foreign key constraint

Question

I'm trying to delete all tables from a database except one, and I end up having the following error:

Cannot delete or update a parent row: a foreign key constraint fails

Of course I could trial and error to see what those key constraints are and eventually delete all tables but I'd like to know if there is a fast way to force drop all tables (as I'll be able to re-insert those I don't want deleted).

Google aimed me at some site that suggested the following method:

mysql> SET foreign_key_checks = 0;
mysql> drop table ...
mysql> SET foreign_key_checks = 1;

Short answer is it didn't really do the trick since I ended up receiving the same error while I was able to delete some more tables. I've seen on Stack Overflow ways to get all foreign keys linked to a certain table but that's way too time consuming unless I script it all (which is doable in the case there is no other option)

Database is 4.1 so I can't use DROP DATABASE

Ideas?

Answer 1

This might be useful to someone ending up here from a search. Make sure you're trying to drop a table and not a view.

SET foreign_key_checks = 0;
-- Drop tables
drop table ...
-- Drop views
drop view ...
SET foreign_key_checks = 1;

SET foreign_key_checks = 0 is to set foreign key checks to off and then SET foreign_key_checks = 1 is to set foreign key checks back on. While the checks are off the tables can be dropped, the checks are then turned back on to keep the integrity of the table structure.