CODEWITHSUNDEEP
mysqldatabase
Aditya Shukla
Aditya Shukla

Reputation: 14245

Empty a relational database schema

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 ?

Upvotes: 6

Views: 2435

Answers (2)

Ike Walker
Ike Walker

Reputation: 65577

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

Upvotes: 8

Ed Guiness
Ed Guiness

Reputation: 35277

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.

Upvotes: 2

Related Questions