soledad penadés

You probably know there's a quick way of dumping your whole database to a file, with mysqldump. This generates a .sql file containing all the data you would need to restore your database when moving to another server, or when doing a backup.

Anyway, if you have ever tried to restore a database which contained foreign keys it's very probable that you had problems with it, as the dump just outputs all the tables in alphabetical order, not taking into account whether one depends on another and so on. So you are likely to get an error like this: "Cannot delete or update a parent row: a foreign key constraint fails".

Scaring, isn't it?

First solution could be reordering manually the dump, putting the tables in a reasonable order in a way that they can be created without breaking any constraint. But this is a pain-you-know-where!!

There's a better way of doing this. In your sql dump, add this statement at the beginning:

SET FOREIGN_KEY_CHECKS=0;

and this other at the end:

SET FOREIGN_KEY_CHECKS=1;

and voila! run that dump with your favourite method - may it be phpmyadmin or mysql command line client itself (this is the fastest one and the recommended method if you have a very big database, otherwise phpmyadmin can time out and you'll end with an incomplete database).

Now this method is not only avoiding problems with foreign keys, but is also faster, as it does not check things that shouldbe ok - as they come from a supposedly ok database.

Isn't it cool? Enjoy it! Now you don't have excuse for not doing backups of your database!