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 ?

Sensible

I don't know of a tool that can do this but it may not be necessary. The trick is to delete from tables in the correct order. I once had to do this in Oracle. Oracle has its own tables that contain info about all the relations between the tables I had created. By querying this metatable, I was able to get the proper ordering of the relations in order to delete without ever hitting a key contraint violation (I'm posting as a comment because I don't know if this is possible in MySQL). The other (and quicker-running) option is to disable contraints, truncate, re-enable constraints. – Masto

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

Heteroecious

Thanks for the help.Solved the issue. – Sensible

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.

Pleistocene

Thanks.I will try these solutions. – Sensible