MySQL DROP TABLE Statement

The DROP TABLE statement in MySQL is a Data Definition Language (DDL) command that permanently removes an existing table from a MySQL database.

When you execute the DROP TABLE statement, MySQL performs the following operations:

• Deletes the table structure permanently.
• Removes all records stored in the table.
• Deletes all indexes associated with the table.
• Removes all constraints defined on the table.
• Frees the storage space occupied by the table.

Unlike the DELETE statement, the DROP TABLE statement removes the entire table, including its structure and data, instead of deleting only the rows.

Once a table is dropped, you cannot recover it unless you have a valid backup. Therefore, you should use this statement carefully.

Syntax of MySQL DROP TABLE

The basic syntax to use the DROP TABLE statement in MySQL is:

DROP TABLE table_name;

In this syntax, the DROP TABLE specifies that you want to delete a table. The table_name specifies the name of the table to remove.

How does DROP TABLE Work in MySQL?

When MySQL executes the DROP TABLE statement, it performs the following actions:

1. Locates the specified table.
2. Deletes the table definition (schema).
3. Removes all rows stored in the table.
4. Deletes all indexes associated with the table.
5. Removes all constraints defined on the table.
6. Deletes any triggers associated with the table.
7. Frees the storage space occupied by the table.
8. Permanently removes the table from the database.

Example of DROP TABLE Statement in MySQL

Create a Table in MySQL

Let’s first create a table in the MySQL database. Run the following SQL query:

CREATE TABLE student (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    course VARCHAR(30)
);

Insert Records

INSERT INTO student
VALUES
(101,'John','Java'),
(102,'Saanvi','Python'),
(103,'Emma','PHP');

Table Structure

Drop a Table

Now we will delete the table named student from the MySQL database using the DROP TABLE statement.

DROP TABLE student;

This statement:

• Deletes the student table.
• Removes all rows stored in the table.
• Deletes all indexes associated with the table.
• Removes the table definition.
• Removes all constraints defined on the table.
• Deletes any triggers associated with the table.
• Frees the storage space occupied by the table.

The table no longer exists in the database.

Verify the Table Has Been Deleted

After executing the DROP TABLE statement, you can verify that the table has been deleted by displaying the list of tables in the current database.

Run the following statement:

SHOW TABLES;

Output:

If the dropped table is the only table in the database, the output may look like this:

Empty set

If the database contains other tables, the student table will simply not appear in the list.

The SHOW TABLES statement displays all tables in the currently selected database. If the database contains no tables, MySQL returns Empty set.

If other tables exist in the database, MySQL lists them, but the dropped student table does not appear, which confirms that it has been successfully deleted.

How to Drop Multiple Tables in MySQL?

To drop multiple tables in MySQL, you can also use the DROP TABLE statement. This statement deletes multiple tables in a database. Suppose the current database contains the following tables:

• student
• teacher
• employee

Execute the following statement:

DROP TABLE student, teacher, employee;

This statement instructs MySQL to delete all three tables in a single operation. When MySQL executes the statement, it deletes the student, teacher, and employee tables. After the statement executes successfully, the student, teacher, and employee tables do not appear in the database.

Note that if any of the specified tables do not exist, MySQL returns an error unless you use the IF EXISTS clause.

Drop a Table Using IF EXISTS Clause

Suppose the table has already been deleted from the database. If you run the DROP TABLE statement again, MySQL generates an error because it cannot find the specified table. To avoid this error, use the IF EXISTS clause with the DROP TABLE statement.

Execute the following statement:

DROP TABLE IF EXISTS student;

If the student table exists, MySQL deletes it. If the student table does not exist, MySQL does not generate an error. Instead, MySQL displays a warning message indicating that the table was not found.

Drop Multiple Tables Using IF EXISTS Clause

Similarly, you can also drop multiple tables using the DROP TABLE with IF EXISTS clause. To delete multiple tables from the database, execute the following statement:

DROP TABLE IF EXISTS student, teacher, employee;

How to Drop a Table from Another Database?

In MySQL, you are not limited to deleting tables from the currently selected database. You can also delete a table from another database by specifying the database name before the table name.

Suppose your MySQL server contains the following two databases:

• school_db
• college_db

Assume that the student table exists in the school_db database. To delete this table, execute the following statement:

DROP TABLE school_db.student;

In this statement:

• DROP TABLE specifies that you want to permanently delete a table.
• school_db is the name of the database that contains the table.
• student is the name of the table to be deleted.
• The dot (.) operator separates the database name from the table name.

When MySQL executes this statement, it locates the student table in the school_db database and deletes the table structure. It removes all rows stored in the table. After the statement executes successfully, the student table no longer exists in the school_db database.

Why Specify the Database Name?

Specifying the database name is useful when:

• You have multiple databases on the same MySQL server.
• You want to delete a table without switching to another database using the USE statement.
• Tables with the same name exist in different databases.

For example, both school_db and college_db may contain a table named student. Using the database name ensures that MySQL deletes the correct table. If you execute this statement:

DROP TABLE school_db.student;

MySQL only deletes the student table in the school_db database. The student table in the college_db database remains unchanged.

Best Practices for Using the DROP TABLE Statement

Follow these best practices before using the DROP TABLE statement:

• Always verify the table name before executing the statement.
• Create a backup before deleting important tables. The DROP TABLE statement permanently removes the table and all the data stored in it. You cannot recover a dropped table unless you have a valid backup.
• Use the IF EXISTS clause whenever possible because it prevents an error if the specified table does not exist.
• Check for foreign key relationships before dropping a table. If another table references it through a foreign key constraint, MySQL may prevent the operation.
• Make sure the table is no longer required by your application or other users before deleting it.

When Should You Use MySQL DROP TABLE Statement?

You should use the MySQL DROP TABLE statement when:

• A table is no longer needed.
• Removing temporary tables after they are no longer required.
• Cleaning up development or testing databases.
• Deleting obsolete or unused tables.
• Recreating a table with a new structure from scratch.

Avoid using the DROP TABLE statement if you only want to remove the records from a table. In that case, you should use the DELETE statement to remove specific or all rows while keeping the table structure, or use the TRUNCATE TABLE statement to remove all rows while preserving the table structure.

In the next tutorial, we will learn about the MySQL TRUNCATE TABLE statement.