Online College Courses for Credit

+
DROP TABLE to Remove Tables

DROP TABLE to Remove Tables

Rating:
(0)
Author: Sophia Tutorial
Description:

Identify a correctly constructured DROP TABLE statement given an existing data set.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using the DROP TABLE statement to remove a table in two parts:

  1. Getting Started
  2. Options

1. Getting Started

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.

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

File:11262-1280-1.png

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;

File:11263-1280-2.png

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.

File:11264-1280-3.png

We should be able to now drop them both. Notice that we have a semi-colon 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 ran commands for each individual statement.

DROP TABLE invoice_line;
DROP TABLE playlist_track;

File:11265-1280-4.png

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:

File:11266-1280-5.png

As they do not, we can go ahead to 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:

File:11267-1280-5b.png

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 4 separate sets of statements with first dropping the tables that had no foreign keys linked to them:

DROP TABLE invoice_line;
DROP TABLE playlist_track;

Then dropping the tables that only had foreign keys to those tables that were dropped:

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

Then dropping the tables that had foreign keys to those tables dropped:

DROP TABLE album;
DROP TABLE customer;

Lastly, we had 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. 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:

File:11268-1280-7.png

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;

File:11269-1280-8.png

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

DROP TABLE IF EXISTS customer;

File:11270-1280-9.png

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.

summary
The DROP TABLE command is used to remove tables from the database.