DROP TABLE to Remove Tables

1. Introduction

A table can be removed from the database by using the DROP TABLE command. It is one of the easier statements to write but it can get a bit more complex when considering relationships between tables. The structure of the statement looks like the following:

DROP TABLE <tablename>;

In a relationship between tables, you can only drop a table if it is not the “one” side of a one-to-many relationship. We will get into more specifics about table relationships later in the course, but it is important to be aware of the details. If we try to drop a table that has an existing relationship, we will get an error. The order in which we drop tables depends on the foreign key constraints.

2. Examples

Let us look at our database. We have the following foreign keys set up across the various tables:

We can see that the album table has the artist_id as a foreign key to the artist table’s artist_id. If we tried to run a DROP TABLE on the artist table, we should get the following error:

DROP TABLE artist;

It can be a bit of a puzzle to identify the order in which the tables need to be dropped. To do so, we want to first identify the tables that do not have a foreign key. This will include the invoice_line table and the playlist_track table.

We should be able to now drop them both. Notice that we have a semicolon at the end of each line as they are viewed as separate commands that are run sequentially. As such, the query results will also include multiple successfully run commands for each individual statement.

DROP TABLE invoice_line;
DROP TABLE playlist_track;

Next, we can then look at the tables that they initially had referenced to see if they have any relationships that still remain as foreign keys:

As they do not, we can go ahead and drop those tables as well:

DROP TABLE invoice;
DROP TABLE track;
DROP TABLE playlist;

Then, looking at those tables, we can track down which tables had a reference on them:

Although we have the album, artist, customer, employee, genre, and media_type tables left, if we drop the album and customer tables, we can drop the rest, as the employee table is linked to itself.

We can drop all of the tables by running the following:

DROP TABLE album;
DROP TABLE customer;
DROP TABLE employee;
DROP TABLE artist;
DROP TABLE genre;
DROP TABLE media_type;

As a recap, we could break down the order of the dropping of the tables into four separate sets of statements, starting with dropping the tables that had no foreign keys linked to them:

DROP TABLE invoice_line;
DROP TABLE playlist_track;

Next, we drop the tables that only had foreign keys to those tables that were dropped:

DROP TABLE invoice;
DROP TABLE track;
DROP TABLE playlist;

Then, we drop the tables that had foreign keys to those tables dropped:

DROP TABLE album;
DROP TABLE customer;

Lastly, we proceed with the rest of the tables that could be dropped as they no longer had any foreign keys that held them back from being dropped:

DROP TABLE employee;
DROP TABLE artist;
DROP TABLE genre;
DROP TABLE media_type;

2. DROP TABLE Options

Another approach to avoid having to go through each of the steps is to use the DROP TABLE with the CASCADE option. This will drop the table but also remove any constraints that link to the table. For example, if we tried to drop the CUSTOMER table without any options:

DROP TABLE customer;

We should get the following error as the customer_id is used in the invoice table:

By running this command:

DROP TABLE customer CASCADE;

This drops the table but also removes the foreign key constraint on the invoice table for the customer_id.

Another option with the DROP TABLE statement is the IF EXISTS option. If you try to drop a table that doesn’t exist or has already been dropped, you will get an error:

DROP TABLE customer;

However, adding the IF EXISTS will allow you to still run the command:

DROP TABLE IF EXISTS customer;

This can be useful when we include multiple commands together and do not want the database to stop on an error.

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.