Lesson 9.3 · Reading time: ~6 min

SQL TRUNCATE removes all rows from a table and keeps the table; DROP TABLE removes the table itself, with its data and structure. In this lesson you'll learn how both statements work, how they differ from DELETE in speed, rollback and auto-increment behavior, and how foreign keys affect them.

SQL TRUNCATE and DROP TABLE

You can remove data on three levels: some rows (DELETE ... WHERE), all rows (TRUNCATE), or the whole table (DROP TABLE). The first was covered in Lesson 8.3. TRUNCATE and DROP TABLE are DDL statements: they work with the table as a whole and are much faster, but also less forgiving.

The sample databases on SQLtest are read-only; the examples create their own table, so you can run them in the playground on a database without sample data.

TRUNCATE: Empty the Table

TRUNCATE TABLE table_name;

After TRUNCATE:

• all rows are gone;
• the table, its columns, indexes and constraints remain;
• the auto-increment counter starts again from 1.

CREATE TABLE daily_import (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payload VARCHAR(100)
);
INSERT INTO daily_import (payload) VALUES ('a'), ('b'), ('c');

TRUNCATE TABLE daily_import;

INSERT INTO daily_import (payload) VALUES ('d');
SELECT * FROM daily_import;

Result: one row, id = 1, payload = 'd'. The table was emptied and its id counter reset.

DROP TABLE: Remove the Table

DROP TABLE table_name;

After DROP TABLE, the table no longer exists: its data, columns, indexes and constraints are gone, and any query that uses the table fails until it is created again.

DROP TABLE IF EXISTS old_reports;

IF EXISTS skips the statement without an error when the table doesn't exist, which is useful in scripts that recreate tables.

TRUNCATE vs DELETE vs DROP

The rollback row matters most. In MySQL and MariaDB, DDL statements commit the current transaction automatically, so a mistaken TRUNCATE or DROP TABLE can't be undone with ROLLBACK. Only a backup helps.

Foreign Keys

Foreign keys protect tables that other tables reference:

• DROP TABLE fails if another table has a foreign key to it. Drop the child table first, or drop the constraint.
• In MySQL, TRUNCATE fails for a table referenced by a foreign key, even if the child table is empty. DELETE FROM works in that case, because it checks the actual rows.

CREATE TABLE departments (department_id INT PRIMARY KEY);
CREATE TABLE staff (
    staff_id INT PRIMARY KEY,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments (department_id)
);

-- Error: Cannot truncate a table referenced in a foreign key constraint
TRUNCATE TABLE departments;

Which One to Use

• Remove some rows → DELETE ... WHERE.
• Empty a table you will fill again, such as a staging table for imports → TRUNCATE.
• The table is no longer needed → DROP TABLE.

Before TRUNCATE or DROP on a real database, check the table name and the database you are connected to: both statements work instantly and, in MySQL, without a way back.

Key Takeaways:

• TRUNCATE TABLE removes all rows quickly and keeps the table; the auto-increment counter is reset.
• DROP TABLE removes the table together with its data and structure; IF EXISTS avoids an error.
• DELETE can remove part of the rows and can be rolled back; TRUNCATE can't use WHERE.
• In MySQL and MariaDB, TRUNCATE and DROP commit implicitly and can't be rolled back.
• Foreign keys block dropping or truncating a referenced table.

Frequently Asked Questions

What is the difference between TRUNCATE and DELETE?

DELETE removes rows one by one, can use WHERE, fires delete triggers and can be rolled back. TRUNCATE removes all rows at once, is much faster, resets the auto-increment counter and, in MySQL, can't be rolled back.

What is the difference between TRUNCATE and DROP TABLE?

TRUNCATE empties the table but keeps it, ready for new rows. DROP TABLE removes the table completely; to use it again, you must create it with CREATE TABLE.

Can TRUNCATE be rolled back?

In MySQL and MariaDB, no: it causes an implicit commit. In PostgreSQL and SQL Server, yes, when it runs inside a transaction.