SQL DROP TABLE

Summary: This tutorial shows you how to use the SQL DROP TABLE statement to remove one or more tables in a 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 TABLE 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.

Notice that not database systems support the IF EXISTS option. The ones that do support the IF EXISTS option are MySQL, PostgreSQL, and SQL Server 2016.

The DROP TABLE statement removes 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 you prevent accidentally deleting a table that is still in use.

To delete all data in a table, you can use either the DELETE or TRUNCATE TABLE statement.

To drop a table that is referenced by a foreign key constraint of another table, you must disable or remove the foreign constraint 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 stores 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
);

The following statement drops the emergency_contacts table:

DROP TABLE emergency_contacts;

SQL DROP TABLE – removing multiples tables

The DROP TABLE the statement allows you to remove multiple tables at the same time. To do this, you need to specify a list of comma-separated tables after the DROP TABLE clause as follows:

DROP TABLE table_name1,table_name2,...;

The database system then deletes all tables one by one.

In this tutorial, you have learned how to drop one or more tables in a database using the SQL DROP TABLE statement.