SQL DROP TABLE Statement

Database tables are some of the fundamental and essential building blocks for any relational database. They layout the foundation to set up the rows and columns in which we add the data and associated attributes.

However, as common as the process of creating the tables in relational databases is deleting them. In SQL, we have access to the DROP TABLE statement which allows us to remove an existing table and all associated data from the database.

Dropping a table is a common task in the database when dealing with unused data, restructuring new schema, and more.

In this tutorial, we will learn all about the SQL DROP TABLE statement. We will learn all the basics and advanced features such as dropping multiple tables, coalescing, and more.

Points to Note

For this tutorial, we will use the following tools:

MySQL 8.0
Sakila sample database
DBeaver Community as the IDE of choice

It is good to keep in mind that the DROP BY clause is extremely dangerous and irreversible. Be cautious when dropping any table from the database.

Also ensure to have backups of your database and DO NOT RUN in production unless you are absolutely necessary. We do not take responsibility for any damage caused by the instructions provided in this post.

SQL DROP TABLE

The SQL DROP TABLE statement is a part of the Data Definition Language which is commonly known as the DDL query. DDL statements such as DROP TABLE are used to modify the structure of a given database object.

The following shows the syntax of the DROP TABLE clause in SQL:

DROP TABLE table_name;

Here, the table name represents the name of the table that you wish to drop.

Basic DROP TABLE

The most straightforward way to drop a table is using the basic DROP TABLE statement. Consider a basic example as follows:

DROP TABLE countries;

In this example, we use the DROP TABLE clause to remove the “countries” table including the stored data, table structure, and all associated objects.

Conditional DROP TABLE

Depending on the database engine, we can introduce a conditional drop where we remove a table only if it exists. This removes any potential errors that might arise if the table is missing.

In MySQL, we can add the IF EXISTS clause as follows:

DROP TABLE IF EXISTS table_name;

This query removes the specified table if it exists, and there’s no error if the table doesn’t exist.

Drop Multiple Tables

We can also drop multiple tables in a single SQL statement by separating them with commas. An example syntax is as follows:

DROP TABLE table1, table2, table3;

This removes the tables from the database.

CASCADE and RESTRICT

By default, some databases might prevent you from dropping a table if it has dependencies like foreign key constraints. However, we can control this behavior using the CASCADE and RESTRICT clauses.

The CASCADE clause allows us to automatically drop a table and all associated objects. This ensures that there are no hanging dependent objects which is very useful in database cleaning.

Example is as follows:

DROP TABLE table_name CASCADE;

This drops the specified table and any other dependent objects like views, triggers, or foreign keys.

The opposite of CASCADE is RESTRICT. Unlike CASCADE, RESTRICT prevents dropping the table if it has any dependency.

An example is as follows:

DROP TABLE table_name RESTRICT;

Conclusion

In this tutorial, we learned about the SQL DROP TABLE statement which allow us to remove an existing table from the database.