MySQL TRUNCATE TABLE

The TRUNCATE TABLE statement removes all rows from a table in one fast operation, while keeping the table structure — its columns, indexes, and constraints — completely intact. It is essentially a way to empty a table and start fresh, without having to drop and recreate it.

Syntax

TRUNCATE TABLE table_name;

The keyword TABLE is optional in MySQL, so this also works:

TRUNCATE table_name;

Basic Example

Remove all rows from the students table while keeping its structure:

TRUNCATE TABLE students;

MySQL responds with:

Query OK, 0 rows affected (0.04 sec)

The table is now empty. Running SELECT * FROM students; will return an empty result set, but the table itself still exists with all its columns defined.

Warning: TRUNCATE TABLE cannot be rolled back. Unlike DELETE, it does not log individual row deletions and is not transactional in MySQL's InnoDB engine. Once you run it, the data is gone permanently without a backup.

AUTO_INCREMENT is Reset

One important behavior of TRUNCATE is that it resets the AUTO_INCREMENT counter back to 1. So the next row you insert after a truncate will get id = 1 again:

-- Before: students table has rows with id 1, 2, 3, 4
TRUNCATE TABLE students;

-- After: table is empty, AUTO_INCREMENT resets to 1
INSERT INTO students (name, email) VALUES ('Alice', 'alice@example.com');
-- Alice gets id = 1

This is different from DELETE FROM students; — deleting all rows with DELETE does not reset the AUTO_INCREMENT counter. The next insert after a DELETE would continue from where the counter left off.

Use Case: TRUNCATE is ideal for resetting test data or clearing staging tables before loading fresh data, because it is much faster than DELETE on large tables and automatically resets the ID counter.

TRUNCATE vs DELETE vs DROP TABLE

Feature	TRUNCATE	DELETE	DROP TABLE
Removes all rows	✔ Yes	✔ Yes (without WHERE)	✔ Yes
Keeps table structure	✔ Yes	✔ Yes	✘ No
Resets AUTO_INCREMENT	✔ Yes	✘ No	✔ Yes (on recreate)
Can filter rows (WHERE)	✘ No	✔ Yes	✘ No
Can be rolled back	✘ No	✔ Yes (in transaction)	✘ No
Speed on large tables	✔ Very fast	Slower (row by row)	✔ Very fast

Foreign Key Restrictions

You cannot TRUNCATE a table that is referenced by a foreign key in another table. MySQL will return an error to protect referential integrity:

TRUNCATE TABLE students;
-- ERROR 1701: Cannot truncate a table referenced in a foreign key constraint

To work around this, you can temporarily disable foreign key checks, truncate, then re-enable them:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE students;
SET FOREIGN_KEY_CHECKS = 1;

Tip: Only disable foreign key checks when you are absolutely certain about what you are doing. Leaving them disabled accidentally can allow data integrity violations to silently accumulate in your database.

Key Points to Remember

TRUNCATE TABLE name; removes all rows but keeps the table structure.
It resets the AUTO_INCREMENT counter to 1 — unlike DELETE.
It is much faster than DELETE on large tables because it does not log individual row deletions.
It cannot be rolled back — the operation is immediate and permanent.
It cannot be used on tables that are referenced by active foreign key constraints.
Use it for clearing test/staging data where you want a clean restart with IDs beginning at 1.

« Previous Lesson Next Lesson »