MySQL Truncate Statement

MySQL Truncate table empties or clears out all the records from a table. The structure of the data remains untouched.

MySQL Truncate syntax

The basic syntax will be:

TRUNCATE TABLE  table_name;

Some important points to remember about the Truncate statement are as follows:

• Users require the Drop privilege to truncate a table.
• It is similar to delete without where clause but better optimized.
• Unlike a delete statement, MySQL implicitly commits, i.e., auto-commit with no reversal.
• If Foreign Key constraints from other tables are present, truncate fails.
• A Truncate operation always returns "0 rows affected".
• Truncate resets the auto-increment value.
• Truncate command preserves partitions but drops indexes.

Truncate table with Foreign key constraint

Since the employee's table has a foreign key constraint on the ratings table, the below command fails.

The ratings table has two columns. rating_id (int) which is the primary key and percentage (float). 

The employees table references the rating_id columns as a foreign key. The truncate does not affect either table since it fails.

truncate table employees;

Truncate table in MySQL

The employee_subset table does not have any constraints or references to it. So the truncate table command works on it. The structure remains intact aas seen in the below image.

truncate table employee_subset;