Topic 8 of 52 · Beginner
TRUNCATE TABLE
Remove every row instantly while keeping the table structure.
01TRUNCATE TABLE
Module 02 – Database & Table Management (DDL)
02What is it?
TRUNCATE TABLE empties a table: all rows are removed, but the table, its columns, indexes and constraints remain ready for new data. In MySQL it also resets AUTO_INCREMENT.
03Why do we need it?
Staging tables are reloaded every run. Removing millions of rows one by one with DELETE is slow and logs every row; TRUNCATE drops and recreates the data storage almost instantly.
04Syntax
TRUNCATE [TABLE] table_name;The in-browser engine (SQLite) has no TRUNCATE, so AICodeDost translates it to DELETE FROM — rows and structure end up identical.
05Parameter explanation
- table_name
- Table to empty. No WHERE clause is allowed.
06Sample database
Every example and exercise below starts from a fresh copy of this data.
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
dept_id INT,
salary DECIMAL(10,2),
hire_date DATE
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'HR');
INSERT INTO employees VALUES
(1,'Asha',10,85000,'2021-04-12'),
(2,'Ravi',20,52000,'2022-01-03'),
(3,'Meena',10,97000,'2019-09-20'),
(4,'Karan',30,46000,'2023-06-15'),
(5,'Divya',20,61000,'2020-11-30');07Basic coding example
08Step-by-step execution
- 1.MySQL takes an exclusive lock on employees.
- 2.Internally it drops and recreates the table's storage (DDL), so rows aren't deleted one by one.
- 3.AUTO_INCREMENT restarts at 1. The table still exists with 0 rows.
09Before and after output
Generated live by running the basic example against the sample database.
Running on the SQL engine…
10Intermediate coding example
11Real-world business scenario
A daily job: TRUNCATE TABLE stg_transactions; then bulk-load today's file into it, validate, and merge into the main table.
12Common mistakes
TRUNCATE TABLE employees WHERE dept_id = 20;
TRUNCATE has no WHERE clause.
DELETE FROM employees WHERE dept_id = 20;
TRUNCATE TABLE departments; -- referenced by a foreign key
MySQL refuses to truncate a table referenced by an FK, even if the child is empty.
Delete with DELETE FROM, or drop the FK first.
START TRANSACTION; TRUNCATE TABLE employees; ROLLBACK;
TRUNCATE auto-commits in MySQL; ROLLBACK can't restore rows.
Use DELETE inside a transaction if you might need to undo.
13Important comparisons
| TRUNCATE | DELETE (no WHERE) | |
|---|---|---|
| Type | DDL | DML |
| Speed on big tables | Very fast | Slow (row by row) |
| Fires DELETE triggers | No | Yes |
| Resets AUTO_INCREMENT | Yes | No |
| Can ROLLBACK | No | Yes |
14Practical exercises
Beginner1. Empty employees and count its rows.
Reveal solution (15)
0 rows; table still exists.
Beginner2. Truncate departments and show its columns still exist.
Reveal solution (15)
Structure is kept.
Beginner3. Truncate employees, then insert one new row.
Reveal solution (15)
Ready for new data immediately.
Intermediate4. Back up employees, truncate, and reload from the backup.
Reveal solution (15)
Classic truncate-and-reload.
Intermediate5. Reload employees with only Engineering staff (dept 10).
Reveal solution (15)
2 rows.
Intermediate6. Delete only Sales staff (dept 20) — use the right command.
Reveal solution (15)
Filtering needs DELETE, not TRUNCATE.
Advanced7. Create a staging table, load it twice with truncation between loads, and show it holds only the last load.
Reveal solution (15)
WHERE 1=0 copies structure without rows.
Advanced8. Compare row counts before and after truncation in one script.
Reveal solution (15)
Reconciliation pattern: 5 then 0.
16Key takeaways
- TRUNCATE empties a table but keeps its structure.
- No WHERE; no DELETE triggers; resets AUTO_INCREMENT.
- DDL in MySQL — cannot be rolled back.
- The browser engine runs it as DELETE FROM, which gives the same visible result.
17Knowledge check
1. TRUNCATE is classified in MySQL as…
2. Can TRUNCATE take a WHERE clause?
3. After TRUNCATE in MySQL, the next AUTO_INCREMENT value is…
Interview questions
Why is TRUNCATE faster than DELETE?
DELETE removes and logs each row and fires triggers; TRUNCATE deallocates the table's data pages in one operation.
Lesson test
Take an 8-question test on “TRUNCATE TABLE” — concepts, output prediction, bug spotting and interview questions.