Empty a relational database schema

Question

I have a database which I have backed up . Now i am trying to delete all the content from the original db and restore it to it's empty state. since it's a relational db it has key constraints. Is there any tool I could use for this ?

Answer 1

The easiest way to do this is probably to disable foreign key checks, then truncate the tables. Since foreign keys are disabled, the order in which you truncate the tables does not matter.

set foreign_key_checks = 0;
truncate table parent;
truncate table child;
truncate table ...

You can even use the information_schema to generate the truncate table statements for you. Something like this:

select concat('truncate table ',table_schema,'.',table_name,';') as sql_stmt
from information_schema.tables
where table_schema = 'your_schema_name'
and table_type = 'base table';

Answer 2

You could temporarily drop or disable all constraints, truncate all tables, and then restore the constraints. I've taken this approach for SQL Server and it works fine.

http://lists.mysql.com/mysql/194954

Perhaps an even better approach would be to reverse out the schema into scripts (which you put under version control) and then recreate the database from scratch.