Summary: this tutorial shows you how to use the SQL DROP TABLE statement to remove a table in the database.
Introduction to SQL DROP TABLE statement
As the database evolves, we will need to remove the obsolete and redundant tables from the database. To delete a table, we use the DROP TALE statement.
The following illustrates the syntax of the DROP TABLE statement.
DROP TABLE [IF EXISTS] table_name;
To drop an existing table, you specify the name of the table after the DROP TABLE clause. If the table that is being dropped does not exist, the database system issues an error.
To prevent the error of removing a nonexistent table, we use the optional clause IF EXISTS. If we use the IF EXISTS option, the database system will not throw any error if we remove a non-existent table. Some database systems throw a warning or a notice instead.
Unfortunately, not all database systems support the IF EXISTS option. The ones that do support the IF EXISTS option are MySQL, PostgreSQL, SQL Server 2016, etc.
The DROP TABLE statement removes the both data and structure of a table permanently. Some database systems require the table must be empty before it can be removed from the database. This helps us prevent from accidentally deleting a table that is still in use.
To delete all data in a table, you use either DELETE or TRUNCATE TABLE statement.
In addition to removing the table, the DROP TABLE statement also removes all the objects associated with the table such as triggers, indexes, and constraints.
To drop a table that is referenced by a foreign key constraint of another table, you must remove the foreign key constraint first before removing the table.
SQL DROP TABLE examples
Let’s create a new table for practicing the DROP TABLE statement.
The following statement creates a new table named emergency_contacts that store the emergency contacts of employees.
CREATE TABLE emergency_contacts (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
relationship VARCHAR(50) NOT NULL,
employee_id INT NOT NULL
To remove the emergency_contacts table, you use the following statement:
DROP TABLE emergency_contacts;
Dropping multiples tables
It is possible to delete multiple tables using a single DROP TABLE statement. To do this, you specify a list of comma-separated tables after the DROP TABLE clause as follows:
DROP TABLE table_name1,table_name2,...;
The database system will delete all tables one by one.
In this tutorial, we have shown you how to drop one or more tables using the SQL DROP TABLE statement.