TRUNCATE

An SQL statement that quickly deletes all rows from a table. It is faster than DELETE and also resets the AUTO_INCREMENT counter.

Syntax

-- Deletes all rows from the table.
TRUNCATE TABLE table_name;

-- The TABLE keyword is optional (MySQL).
TRUNCATE table_name;

Differences from DELETE

Item	TRUNCATE	DELETE (all rows)
Speed	Fast (does not write row-level logs).	Slow (logs each row individually).
WHERE clause	Not supported (removes all rows only).	Supported.
AUTO_INCREMENT	Reset to 1.	Not reset.
Transaction	Cannot be rolled back (DDL statement).	Can be rolled back.
Rows affected	Not returned (0 rows affected).	Returns the number of deleted rows.
Foreign key constraint	Cannot be used on tables referenced by a foreign key.	Supported (raises an error on constraint violation).

Sample Code

-- Clears the table used to store temporary import data.
TRUNCATE TABLE temp_import_data;

-- Periodically clears the log table.
TRUNCATE TABLE access_logs;

-- Resets the test table (also resets AUTO_INCREMENT to 1).
TRUNCATE TABLE test_users;

-- Example of disabling foreign key checks before TRUNCATE (MySQL).
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE orders;
TRUNCATE TABLE order_items;
SET FOREIGN_KEY_CHECKS = 1;

Result

-- Result of: TRUNCATE TABLE temp_import_data;
-- Query OK, 0 rows affected (0.03 sec)
-- (The row count is always displayed as 0.)

-- After TRUNCATE, INSERT starts AUTO_INCREMENT from 1.
INSERT INTO test_users (name) VALUES ('Test User');
-- id = 1 is assigned.

Syntax by Database

'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;

Notes

'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.

If you find any errors or copyright issues, please contact us.

Home

SQL Dictionary