Now, run the Db Truncate.sql script generatedДў) Not needed to drop and recreate the DB/Schema with the same structureДЎ) FK constraints should be names in the table with the name containing 'FK' in the constraint name. INTO OUTFILE 'C:/DB Truncate.sql' LINES TERMINATED BY ' ' WHERE CONSTRAINT_NAME LIKE 'FK%' AND TABLE_SCHEMA='' SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ADD CONSTRAINT ',CONSTRAINT_NAME,' FOREIGN KEY(',COLUMN_NAME,')',' REFERENCES ',REFERENCED_TABLE_NAME,'(',REFERENCED_COLUMN_NAME,') ') FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE SELECT CONCAT('OPTIMIZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='' AND TABLE_TYPE='BASE TABLE' SELECT CONCAT('TRUNCATE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ') FROM INFORMATION_SCHEMA.TABLES WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_SCHEMA='' SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,' ') FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS See below a script that would generate the script to carry out the above operations. Once data truncation is carried out, create the same foreign key constraints again on the same table. Optionally, use the optimize table (in mysql, innodb engine esp) to reclaim the used data space/size to OS after data truncation.

So, all foreign key constraints must be dropped initially followed by table truncation.To truncate a table, one must drop the foreign key constraints mapped to the columns in this table from other tables (in fact on all tables in the specific DB/Schema).

Base de datos order and chaos