MySQL DROP TABLE Statement

MySQL is a free and open-source relational database management system written in C and C++ that is extremely popular among developers. Like other relational database management systems, MySQL provides various rich features to create databases and tables, insert data in them, and further manipulate them as the system evolves. In this article, we are going to have a look at one such functionality, the DROP TABLE statement.

DROP TABLE in MySQL

The DROP TABLE statement drops one or more existing tables from the database. It removes the table definition and all the data inside the table, so be careful while using this statement.

Syntax:

DROP [TEMPORARY] TABLE [IF EXISTS]

tbl_name [, tbl_name] ...

[RESTRICT | CASCADE]

Parameters:

• IF EXISTS: This optional clause ensures that no error is thrown if the table does not exist.
  
• table_name: The name of the table you want to delete. Multiple tables can be specified, separated by commas.
  
• RESTRICT | CASCADE: These optional keywords are used to determine the behavior if there are dependencies. In MySQL, these keywords are recognized but not enforced. The default behavior is to drop the table even if there are dependencies.

Use the DROP Statement to Remove a Table

Let's start by creating some tables and inserting records in it. We will later use these tables in the subsequent examples to understand the DROP TABLE statement better.

The first table we are going to create is the EMPLOYEE table. The following query creates the table and then later inserts records in it.

-- create employee table
CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);

-- insert into employee table
INSERT INTO EMPLOYEE VALUES (1, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (2, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (3, 'Ava', 'Sales');

We will run the following query to fetch the initial data in the table:

SELECT * FROM EMPLOYEE;

The following is the initial data in the table:

The second table, we are going to create is the MANAGER table. The following query creates the table and then later inserts records in it:

-- create manager table
CREATE TABLE MANAGER (
  managerId INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

-- insert into manager table
INSERT INTO MANAGER VALUES (99, 'Jake');
INSERT INTO MANAGER VALUES (98, 'Smith');
INSERT INTO MANAGER VALUES (97, 'Lucy');

We will run the following query to fetch the initial data in the table:

SELECT * FROM MANAGER;

The following is the initial data in the table

The tables that we have created are all permanent tables, i.e. they will be persisted even after the session is closed. Now let's create a temporary table. The temporary tables do not persist and are only present for the duration of the current session. We will create a temporary table ORGANISATION using the records we already have in the EMPLOYEE and MANAGER tables. The following query creates the table and populates it with records:

-- create temporary table organisation
CREATE TEMPORARY TABLE ORGANISATION 
AS
SELECT empId, name FROM EMPLOYEE
UNION ALL
SELECT managerId, name FROM MANAGER;

We will run the following query to fetch the initial data in the table:

SELECT * FROM ORGANISATION;

The following is the initial data in the table

Dropping a Temporary Table

Example: Dropping Temporary Table and Handling Non-Existent Table Error

In this example, we will see how we can drop a temporary table. The following query drops the table ORGANISATION that we created above.

DROP TEMPORARY TABLE ORGANISATION;

Note that we used the TEMPORARY keyword to specify to the engine that we are deleting a temporary table.

Now if we run the following query:

SELECT * FROM ORGANISATION;

We will get the following error:

ERROR 1146 (42S02) at line line_num: Table 'db_name.organisation' doesn't exist

Explanation: Dropping the temporary table ORGANISATION using DROP TEMPORARY TABLE removes it from the session. Attempting to query it afterward results in an "ERROR 1146" as the table no longer exists. This underscores the ephemeral nature of temporary tables and the need for careful session-specific management.

Dropping Multiple Tables

Example: Dropping Multiple Tables and Handling Subsequent Query Errors

In this example let's try to delete multiple tables. The following query drops the tables EMPLOYEE and MANAGER tables that we created.

DROP TABLE EMPLOYEE, MANAGER;

Now if we try to use the above tables in any subsequent query we will get an error message.

Explanation: Executing the query DROP TABLE EMPLOYEE, MANAGER; removes both the EMPLOYEE and MANAGER tables. Subsequent attempts to use these tables in queries result in error messages, specifically indicating that the tables no longer exist.

Handling Non-Existent Table with DROP TABLE IF EXISTS

Example: Handling Non-Existent Table with DROP TABLE IF EXISTS

In this example, we will try to drop a table that doesn't exist. The following query tries to drop table STUDENT which does not exist.

DROP TABLE STUDENT;

Once you run this command, you will get the following query.

ERROR 1146 (42S02) at line line_num: Table 'db_name.student' doesn't exist

We can avoid the error message by running the following query:

DROP TABLE IF EXISTS STUDENT

Here we made use of the IF EXISTS keyword. Executing the above query doesn't throw any error.

Explanation: The initial attempt to drop the non-existent table STUDENT results in an "ERROR 1146" as it's not found. Using the DROP TABLE IF EXISTS STUDENT prevents errors by checking for existence before attempting to drop, providing a smooth execution even if the table doesn't exist.

Conclusion

In this article, we went through the DROP TABLE statement. We had a chance to look at the different use cases as well as different keywords that we can use along the statement. As you can see the DROP TABLE statement provides the developer immense power in playing with data and tables. However, one should be careful about using the DROP TABLE statement as once committed there is no way to retrieve the data back.