views:

693

answers:

3

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 SO 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?

+2  A: 

Since you are not interested in keeping any data, drop the entire database and create a new one.

Otávio Décio
Forgot to say the database is not mysql 5.x but 4.x which means I'm not able to use that command
johnnyArt
I think you should - http://dev.mysql.com/doc/refman/4.1/en/drop-database.html
Otávio Décio
@Otávio Décio: Oh my, I feel so stupid now, I was substituting the word DATABASE with the actual name of the database instead of adding it afterwards, thanks both +1
johnnyArt
+1  A: 

Drop database exist in all versions of MySQL. But if you want to keep the table structure, here is an idea

mysqldump --no-data --add-drop-database --add-drop-table -hHOSTNAME -uUSERNAME -p > dump.sql

This is a program, not a mysql command

Then, log into mysql and

source dump.sql;

MindStalker
A: 

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;
PAT