TRUNCATE

Deletes all rows from the table.

TRUNCATE TABLE table_name;

The TABLE keyword is optional (MySQL).

TRUNCATE table_name;

The following examples use the temp_import_data table.

Clears the table used to store temporary import data.

sample_truncate.sql

TRUNCATE TABLE temp_import_data;

Query OK, 0 rows affected (0.03 sec)

sample_truncate.sql

SELECT * FROM temp_import_data;

Empty set (0.00 sec)

Periodically clears the log table.

sample_truncate.sql

TRUNCATE TABLE access_logs;

Query OK, 0 rows affected (0.02 sec)

Resets the test table (also resets AUTO_INCREMENT to 1).

sample_truncate.sql

TRUNCATE TABLE test_users;

Query OK, 0 rows affected (0.02 sec)

Example of disabling foreign key checks before TRUNCATE (MySQL).

sample_truncate.sql

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE orders;
TRUNCATE TABLE order_items;
SET FOREIGN_KEY_CHECKS = 1;

Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)

TRUNCATE TABLE is supported in MySQL, PostgreSQL, Oracle, and SQL Server.

TRUNCATE TABLE temp_import_data;

In PostgreSQL, TRUNCATE can be rolled back within a transaction (DDL statements are also transaction-safe). You can also add CASCADE to simultaneously delete rows in referencing tables when a foreign key relationship exists.

-- Deletes from the table and all tables that reference it (PostgreSQL).
TRUNCATE TABLE orders CASCADE;

-- Truncates multiple tables at once (PostgreSQL).
TRUNCATE TABLE orders, order_items;

SQLite does not support TRUNCATE TABLE. Use DELETE FROM to remove all rows instead.

-- Delete all rows in SQLite.
DELETE FROM temp_import_data;

TRUNCATE removes all rows in a manner similar to recreating the table, making it extremely fast even for large tables. It is well suited for periodically resetting log tables or clearing test data.

TRUNCATE is not protected by transactions (BEGIN / ROLLBACK). Once executed, it cannot be undone — always take a backup before running it. It also cannot be used on tables that are referenced by a foreign key in another table.

If you need to delete only specific rows based on a condition, or if you need rollback support, use DELETE instead.