MySQL TRUNCATE TABLE

What is TRUNCATE TABLE and Why is it used?

If you want to remove all data from a table, type TRUNCATE TABLE table_name; It will delete all data from the table, but the table structure will remain.

It is used when you want to remove all existing data from a table but want to keep the same table available for storing new data records according to your needs.

Syntax:

TRUNCATE TABLE table_name;

Example:

Suppose the StudentA table contains the records of three students with their ages, which are organized in the table as follows. Now, you want to remove all records from this table while keeping its structure.

   
   StudentA Table:

┌────────────┬─────────┬─────┐
│ Student_ID │ Name    │ Age │
├────────────┼─────────┼─────┤
│ 101        │ Rahul   │ 20  │
│ 102        │ Amit    │ 21  │
│ 103        │ Neha    │ 19  │
└────────────┴─────────┴─────┘

Now, you will execute the TRUNCATE TABLE command:

  TRUNCATE TABLE StudentA;

Then, all records are removed from the StudentA table, but the table structure remains.

Output:

StudentA Table:
┌────────────┬─────────┬─────┐
│ Student_ID │ Name    │ Age │
├────────────┼─────────┼─────┤
└────────────┴─────────┴─────┘

Common Mistakes When Using TRUNCATE TABLE

➤If you use the TRUNCATE TABLE command on a table, you should carefully avoid the following mistakes:

• Using TRUNCATE TABLE When You Want to Keep Some Records:

  You should not use the TRUNCATE TABLE command when you want to keep some records because it removes all records from the table. It does not keep selected records, but the table structure remains.

• Trying to Use a WHERE Condition:

  When using the TRUNCATE TABLE command, you should not use a WHERE clause because TRUNCATE TABLE does not support the WHERE clause.

• Selecting the Wrong Table:

  You should carefully check the table name before executing the TRUNCATE TABLE command. If you select the wrong table, all records from that table will be removed.

• Ignoring Foreign Key Relationships:

  You should check Foreign Key relationships before using the TRUNCATE TABLE command because Foreign Key relationships can prevent a table from being truncated.

Difference Between TRUNCATE TABLE, DELETE, and DROP TABLE:

Feature	TRUNCATE TABLE	DELETE	DROP TABLE
What It Removes	Removes all records.	Removes selected records or all records.	Removes the entire table.
Table Structure	Keeps the table structure.	Keeps the table structure.	Does not keep the table structure.
WHERE Clause	Does not support the WHERE clause.	Supports the WHERE clause.	Does not use the WHERE clause.
Table Availability	The table remains available.	The table remains available.	The table is no longer available.