How to Truncate a Table in SQL

Published : 30 December 2024
Updated : 01 January 2025

142 Views

The TRUNCATE TABLE command is a Data Definition Language (DDL) statement, used in SQL to delete all rows from a table. It resets table to its original state while preserving the table structure, indexes and constraints. It also resets any auto-increment counters associated with the table. It is a powerful and efficient way of removing all rows from a table. Unlike the DELETE statement. Which logs individual row deletions, the TRUNCATE statement removes all rows from a table in a single go without detailed logging that’s making it a faster option for bulk deletions.

You can use the DROP TABLE command to delete a table but this will completely remove the table structure from the database. If you want to store data in this table again. You will need to recreate the table.

In this article, we will explore how TRUNCATE works, its syntax, use cases and the differences compared to DELETE and so on. by the end of this article you have clear understanding of truncate operation on table.

Syntax of TRUNCATE TABLE Command

TRUNCATE TABLE TableName;

Key Features of TRUNCATE TABLE Command

Efficient Deletion :- TRUNCATE is faster than DELETE as it does not generate individual row-level logs.
Resets Identity Counters :- For tables with auto-increment columns, the counter is reset to its seed value after the TRUNCATE operation.
DDL Statement :- TRUNCATE is a DDL statement, which means it can not be rolled back if the database does not support transactional DDL.
Constraints Handling :- When you attempt to truncate a table that has foreign key constraints, the operation may be blocked if the table is referenced by another table. Foreign key constraints prevent truncation in such cases, unless the constraints are removed or the data in the child table handled first.

Let's Understand TRUNCATE TABLE Command Behavior with Example

Let's consider a scenario where we create an Employees table and insert some records in table.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50),
    Mobile VARCHAR(50),
    DepartmentID INT,
    Salary DECIMAL(10, 2)
);

INSERT INTO Employees (Name, Mobile, DepartmentID, Salary)
VALUES
    ('Ram', '1234567890', 101, 50000.00),
    ('Rajesh', '9876543210', 102, 60000.00),
    ('Deepak', '5551234567', 103, 55000.00);

Now run SELECT Query on Employess

SELECT * FROM Employees;

EmployeeID Name	     Mobile	DepartmentID	Salary
1	   Ram	     1234567890	  101	      50000.00
2	   Rajesh    9876543210	  102	      60000.00
3	   Deepak    5551234567	  103	      55000.00

Now run TRUNCATE query and after that SELECT query.

TRUNCATE TABLE Employees;

SEELECT * FROM Employees;

There will be no data in table.

Let's insert some new records in table and understand what will happen

INSERT INTO Employees (Name, Mobile, DepartmentID, Salary)
VALUES
     ('Rakesh', '5654678745', 112, 80000.00),
    ('Gopal', '5654678765', 111, 70000.00);

SELECT * FROM Employees;

Select Query OutPut

EmployeeID	Name	Mobile	   DepartmentID	Salary
1	     Rakesh	5654678745	112	80000.00
2	     Gopal	5654678765	111	70000.00

As we can see, after truncating Employees table, when we insert a new record into the table, the auto-increment column (EmployeeId) resets to its initial state, starting from the defined seed value.

Attempting to Truncate a Table with a Foreign Key

If we attempt to truncate a table which has Foreign key constraints. the database will throw an error

Let's explore with Example, Create two tables Departments and Employees with Foreign Key and insert some data into this

CREATE TABLE Departments ( 
Id INT PRIMARY KEY AUTO_INCREMENT, 
Name VARCHAR(100) 
);
CREATE TABLE Employees (
    EmployeeId INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100),
    Mobile VARCHAR(15),
    DepartmentId INT,
    Salary DECIMAL(10, 2),
    CONSTRAINT FK_Department FOREIGN KEY (DepartmentId) REFERENCES Departments(Id)
);

Insert some records into both tables

INSERT INTO Departments (Name) VALUES ("Tech"),("Opr");

INSERT INTO Employees (Name, Mobile, DepartmentID, Salary)
VALUES
    ('Ram', '1234567890', 1, 50000.00),
    ('Rajesh', '9876543210', 2, 6000.00),
    ('Deepak', '5551234567', 1, 55000.00);

Now let's TRUNCATE Departments table.

TRUNCATE TABLE Departments;

Error Code: 1701. Cannot truncate a table referenced in a 
foreign key constraint (`Employees`, CONSTRAINT `FK_Department`) 0.000 sec

Differences Between TRUNCATE and DELETE

FeatureTRUNCATEDELETE

Command Type

DDL (Data Definition Language)

DML (Data Manipulation Language)

Row Deletion

Deletes all rows without logging

Deletes rows individually, generating logs

Performance

Faster for large datasets

Slower as it logs each row deletion

Constraints Handling

May be restricted by foreign key constraints

Can delete rows with constraints if handled properly

Transaction Support

Not always rollback-supported

Rollback-supported

Identity Reset

Resets auto-increment counters

Does not reset auto-increment counters

WHERE Clause Support

Not supported

Supported

Notes

TRUNCATE TABLE command is a powerful and efficient way to remove all rows from a table while retaining its structure.
It is useful for resetting staging or testing environments quickly.
Always back up important data before truncating.
Foreign key constraints might prevent truncation.
Use DELETE instead of TRUNCATE if you need conditional deletion or rollback support.
Truncation requires higher privileges compared to DELETE.