3

I would like to truncate all the rows of the tables in a mysql database.

SET FOREIGN_KEY_CHECKS = 0; -- Disable foreign key checking.
-- Need MySQL Query to iterater/loop and truncate all the tables or delete all the rows of the -- table
SET FOREIGN_KEY_CHECKS = 1; -- Enable foreign key checking.

Could someone help me with the query please?

Thanks.

Improve this question
2

2 Answers 2

Reset to default
1

because u used this table primary key as foreign key in some other table. *Delete values from child table and then try to truncate parent table *

eg: country table -> country id(primary key),name
people table -> people id(primary key), People name, country id(foreign key)

delete values from people table and then try to truncate country table :)

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I need a generic query to disable all constraints and truncate all rows..i.e i want the structure to be maintained but all the rows of the all the tables in a database to be delete by ignoring any constraints such as foreign key, primary key, unique key etc.,
Your question is similar to this post.
the query should be generic to be used in any mysql database to truncate all the tables..specificying names will make the query as DB dependent.
This does not answer the question (which is actually "how do I truncate all tables"), but provides a workaround.
1

This query would do it:

SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) 
FROM INFORMATION_SCHEMA.TABLES
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.