Truncate Table in MariaDB

MariaDB is an open-source relational database management system. It offers faster query execution and data manipulation as compared to other RDBMS. Also, it handles large datasets very efficiently. MariaDB Provides plugins for various functionalities like backup, performance monitoring, security, and data integration. MariaDB can easily integrate with other open-source software and cloud platforms.

In this article, We will learn about the Truncate Table in MariaDB along with its syntax, examples, and so on.

TRUNCATE TABLE in MariaDB

A TRUNCATE TABLE allows all records from a table to be deleted, resetting the table to an empty state. Compared with the DELETE statement, which deletes rows one by one and causes increased transaction logs, TRUNCATE TABLE is a more efficient method of clearing a table.

Syntax:

TRUNCATE TABLE [database_name.] table_name;

Explanation: In this syntax, database_name denotes the name of the database in which your table is present and table_name is the name of the table.

Examples of TRUNCATE TABLE in MariaDB

To understand the TRUNCATE TABLE in MariaDB, we need a table on which we will perform the TRUNCATE TABLE command. So here we will create a table and insert some data into it.

Query for Create Table

CREATE TABLE Students 
(
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    course VARCHAR(50),
);

Query for Insert Data:

INSERT INTO Students VALUES
(1, 'Ratnala', 'Harshavardha', 'CSE'),
(2, 'Maram', 'Harsha', 'Electrical'),
(3, 'Medishetty', 'Yashwanth', 'Chemical'),
(4, 'Sukumar', 'Reddy', 'Mechanical' ),
(5, 'Engula', 'Vamshi', 'Electronics');

We can check whether our data is inserted successfully or not using below command.

SELECT * FROM Students;

Output:

Records Inserted

Explanation: Suppose we have a table named Students, and we want to remove all students records from the table while keeping its structure. We can done this using the TRUNCATE TABLE command.

Query:

TRUNCATE TABLE Students;
SELECT * FROM STUDENTS;

Output:

Delete Table

Explanation: The TRUNCATE query is executed successfully. As we can see that the records of “Students” table have been deleted permanently.

Advantages of the TRUNCATE TABLE

• Performance Efficiency: When deleting all records from a table, TRUNCATE TABLE is significantly faster than the DELETE statement. This is due to the fact that TRUNCATE does not create the same number of transaction logs, and the entire database engine does not have to process each row separately.
• Resource Optimization: Truncating a table takes less storage space than the DELETE statement, making it easier to recover disk space.
• Resetting Auto-Increment Columns: When the table contains an auto-increment column, the truncation of the table restores the auto-increment counter to its original value. This is not true for the DELETE statement, which may result in gaps in the auto-increment sequence.
• Reduced Locking Overhead: In general, truncate operations require less locking overhead than the DELETE statement, and thus, truncate is preferred when minimal disruption to concurrent transactions is needed.

Disadvantages of the TRUNCATE TABLE

While TRUNCATE TABLE is a powerful and efficient tool, there are certain considerations and limitations of TRUNCATE TABLE.

• No WHERE Clause: In respect to the DELETE statement, TRUNCATE TABLE does not allow a WHERE clause. It deletes all records from the table without any restrictions.
• Referential Integrity: If the table is referenced by Foreign keys from other tables, then one has to ensure that truncating the table does not break any referential integrity constraints. There are times when we would have to disable Foreign key checking.
• Resetting Auto-Increment Values: While truncating the table resets the auto-increment counter, however, it is important to note that such an operation cannot undone it. Make sure that resetting the counter is consistent with our database structure and application needs.

Conclusion

In MariaDB, TRUNCATE TABLE command is significantly important and remove all records in the table. Its good performance feature, low use of resources, and auto-increment reset capabilities make it an best option for some cases. We have saw in the advantage that TRUNCATE TABLE is faster than DELETE. But there is a limitations too which is it removes all records but DELETE can’t. While truncating the table it preserve the structure of Table.