abdou / classroom
Back to MIS 3220

SQL Lesson 18: Dropping Tables

Learn how to remove tables from your database using DROP TABLE statements.

Loading Database...

SQL Lesson 18: Dropping tables

In some rare cases, you may want to remove an entire table including all of its data and metadata, and to do so, you can use the DROP TABLE statement, which differs from the DELETE statement in that it also removes the table schema from the database entirely.

DROP TABLE
IF EXISTS mytable;

Like the CREATE TABLE statement, the database may throw an error if the specified table does not exist, and to suppress that error, you can use the IF EXISTS clause.

In addition, if you have another table that is dependent on columns in table you are removing (for example, with a FOREIGN KEY dependency) then you will have to either update all dependent tables first to remove the dependent rows or to remove those tables entirely.

Danger Zone

WARNING: DROP TABLE is a destructive operation that permanently removes both the table structure and all its data. Unlike DELETE, which only removes rows, DROP TABLE removes the entire table from the database. Always make sure you have backups and that you really want to remove the table completely.

When to use DROP TABLE

  • Removing temporary tables: Tables created for temporary data processing
  • Database cleanup: Removing unused or obsolete tables
  • Schema changes: When restructuring the database design
  • Development/testing: Cleaning up test tables
  • Migration rollbacks: Removing tables that were added in error

Best practices

  • Always backup first: Create backups before dropping important tables
  • Check dependencies: Ensure no other tables reference the table you're dropping
  • Use IF EXISTS: Prevents errors if the table doesn't exist
  • Test in development: Always test DROP operations in a safe environment first
  • Document changes: Keep track of schema changes for team coordination

Exercise

We've gone ahead and added a few tables to the Pixar database, but it turns out that most of them aren't going to be used in the application, so we need to clean them up.

Current Database Tables

Query results

Run a query to see results

Exercise 18 — Tasks

1
We've gone ahead and created a new table in this database called temp_data, but realized that it might not be needed. Write a query to remove this table.
Hint: Use DROP TABLE to remove an entire table
2
The movie studio has also decided that they are not ready to use the directors table for their application, so go ahead and remove it as well.
3
And drop the genres table as well since it's not being used by the application.

Solve all tasks to continue to the next lesson.

Progress0 of 3 tasks completed