How to truncate table in MariaDB + Examples

In this MariaDB tutorial, we will understand the usability of the MariaDB Truncate Table statement using various examples. Moreover, we will discuss the following topics.

• How to truncate table in MariaDB
• MariaDB truncate table with foreign key
• MariaDB truncate table where
• MariaDB truncate table if exists
• MariaDB truncate table cascade
• MariaDB truncate table AUTO_INCREMENT
• MariaDB truncate table partition
• MariaDB truncate temporary table
• MariaDB truncate all tables in database
• MariaDB truncate multiple tables
• MariaDB alter table truncate partition

MariaDB Truncate Table

In MariaDB, the TRUNCATE TABLE statement is utilized to simply delete all the data from a MariaDB table. Utilizing the MariaDB TRUNCATE TABLE is quite similar to using the DELETE TABLE statement without a WHERE clause. However, there are certain differences between both the statements

Moreover, when we use the TRUNCATE TABLE statement in MariaDB, instead of deleting the data one by one, it drops and recreated an entire table. Also, it is not possible to roll back the truncate table statement as it produces an implicit commit.

Now, any user in a MariaDB with DROP privilege can easily use the TRUNCATE TABLE statement. Here is how we can grant the DROP privilege on a table to a MariaDB user.

GRANT DROP ON tbl_name TO user;

Now that we understand the use of TRUNCATE TABLE in MariaDB let’s discuss the syntax of this statement.

TRUNCATE [TABLE] tbl_name;

To use the above syntax, we simply need to specify the name of a table in place of the tbl_name keyword. Moreover, here TABLE is an optional keyword while using the TRUNCATE statement.

However, using the TABLE keyword minimizes the potential confusion between the truncate() method and the TRUNCATE TABLE statement.

Now, let’s take an example and understand how to use the TRUNCATE TABLE statement in MariaDB. For this example, we are going to take the USA_STATES table. This table consists of various state names from the United States of America. The table is also shown below.

Now, we will use the following SQL query to truncate the USA_STATES table.

TRUNCATE TABLE USA_STATES;

Once we execute the above query, all the rows from the USA_STATES table will be deleted. And now if try to query the table, it will not return any row.

Also, check: MariaDB Select Statement

MariaDB Truncate Table with Foreign key

In the previous section, we have seen how to use the TRUNCATE TABLE statement in MariaDB. Now, in this section, we will discuss how to truncate a MariaDB table having a foreign key constraint.

MariaDB Truncate Table with Foreign key: Error

But, there is a small issue with this execution. Generally, in MariaDB, we cannot truncate a table that is referenced in a foreign key, as it is not allowed. And the MariaDB server will also return an error. Let’s understand this with the help of an example.

For this example execution, we are going to create two tables with primary and foreign key relations. The SQL query for both tables is given below.

create table country (
	country_id INT PRIMARY KEY,
	country_name VARCHAR(50),
	country_code VARCHAR(50)
);
insert into country (country_id, country_name, country_code) values (1, 'United States', 'US');
insert into country (country_id, country_name, country_code) values (2, 'Canada', 'CA');
insert into country (country_id, country_name, country_code) values (3, 'Australia', 'AU');
insert into country (country_id, country_name, country_code) values (4, 'New Zealand', 'NZ');

First is the COUNTRY table that will consist of 3 columns with country_id as a primary key. And we also inserted 4 country records in the table. After this, we will create the STATE table.

create table state (
	state_id INT PRIMARY KEY,
	state_name VARCHAR(50),
	country_id INT,
	CONSTRAINT fk_country_id
	FOREIGN KEY(country_id) references country(country_id) 
);
insert into state (state_id, state_name, country_id) VALUES (101, NULL, 4);
insert into state (state_id, state_name, country_id) VALUES (102, 'Nevada', 1);
insert into state (state_id, state_name, country_id) VALUES (103, 'California', 1);
insert into state (state_id, state_name, country_id) VALUES (104,'Québec', 2);
insert into state (state_id, state_name, country_id) VALUES (107,'Nunavut', 2);
insert into state (state_id, state_name, country_id) VALUES (120, 'New South Wales', 3);

The STATE table will also consist of 3 columns with state_id as primary key and country_id as a foreign key.

Now, we can easily truncate the STATE table in MariaDB. But, if we try to use the TRUNCATE TABLE statement on the COUNTRY table, the MariaDB server will return the following error.

MariaDB Truncate Table with Foreign key: Solution

Now, there are two ways to execute this task:

1. Using DELETE statement
2. Using TRUNCATE TABLE

The first method is by using the DELETE TABLE statement in MariaDB and resetting the AUTO_INCREMENT value. Here is the sample example for this execution.

DELETE FROM COUNTRY;
ALTER TABLE COUNTRY AUTO_INCREMENT = 1;

The above query will remove all the table data and also reset the AUTO_INCREMENT value to 1.

In the second method, we can try to truncate the table after disabling the foreign key constraint. And after the successful truncation, we could enable the foreign key constraint. The SQL code for this execution is given below.

USE Mariadbtips;

SET FOREIGN_KEY_CHECKS = 0; 

TRUNCATE TABLE COUNTRY;
TRUNCATE TABLE STATE;

SET FOREIGN_KEY_CHECKS = 1;

After executing the above query, the data from both the COUNTRY and STATE tables will be deleted. We can also check it by querying the COUNTRY table.

Read: MariaDB Primary Key

MariaDB Truncate Table Where

In MariaDB, a TRUNCATE TABLE statement is utilized to empty large database tables that consist of thousands of rows. Moreover, a MariaDB TRUNCATE TABLE statement is quite similar to a DELETE statement without a WHERE clause.

So, we cannot use the TRUNCATE TABLE statement with the WHERE clause, as the sole purpose of the TRUNCATE TABLE statement is to delete the entire data of a table. On the other hand, the WHERE clause is utilized to filter certain records.

MariaDB Truncate Table IF EXISTS

Many times when we execute the TRUNCATE TABLE statement on a non-existing table, The MariaDB server will return an error. So, in this section, we will understand how to check the existence of a MariaDB table before performing the TRUNCATE TABLE command.

Now, for the execution of this task, we are going to create a stored procedure in MariaDB that will run the TRUNCATE TABLE statement based upon the existence of the table. Here is the SQL code for the creation of the stored procedure.

USE mariadbtips;
DELIMITER $$

CREATE PROCEDURE `truncatetbl_if_exist`(IN tbl VARCHAR(100) )
  BEGIN
    IF EXISTS(SELECT 1 FROM information_schema.TABLES 
	      WHERE table_name = tbl AND table_schema = DATABASE()) 
	 THEN
    	SET @sqlqry = CONCAT('TRUNCATE TABLE ', tbl);
    	PREPARE sqlstmt FROM @sqlqry;
    	EXECUTE sqlstmt;
    
    ELSE
    	SELECT 'Table does not exist' AS RESULT;
    END IF;
  END $$

DELIMITER ;

In the above SQL code, first, we are creating a procedure named ‘truncatetbl_if_exist‘ with an input parameter named tbl. This tbl parameter will be the name of the table whose existence we want to check.

Within the procedure, we are using the IF EXISTS statement to check the existence of the table using information_schema.TABLES. And based upon existence, we are preparing and executing the TRUNCATE TABLE statement.

Here is an example where are using this procedure to truncate the USA_STATES table.

USE mariadbtips;

CALL truncatetbl_if_exist('USA_STATES');

Now, after executing the above code, we can also check the table data by using the SELECT statement.

However, if the table with the given name does not exist, the server will return a “Table does not exist” result set. Here is an example where we are calling the procedure to truncate the “UNITED_STATES” table.

USE mariadbtips;

CALL truncatetbl_if_exist('UNITED_STATES');

As the UNITED_STATES table does not exist in the mariadbtips database, the instance will return the following result.

Read: MariaDB Rename Column

MariaDB Truncate Table Cascade

In MariaDB, the CASCADE option is utilized in combination with ON DELETE and ON UPDATE in a relational table. With this option, if some rows from a patent table are removed or updated then the rows from the child table will also be deleted or updated.

Issue with MariaDB Truncate Table Cascade

In many databases like PostgreSQL and Oracle, we can use the CASCADE option with the TRUNCATE TABLE statement to also truncate the referencing table. However, MariaDB does not support the use of the CASCADE option with the TRUNCATE TABLE statement.

In addition to this, we cannot truncate a table that is referenced in a foreign key, as it is not allowed. Now even if we try to use the CASCADE with TRUNCATE statement or we try to truncate a table referencing in a foreign key, the instance will return an error.

Let’s understand this with the help of an example. For this, we are going to create two tables with primary and foreign key relations. The SQL query for both tables is given below.

create table country (
	country_id INT PRIMARY KEY,
	country_name VARCHAR(50),
	country_code VARCHAR(50)
);
insert into country (country_id, country_name, country_code) values (1, 'United States', 'US');
insert into country (country_id, country_name, country_code) values (2, 'Canada', 'CA');
insert into country (country_id, country_name, country_code) values (3, 'Australia', 'AU');
insert into country (country_id, country_name, country_code) values (4, 'New Zealand', 'NZ');

First is the COUNTRY table that will consist of 3 columns with country_id as a primary key.

create table state (
	state_id INT PRIMARY KEY,
	state_name VARCHAR(50),
	country_id INT,
	CONSTRAINT fk_country_id
	FOREIGN KEY(country_id) references country(country_id)
	ON DELETE CASCADE

);
insert into state (state_id, state_name, country_id) VALUES (101, NULL, 4);
insert into state (state_id, state_name, country_id) VALUES (102, 'Nevada', 1);
insert into state (state_id, state_name, country_id) VALUES (103, 'California', 1);
insert into state (state_id, state_name, country_id) VALUES (104,'Québec', 2);
insert into state (state_id, state_name, country_id) VALUES (107,'Nunavut', 2);
insert into state (state_id, state_name, country_id) VALUES (120, 'New South Wales', 3);

The STATE table will also consist of 3 columns with state_id as primary key and country_id as a foreign key. Moreover, the foreign key is referencing the primary key column of the COUNTRY table and we have also used the ON DELETE CASCADE option.

Now, if we try to execute the TRUNCATE TABLE statement with the CASCADE option, the instance will return the following error.

MariaDB Truncate Table Cascade: Solution

The only way to use the CASCADA option in MariaDB is by using the DELETE statement. But, instead of using the DELETE statement to delete only a few rows, we can use this statement to delete the entire table data.

Here is an example where we are using the DELETE statement to delete the entire data from the COUNTRY table.

USE mariadbtips;

DELETE FROM country;

After executing the above query, we can check the STATE table by using the SELECT statement. And we can observe that the rows referencing the COUNTRY table are deleted.

Read: MariaDB Order By Clause

MariaDB Truncate Temporary Table

In this section, we will understand how to truncate temporary tables in MariaDB. A temporary table in MariaDB is another type of MariaDB table that allows us to store data temporarily. When the current session ends, the temporary table’s existence comes to an end as well.

Now, before truncating a temporary table in MariaDB, we should know how to create a temporary table. However, creating a temporary table in MariaDB is quite similar to creating a standard MariaDB table. Just, instead of using CREATE TABLE, we need to use CREATE TEMPORARY TABLE statement.

Here is a simple example where we are creating a temporary table with the tmpcountry name.

USE mariadbtips;
create temporary table tmpcountry (
	country_id INT PRIMARY KEY,
	country_name VARCHAR(50),
	country_code VARCHAR(50)
);
insert into tmpcountry (country_id, country_name, country_code) values (1, 'United States', 'US');
insert into tmpcountry (country_id, country_name, country_code) values (2, 'Canada', 'CA');
insert into tmpcountry (country_id, country_name, country_code) values (3, 'Australia', 'AU');
insert into tmpcountry (country_id, country_name, country_code) values (4, 'New Zealand', 'NZ');

After executing the above SQL query, we can use the SELECT statement to query the temporary table.

Now, to truncate the above temporary table, we need to execute the following TRUNCATE TABLE statement.

USE mariadbtips;

TRUNCATE TABLE tmpcountry;

After executing the above SQL query, we can use the SELECT statement to check the data of tmpcountry.

Read: MariaDB Union Operator

MariaDB Truncate Multiple Tables

Till now in this tutorial, we have truncated a single MariaDB table at a time. But, what if a DBA has to truncate multiple tables. So, in this section, we will illustrate a method to execute the TRUNCATE TABLE statement on multiple tables.

In MariaDB, we cannot implement this execution using a single SQL code. So, we will create and use an anonymous block in MariaDB. And the complete query in the block is given below.

USE mariadbtips;

DELIMITER //
	FOR i IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME IN ('USA_STATES','STATE'))
	DO
	SET @sqlQusery = concat('TRUNCATE TABLE ',i.table_name);
	PREPARE sqlstmt FROM @sqlQusery;
	EXECUTE sqlstmt;
	END FOR;
//

In the above query, first, we are using a FOR LOOP with a SELECT statement to fetch two table names. These table names are USA_STATES and STATE. After this, within the loop, we are using the CONCAT function to create a TRUNCATE TABLE command. And in the last, we are executing the SQL code.

After execution, if we check the table content for USA_STATES and STATE table, we will get the following result.

Read: MariaDB Set Variable

MariaDB Truncate All Tables in Database

From the previous section, we learned how to truncate more than one table at once in MariaDB. But what if we want to truncate all the tables from a database. So, in this section, we will illustrate how to truncate all the tables with a given MariaDB database.

• For this illustration, first, we will disable the foreign key constraints.
• After this, we will try to fetch all the table names from a database using the information_schema.tables view in MariaDB.
• And then, we will use the table names to prepare and execute the TRUNCATE TABLE statement.
• In the end, we will enable the foreign key constraints.

Here is the syntax of the above implementation.

SET FOREIGN_KEY_CHECKS = 0;

SELECT @sql_query := CONCAT('TRUNCATE TABLE ', table_name, ';')
FROM   information_schema.tables
WHERE  table_type   = 'BASE TABLE'
  AND  table_schema IN ('database_name');

PREPARE sql_stmt FROM @sql_query;

EXECUTE sql_stmt;

DEALLOCATE PREPARE sql_stmt;

SET FOREIGN_KEY_CHECKS = 1;

In this syntax, we just need to specify the name of the database instead of the database_name keyword. And it will create and execute the TRUNCATE TABLE statement for all the tables in the specified database.

Now, let’s use the above syntax to truncate all the tables from the mariadbtips database. The SQL script for this task is given below.

SET FOREIGN_KEY_CHECKS = 0;

SELECT @sql_query := CONCAT('TRUNCATE TABLE ', table_name, ';')
FROM   information_schema.tables
WHERE  table_type   = 'BASE TABLE'
  AND  table_schema IN ('mariadbtips');

PREPARE sql_stmt FROM @sql_query;

EXECUTE sql_stmt;

DEALLOCATE PREPARE sql_stmt;

SET FOREIGN_KEY_CHECKS = 1;

When we execute the above query, the instance will also give a resultset with all the TRUNCATE TABLE statements. And also executes these statements.

Read: MariaDB Regexp + Examples

MariaDB Truncate Table AUTO_INCREMENT

In this section, we will understand what will happen when we use the TRUNCATE TABLE statement on a MariaDB table having AUTO_INCREMENT column.

In MariaDB, whenever we use the TRUNCATE TABLE command on a table with AUTO_INCREMENT column, the AUTO_INCREMENT value gets automatically reset.

Let’s understand this with the help of an example. First, we will create a MariaDB table with an AUTO_INCREMENT column.

create table usa_state_tbl (
  id INT AUTO_INCREMENT PRIMARY KEY,
  state_name VARCHAR(64) NOT NULL,
  state_code CHAR(2) NOT NULL
);

insert into usa_state_tbl (state_name, state_code) values ('Alabama','AL');
insert into usa_state_tbl (state_name, state_code) values ('Alaska','AK');
insert into usa_state_tbl (state_name, state_code) values ('American Samoa','AS');
insert into usa_state_tbl (state_name, state_code) values ('Arizona','AZ');
insert into usa_state_tbl (state_name, state_code) values ('Arkansas','AR');
insert into usa_state_tbl (state_name, state_code) values ('California','CA');

In the above query, we have created a table named usa_state_tbl. This table consists of 3 columns out of which id is AUTO_INCREMENT. In the end, we have also inserted 6 rows in the table.

Now, if we use the SELECT statement on the above table, we will get the following result.

From the above output, we can observe that the value for the id column is automatically generated starting from 1. Now, let’s use the TRUNCATE TABLE statement on this table.

USE mariadbtips;

TRUNCATE TABLE usa_state_tbl;

By executing the above SQL query, the data of usa_state_tbl will be deleted and the value for AUTO_INCREMENT will get reset. Now, let’s insert some rows in the table using the below query.

insert into usa_state_tbl (state_name, state_code) values ('Alabama','AL');
insert into usa_state_tbl (state_name, state_code) values ('Alaska','AK');
insert into usa_state_tbl (state_name, state_code) values ('American Samoa','AS');

After data insertion, if we query the table again, we can observe that the value for the id column is starting from 1 again.

Read: MariaDB Case Statement

MariaDB Truncate Table Partition

In MariaDB, just like truncating the entire table, we can also truncate a particular table partition. So, in this section, we will understand how to create and then truncate a MariaDB table partition.

In MariaDB, table partitioning is a technique of dividing tables with huge data into several smaller pieces. With this process, we simply divide a huge set of data into small portions and execute queries against these small portions. This results in faster query execution as the query only have to look at a small portion of data.

In MariaDB, we can create table partitions of several different types. However, this section will focus on truncating a partition of any type. And for this illustration, we will first create a table partition, insert some data in that partition, and then truncate that partition.

The SQL query to create a table with a partition is shown below.

CREATE TABLE user_log
(
	user_id INT NOT NULL AUTO_INCREMENT,
	timestamp DATETIME NOT NULL,
	username VARCHAR(50),
	ip_address BINARY(16) NOT NULL,
	PRIMARY KEY (user_id, timestamp)
)
PARTITION BY RANGE (YEAR(timestamp))
(
	PARTITION part0 VALUES LESS THAN (2020),
	PARTITION part1 VALUES LESS THAN (2022)
);

insert into user_log (timestamp, username, ip_address) values ('2021-04-23', 'kradsdale0', '108.247.148.73');
insert into user_log (timestamp, username, ip_address) values ('2020-04-24', 'tconew1', '6.135.102.89');
insert into user_log (timestamp, username, ip_address) values ('2021-09-04', 'sgilbanks2', '122.150.55.26');
insert into user_log (timestamp, username, ip_address) values ('2020-07-14', 'abrach3', '243.38.56.27');
insert into user_log (timestamp, username, ip_address) values ('2018-08-17', 'mpollett4', '152.17.219.125');
insert into user_log (timestamp, username, ip_address) values ('2021-06-28', 'nwalentynowicz5', '117.191.222.28');
insert into user_log (timestamp, username, ip_address) values ('2019-02-15', 'pbannester6', '120.3.190.108');
insert into user_log (timestamp, username, ip_address) values ('2019-03-12', 'yeveral7', '78.93.176.138');
insert into user_log (timestamp, username, ip_address) values ('2021-05-05', 'bgentzsch8', '190.32.5.63');
insert into user_log (timestamp, username, ip_address) values ('2018-12-19', 'cdurward9', '236.23.84.51');

In the above query, first, we have created a table name user_log with 4 columns. And we have also partitioned the table based upon the year value in the timestamp column. So, we created 2 partitions, part0, and part1.

After creating the table and partitions, we have inserted 10 rows into the table. Here is the table with complete data.

Now, to truncate any table partition in MariaDB, we need to use the ALTER TABLE statement. Here is the complete syntax of using the ALTER TABLE TRUNCATE PARTITION statement.

ALTER TABLE tbl_name
TRUNCATE PARTITION partition_name;

In the above syntax, we simply need to provide the table name in place of the tbl_name keyword and specify the partition name in place of partition_name.

Let’s use the above syntax to truncate the part0 partition from the user_log table. And the query for this task is given below.

ALTER TABLE user_log
TRUNCATE PARTITION part0;

Now, if we query the user_log table, we can easily observe that the part0 partition data have been removed from the table.

You may also like to read the following MariaDB tutorials.

• MariaDB on Duplicate Key Update
• MariaDB Comment – How to use
• MariaDB Row_Number Tutorial
• MariaDB Enable Remote Access
• MariaDB Logs – Helpful Guide
• MariaDB Create Sequence + Examples

So, in this tutorial, we have learned how to use the MariaDB Truncate Table statement. Additionally, we have covered the following set of topics.

• MariaDB truncate table
• MariaDB truncate table with foreign key
• MariaDB truncate table where
• MariaDB truncate table if exists
• MariaDB truncate table cascade
• MariaDB truncate table AUTO_INCREMENT
• MariaDB truncate table partition
• MariaDB truncate temporary table
• MariaDB truncate all tables in database
• MariaDB truncate multiple tables
• MariaDB alter table truncate partition