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 a variety of 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 Statement

The DROP TABLE statement is used to drop 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]

You can also specify the IF EXISTS clause to drop the table only if it exists. If the IF EXISTS clause is not provided while dropping a non-existent table, it will throw an error.

The TEMPORARY keyword has the following effects:

• When the TEMPORARY keyword is specified, the statement drops only temporary tables.
• When the TEMPORARY keyword is specified, the statement doesn’t cause any implicit commits.

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:

Initial Data

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

Initial Data

The tables that we have created till are all permanent tables, i.e. they will be persisted even after the session is closed. Now lets create a temporary table. The temporary tables are not persisted and are only present till 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

Initial Data

Examples of MySQL DROP Table

Example 1: 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.

Example 2: Dropping Multiple Tables and Handling Subsequent Query Errors

In this example lets try to delete multiple table. The following query drops the tables EMPLOYEE, 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 a 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.

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

In this example we will try to drop a table which 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 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 DROP TABLE statement as once committed there is no way to retrieve the data back.