MariaDB - DROP and TRUNCATE TABLE

MariaDB- DROP TABLE

In MariaDB, we can delete the tables from the database using the DROP TABLE command.

Following is the Syntax:

DROP [TEMPORARY] TABLE [IF EXIST] table_name [, table_name, ..];

• We use the TEMPORARY keyword when we wish to drop only temporary tables
• We can delete one or many tables using a single DROP TABLE statement.
•  To perform this operation, we need DROP Privilege.
•  All the data and the definitions related to that table and the triggers related to that table are deleted; we need to use this statement carefully.
• If we mention the table name in the list that does not exist, then MariaDB returns an error and drops all the tables mentioned in the table list.

The IF EXISTS clause is optional. It deletes the table if it exists, and if the table does not, it returns a note instead of an error.

• We can also specify the table name as database_name.table_name; it helps us delete tables from multiple databases.
• We need DROP Privilege only to delete non-temporary tables; we do not need DROP privilege to delete temporary tables.
• If the foreign key is referenced to the table, we delete the table that cannot be deleted. To do this, first, we need to drop the foreign key

Syntax to drop Multiple Tables

DROP TABLE [IF EXISTS] table_1, table_2….;

We can list the table name separated by a comma.

While using this command, we should keep one thing in mind, i.e., MariaDB will hold on to all user privileges on the dropped tables. In this respect, if we create a new table with the name same as dropped table, all the privileges imposed on the dropped table will be applied to the new table. It is a security risk. It would help if you were careful about it.

Let's see the examples:

We will be using already created table named ‘Class10’.

MariaDB [student]> desc Class10;

Let’s delete the table

MariaDB [student]> DROP TABLE CLass10;

Output:

Here we have dropped the table, and to confirm this, we can use the DESC command, which returns an error that shows the dropped table does not exist.

We can also confirm this using SHOW TABLES, and we can see that in the list of tables, ' Class10' is not present.

Example for multiple tables

Initially, We will create two tables names sample and main to delete:

We have used CREATE TABLE command to create table.

MariaDB [student]> SHOW TABLES;

From the above tables, we will delete two tables named sample and main

MariaDB [student]> DROP TABLE sample, main;

To confirm that the tables are dropped we will see the table list

As we can see, dropped tables are not present in the list.

Example for IF EXISTS:

We will try to drop the table named sample, which does not exist. We can see MariaDB returns an error.

MariaDB [student]> Drop table sample;
ERROR 1051 (42S02): Unknown table 'student.sample'
To avoid the error, we can use IF EXISTS
MariaDB [student]> Drop table IF EXISTS sample;
Query OK, 0 rows affected, 1 warning (0.001 sec)

We can see the query is executed successfully without errors, but the warning is generated.

To see the warnings, we use the SHOW WARNINGS command from MariaDB.

We can see the warning is generated which says : Unknown table 'student.sample'  as we are performng drop operation in student database it says student.sample.

MariaDB - TRUNCATE TABLE

We can clear the complete table using the TRUNCATE TABLE command in MariaDB. TRUNCATE removes all rows from the table. To perform this operation, we need DROP privilege.

Syntax:

 TRUNCATE [TABLE] [database_name.]table_name;

• Table_name is to be specified after the truncate table keyword.
• TABLE keyword is optional. Although you should use the keyword to skip the ambiguity between the truncate table statement and the function- truncate().
• We can also mention the table name as database_name.table_name, which is helpful to truncate a table from a particular database.
• Truncate table is similar to delete statement without where clause.
• The Truncate table works a bit differently than the delete statement. It drops and recreates the table other than removing rows one by one; therefore, a truncate table is more efficient than a delete statement. 
• We should carefully use the truncate statement as we cannot undo it; it performs an implicit commitment.
• If there is an AUTO_INCREMENT counter applied to the column, the truncate table command will reset it. 
• Truncation operation does not return the number of rows deleted; instead, it returns "0 rows affected." 

Let's see an example

We will be using a table named 'dept.'

Using the SHOW Tables command, we can see all the tables from the database.

Using SELECT statement, we can retrieve the rows from the table

We can see there are three rows in the table

Let's perform the truncate operation

MariaDB [student]> TRUNCATE TABLE dept;
Query OK, 0 rows affected (0.680 sec)

Here we can see the query returns that rows affected are 0 although we know the rows deleted are 3

We can confirm the truncate operation.

As we can see using SHOW TABLES, we can see the 'dept' table in the list.

We can see the data using the SELECT statement; we can see no data in the table, which means we have successfully truncated the table.