MySQL

The TRUNCATE statement in MySQL is used to remove all rows from a table quickly. It’s similar to the DELETE statement but differs in terms of speed and the way it handles the data. TRUNCATE is typically faster than DELETE because it doesn’t generate individual row-by-row deletions but simply drops and recreates the table. This operation is useful when you want to empty a table but retain its structure for future use. For example, you might need to clear the employees table while keeping the structure intact for future employee records.

Steps to Use the TRUNCATE Operation:

1. Basic Syntax of TRUNCATE
   • The syntax for TRUNCATE is simple:

     TRUNCATE TABLE table_name;
         

   • Explanation:
     • table_name refers to the table whose rows you want to delete.
     • This command removes all rows from the table but retains the table structure.
2. Example 1: TRUNCATE theemployeesTable
   • Suppose the employees table is defined as follows:

     CREATE TABLE employees (
     employee_id INT AUTO_INCREMENT,
     first_name VARCHAR(50),
     last_name VARCHAR(50),
     department_id INT,
     salary DECIMAL(10, 2),
     hire_date DATE,
     PRIMARY KEY (employee_id)
     );
     
     

   • To remove all records from theemployeestable while keeping the table structure:

     TRUNCATE TABLE employees;
     

   • Explanation:
     • This command removes all data from the employees table, but the table structure (columns, data types) remains unchanged. Any auto-increment values for the primary key will be reset.
3. TRUNCATE vs. DELETE
   • DELETE removes rows one by one and can use the WHERE clause to delete specific rows, while TRUNCATE removes all rows in one go and doesn’t allow a WHERE clause.
   • TRUNCATE is faster because it doesn’t log individual row deletions.
   • When you TRUNCATE a table, it resets any AUTO_INCREMENT counters. This doesn’t happen with DELETE.
4. Example 2: TRUNCATE the departments Table
   • Suppose thedepartmentstable has the following structure:

     CREATE TABLE departments (
     department_id INT AUTO_INCREMENT,
     department_name VARCHAR(50),
     branch_id INT,
     PRIMARY KEY (department_id)
     );
     

   • To clear the table of all department records while keeping the table ready for new records:

     TRUNCATE TABLE departments;
     

   • Explanation:
     • This removes all rows in the departmentstable but retains the table's structure for future department records.
5. TRUNCATE and Foreign Keys
   • If your table has foreign key constraints, TRUNCATE may not work if there are dependent rows in related tables. You may need to disable foreign key checks temporarily:

     SET FOREIGN_KEY_CHECKS = 0;
     TRUNCATE TABLE employees;
     SET FOREIGN_KEY_CHECKS = 1;
     

   • Explanation:
     • Temporarily disabling foreign key checks allows TRUNCATE to work without violating constraints.
6. Example 3: TRUNCATE the branches Table
   • Suppose the branches table has this structure:

     CREATE TABLE branches (
     branch_id INT AUTO_INCREMENT,
     branch_name VARCHAR(100),
     location VARCHAR(100),
     PRIMARY KEY (branch_id)
     );
     

   • To remove all branch records:

     TRUNCATE TABLE branches;
     

   • Explanation:
     This clears the table of all branch records while maintaining the structure for future use.
7. Auto Increment Behavior with TRUNCATE
   • When you truncate a table that has an AUTO_INCREMENT column, the counter resets to the initial value (usually 1). For example, after truncating theemployees table, theemployee_id will restart from 1 for new entries.
8. TRUNCATE on a Table with a Large Amount of Data
   • TRUNCATE is particularly efficient when dealing with large tables as it removes data in bulk rather than row by row. This is much faster than DELETE for larger datasets.

TRUNCATE TABLE employees;

TRUNCATE TABLE departments;

TRUNCATE TABLE branches;

CREATE TABLE company (
company_id INT AUTO_INCREMENT,
company_name VARCHAR(100),
established_year INT,
PRIMARY KEY (company_id)
);

TRUNCATE TABLE company;

To gain complete access, login with gmail or outlook, no need of signup, click here