MySQL Drop Table

Last Updated: 28 May, 2023

The DROP TABLE statement is used to drop an existing table from the MySQL database.

This statement permanently deletes all records, indexes, and privileges associated with the table, so before deleting a table, just make sure the table we are going to delete is no longer needed.

The syntax and example for the DROP TABLE statement are as follows:

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE student;

Drop a Table with IF EXISTS option

With the DROP TABLE statement, we can use the IF EXISTS clause to check whether the table exists before attempting to drop it.

If the table exists, then it is dropped; otherwise, no error is thrown. This is useful to avoid errors when attempting to drop a non-existent table.

The syntax and example for using IF EXISTS with DROP TABLE in MySQL are as follows:

Syntax:

DROP TABLE IF EXISTS table_name;

Example:

DROP TABLE IF EXISTS student;

Remove multiple tables

MySQL allows us to remove multiple tables using a single DROP TABLE statement. For removing multiple tables, we need to separate each table by a comma (,).

The syntax and example in MySQL are as follows:

Syntax:

DROP TABLE table_name1, table_name2, table_name3;

Example:

DROP TABLE student, teacher, course;

• To avoid errors during drop table execution, we must ensure that we have the appropriate privileges and that the table we wish to delete exists.
• It's recommended that we always keep a backup of database tables, so if we delete a table by mistake or lose a table or data for some reason, we can recover from the backup.