Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Statements and Clauses

SQL TRUNCATE TABLE statement

Reference for SQL TRUNCATE TABLE: empty a table quickly, how it differs from DELETE, identity resets, foreign-key limits and the SQLite equivalent.

TRUNCATE TABLE removes all rows from a table in one fast operation. Instead of deleting rows one at a time, the database discards the table's data wholesale, keeping the table definition, columns and indexes. It is meant for clearing staging tables, test data and caches.

SQLite has no TRUNCATE statement. There, DELETE FROM table_name; with no WHERE clause does the same job, and SQLite optimizes it into a quick "truncate" internally.

SQL
-- PostgreSQL, MySQL, SQL Server, Oracle
TRUNCATE TABLE staging_orders;

-- SQLite
DELETE FROM staging_orders;

Try it

SQL The SQLite equivalent: DELETE without WHERE

Press Run (or Ctrl + Enter) to see the result here.

SQL Resetting the counter of an AUTOINCREMENT table

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
TRUNCATE TABLE table_name;
TRUNCATE TABLE table_name RESTART IDENTITY CASCADE;  -- PostgreSQL options
Part Description
table_name The table to empty. TABLE is optional in PostgreSQL and MySQL.
RESTART IDENTITY PostgreSQL only: reset the table's sequences. CONTINUE IDENTITY is the default.
CASCADE PostgreSQL only: also truncate tables that have foreign keys pointing here.

Result: no row count is reported. There is no WHERE; you cannot truncate part of a table.

Dialect differences

Dialect Behavior
SQLite No TRUNCATE. Use DELETE FROM t;. Reset an AUTOINCREMENT counter by deleting its row in sqlite_sequence.
PostgreSQL Transactional (can be rolled back). Fires TRUNCATE triggers, not row triggers. Needs CASCADE when other tables reference it. Identity values restart only with RESTART IDENTITY.
MySQL Implicit commit (cannot be rolled back). Resets AUTO_INCREMENT. Refuses tables referenced by foreign keys.
SQL Server Can be rolled back inside a transaction. Resets identity to its seed. Blocked by foreign-key references; needs ALTER permission on the table.
Oracle DDL: commits implicitly and cannot be rolled back. Supports TRUNCATE TABLE t CASCADE (12c+) for referencing tables with ON DELETE CASCADE.

Usage notes

  • Faster than DELETE on big tables because it skips per-row work and logging; for small tables the difference is negligible.
  • No WHERE. For a filtered removal use DELETE.
  • Usually not undoable in MySQL and Oracle, so the safety net of a transaction is missing there. PostgreSQL and SQL Server allow rollback.
  • Foreign keys commonly block truncation; clear child tables first or use the dialect's CASCADE option.
  • Triggers: ordinary ON DELETE row triggers do not fire for TRUNCATE.
  • Permissions: it is often a separate privilege from DELETE.
  • SQLite file size does not shrink after the DELETE; run VACUUM.
  • DROP TABLE then CREATE TABLE also empties a table but loses indexes, grants and dependent objects.