SQL DROP TABLE for Beginners

Posted on by Ian

In SQL, if you want to remove a table from a database, you need to use the DROP TABLE statement.

That destroys the table and all its data.

Syntax

The SQL standard syntax goes like this:

DROP TABLE <table name> <drop behavior>

Where:

  • <table name> is the name of the table you want to drop.
  • <drop behavior> specifies any options. These can be either CASCADE or RESTRICT.

Some RDBMSs also accept an optional IF EXISTS argument which means that it won’t return an error if the table doesn’t exist.

Some RDBMSs (such as MySQL and MariaDB) also accept an optional TEMPORARY keyword to ensure only temporary tables are dropped.

Oracle also accepts a PURGE clause, which purges it from the recycle bin.

Example

Here’s an example to demonstrate.

DROP TABLE t1;

Running that code drops the table called t1 and all its data.

The IF EXISTS Clause

Here’s an example of using the IF EXISTS clause to check if the table already exists.

DROP TABLE IF EXISTS t1;

Using IF EXISTS ensures that we don’t get an error if the table doesn’t exist.

Here’s what happens if we remove IF EXISTS from the statement:

DROP TABLE t1;

Result:

Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 't1', because it does not exist or you do not have permission.

That is the message returned by SQL Server. Your message will depend on the DBMS that you’re using.

Dependent Foreign Keys & Views

Some RDBMSs allow an optional RESTRICT or CASCADE keyword that specifies what happens if the table has any foreign keys or views that reference it.

The RESTRICT Option

Here’s an example of using RESTRICT when trying to drop a table that is referenced by a foreign key in another table:

DROP TABLE t1 RESTRICT;

Result:

cannot drop table t1 because other objects depend on it

This example was done using PostgreSQL. RESTRICT is the default option, so we would have received the same result even if we hadn’t included the RESTRICT keyword.

The CASCADE Option

Here’s what happens if we switch over to CASCADE when trying to drop the same table (which is referenced by a foreign key in another table):

DROP TABLE t1 CASCADE;

Result:

NOTICE:  drop cascades to constraint t2_c2_fkey on table t2
Commands completed successfully

This dropped the foreign key that was referencing our t1 table. The foreign key was called t2_c2_fkey.

Note that it didn’t drop the table that had the foreign key. It only dropped the foreign key.

If the target table (t1) was referenced by any views, the whole view would have been dropped.

You don’t need to specify CASCADE to drop any indexes, rules, triggers, or constraints that exist for the target table. These are dropped automatically, even when using the default option (RESTRICT).

MySQL and MariaDB

Some DBMSs (such as MySQL and MariaDB) accept the RESTRICT and CASCADE keywords, but they don’t do anything. They are provided simply for easier portability between DBMSs.

Oracle

Oracle has a slightly different syntax, CASCADE CONSTRAINTS, which drops all referential integrity constraints that refer to primary and unique keys in the dropped table.

SQL Server

SQL Server doesn’t support the CASCADE or RESTRICT keywords. If the table has any foreign key dependencies, you’ll need to drop them before dropping the table, otherwise you’ll get an error.

However, in SQL Server, you can drop a table even if it’s referenced by a view or stored procedure. Therefore, you should check for any such references, and explicitly drop them by using DROP VIEW or DROP PROCEDURE.

SQLite

SQLite doesn’t support the CASCADE or RESTRICT keywords.

If the target table is referenced by any views, the table will still be dropped (and the view will remain).

If the target table is referenced by any foreign keys, the outcome will depend on whether you have foreign keys enabled, and if so, whether there’s any data in the child table, and if so, whether or not the foreign key is defined with ON DELETE CASCADE.

If you use SQLite, see SQLite DROP TABLE for an example and discussion of dropping a table that’s referenced by a foreign key.

Drop Multiple Tables

Some RDBMSs allow you to drop multiple tables from a single DROP TABLE statement.

Example:

DROP TABLE t11, t12;

The RDBMSs that support this syntax include SQL Server, MySQL, MariaDB, and PostgreSQL.

However, if you’re dropping a table that’s referenced by a foreign key, and that foreign key prevents it from being dropped, you’ll need to list the child table before the parent table.

For example, if I run the above statement in SQL Server, I get the following error:

Msg 3726, Level 16, State 1, Line 1
Could not drop object 't11' because it is referenced by a FOREIGN KEY constraint.

In this case, I can simply switch the order of the tables in my DROP TABLE statement:

DROP TABLE t12, t11;

Actually, in this case, I got another error telling me that t12 doesn’t exist.

Here’s what I got:

Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 't12', because it does not exist or you do not have permission.

This is because, even though the previous statement couldn’t drop t11, it was in fact successful in dropping t12.

And just like a comedy of errors, this time it was able to drop t11 but not t12.

Regardless, both tables have now been now dropped.

But if you get the order correct the first time, you should receive a message like this:

Commands completed successfully.

The TEMPORARY Keyword

Some RDBMSs (such as MySQL and MariaDB) accept a TEMPORARY keyword.

It goes between DROP and TABLE, like this:

DROP TEMPORARY TABLE t1;

Using the TEMPORARY keyword will ensure that you don’t accidentally drop a non-temporary table when attempting to drop a temporary table.

The TEMPORARY keyword has the following effects:

  • The statement drops only TEMPORARY tables.
  • The statement does not cause an implicit commit (using DROP TABLE without the TEMPORARY keyword automatically commits the current active transaction).
  • No access rights are checked. A TEMPORARY table is visible only with the session that created it, so no check is necessary.

The Purge Clause

Oracle has an optional PURGE clause, which you can use if you want to drop the table and release the space associated with it in a single step. If you specify PURGE, then the database does not place the table and its dependent objects into the recycle bin.

This is equivalent to first dropping the table and then purging it from the recycle bin, but it lets you save one step in the process.

Note that if you specify PURGE, you won’t be able to recover the table.

If you don’t specify PURGE, the DROP TABLE statement does not result in space being released back to the tablespace for use by other objects, and the space continues to count toward the user’s space quota.