How to permanently delete tables using MariaDB DROP TABLE

If you need to delete one or more tables using the free database management system, the DROP TABLE command in MariaDB is the appropriate choice. However, since this action is permanent, it’s important to use the command with great caution, as it will remove both the table and all of its data.

Requirements and syntax

To delete a table, you need the corresponding user rights. You can obtain these either as an admin or by creating a new user with MariaDB CREATE USER.

The syntax of DROP TABLE in MariaDB is as follows:

DROP TABLE Name_of_table;

Replace the placeholder ‘Name_of_table’ with the actual table name.

If you try to remove a table that has either already been deleted or was never in the database, you’ll encounter an error message. To avoid this, MariaDB provides the IF EXISTS option for the DROP TABLE command. This option checks if the specified table exists in the system. If it does, the table is deleted without requiring any additional steps. If the table doesn’t exist, you’ll receive only a warning, and no further actions will be taken. The command with this option looks like this:

DROP TABLE IF EXISTS Name_of_table;

How to use DROP TABLE in MariaDB

The functionality of DROP TABLE in MariaDB can be best demonstrated with a simple example. Suppose you’ve created a database called ‘Tasks’ using the MariaDB CREATE DATABASE command. Within this database, you’ve added several tables using the MariaDB CREATE TABLE statement. However, you no longer need the table ‘Tasks_2023’ and want to delete it permanently. To do this, click on the appropriate database and execute the following command:

DROP TABLE IF EXISTS Tasks_2023;

The table and all data stored in it will now be removed.

How to delete multiple tables

It is also possible to delete several tables at the same time. These are separated from each other by commas. This is what a practical example would look like:

DROP TABLE IF EXISTS Tasks_2023, Tasks_2022, Tasks_2021;

How to delete temporary tables

It is also possible to use DROP TABLE in MariaDB to get rid of a temporary table. For our example from above, the command would then look like this:

DROP TEMPORARY TABLE IF EXISTS Tasks_2023;

In this case, the system checks whether there is a temporary table called ‘Tasks_2023’. If this is the case, it is deleted. If this is not the case or if the table is not temporary, it is not deleted.