SQL DROP TABLE: The Complete Guide

How can you delete a table in SQL? Learn how to do this by using the SQL DROP TABLE statement in this article.

This article applies to Oracle, SQL Server, MySQL, and PostgreSQL.

 

How Can I Delete a Table in SQL?

To delete a table in SQL, you run a statement called DROP TABLE.

It’s called DROP because that’s the standard term for deleting objects from a database. The DELETE keyword is used when you want to delete data from a table (or materialized view).

 

What Does the DROP TABLE Statement Look Like?

The syntax for the DROP TABLE command is pretty simple:

DROP TABLE tablename;

First, you write DROP TABLE, and then the name of the table you want to drop.

Each database has its own additions to the SQL DROP TABLE statement.

 

Oracle

DROP TABLE
[schema.] table_name
[CASCADE CONSTRAINTS] [PURGE];

The CASCADE CONSTRAINTS keyword means that all referential integrity constraints with the table will be dropped as well. If there are constraints on the table and you don’t specify this keyword, then an error will be displayed and the table will not be dropped.

If you specify PURGE, it means that the table and its dependent objects will be purged from the recycle bin, and you will no longer be able to recover the table. By default, this is not specified, and the table and objects are moved to the recycle bin and can be recovered if you need to.

 

SQL Server

DROP TABLE [ IF EXISTS ]
{ database_name.schema_name.table_name | schema_name.table_name | table_name } [ ,...n ];

The IF EXISTS keyword lets you only run the command if the table exists.

SQL Server lets you drop multiple tables in one command. You can also specify the database name and schema name, but they are not required.

There is no CASCADE keyword for SQL Server. You’ll need to drop the constraints first, then drop the table.

 

MySQL

DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE];

MySQL lets you use the IF EXISTS keyword to run the command only if the table exists.

You can drop a temporary table by using the TEMPORARY keyword. You can also drop multiple tables with a single statement in MySQL.

There are two additional options at the end of the statement: RESTRICT and CASCADE. They don’t actually do anything, and just exist to make porting to other databases easier.

If you want to remove the constraints that impact this table, you’ll need to drop the constraints first, then drop the table.

If you want to drop all tables in a database, I’ve written about several methods to do that here: MySQL Drop All Tables: How-To With Examples

 

PostgreSQL

DROP TABLE [ IF EXISTS ]
name [, ...]
[ CASCADE | RESTRICT ]

PostgreSQL also includes an IF EXISTS keyword to run the command only if the table exists. You can also drop multiple tables with a single statement in PostgreSQL.

There are two parameters at the end of the statement:

• CASCADE: Automatically drop objects that depend on the table (such as views) and then all objects that depend on those objects.
• RESTRICT: Refuse to drop the table if any objects depend on it. This is the default.

 

What Happens When You Drop a Table in a Database?

There are a few things that happen in Oracle when you drop a table:

• The data is removed and is no longer accessible.
• The table is no longer in the data dictionary.
• All indexes and triggers on the table are dropped.
• All synonyms that point to this table remain, but when you try to use them, you’ll get an error.

Additionally, in Oracle:

• All views and PL/SQL program units that depend on this table will remain, but they become invalid.
• All extents allocated for the table are returned to the free space of the tablespace and can be used by other objects if required.

 

How Can I Undo a DROP TABLE?

You can undo the deleting of a table in Oracle SQL by using the FLASHBACK command.

The command looks like this.

FLASHBACK TABLE tablename TO BEFORE DROP;

I’ll write more about the FLASHBACK command in another post.

 

Is There a DELETE Table SQL Statement?

No, there isn’t. The DELETE statement deletes rows from a table and is specified as DELETE FROM TABLE.

 

Can You Drop a Table Only If It Exists?

Some databases support the SQL drop table if exists feature: MySQL, SQL Server, and PostgreSQL. Oracle does not include this as part of the DROP TABLE keyword.

 

Examples of SQL DROP TABLE

Here are some examples of dropping or deleting a table in Oracle SQL.

 

Example 1 – Simple drop

This statement drops a table that has no constraints.

DROP TABLE table1;

Table TABLE1 dropped.

 

Example 2 – Constraints

This example drops a table that has constraints.

DROP TABLE table2;

02449. 00000 - "unique/primary keys in table referenced by foreign keys"
*Cause: An attempt was made to drop a table with unique or
primary keys referenced by foreign keys in another table.
*Action: Before performing the above operations the table, drop the
foreign key constraints in other tables. You can see what
constraints are referencing a table by issuing the following
command:

 

Let’s try it again with CASCADE CONSTRAINTS.

DROP TABLE table2 CASCADE CONSTRAINTS;

Table TABLE1 dropped.

Now, the table has been dropped.

 

Example 3 – Undo DROP TABLE

Let’s drop the table and then undo it.

SELECT COUNT(*) FROM table3;

COUNT(*)

3

This shows us that we have a table and what’s inside it.

DROP TABLE table3;

Table TABLE3 dropped.

We have just dropped the table. Let’s try to select from it.

SELECT COUNT(*) FROM table3;

ORA-00942: table or view does not exist

It shows an error because the table has been dropped. Read more on this error here: ORA-00942 error.

Now, let’s restore it.

FLASHBACK TABLE table3 TO BEFORE DROP;
SELECT COUNT(*) FROM table3;

COUNT(*)

3

We can see that the table has been restored and the data is now viewable.

 

Example 4 – Purge (Oracle)

Let’s delete a table using PURGE and try to restore it.

DROP TABLE table4 PURGE;

FLASHBACK TABLE table4 TO BEFORE DROP;

SQL Error: ORA-38305: object not in RECYCLE BIN
38305. 00000 - "object not in RECYCLE BIN"
*Cause: Trying to Flashback Drop an object which is not in RecycleBin.
*Action: Only the objects in RecycleBin can be Flashback Dropped.

You can see that the FLASHBACK statement gives an error. This is because we specified the PURGE parameter when we deleted the table, so we cannot recover it using FLASHBACK.

 

Example 5 – Drop Multiple Tables (SQL Server, MySQL, PostgreSQL)

This example demonstrates how to drop multiple tables. It works for SQL Server, MySQL, and PostgreSQL, but not Oracle.

DROP TABLE table1, table2;

Both tables will then be dropped in one operation.

 

Summary of Differences Between Vendors

This table summarises the differences with the DROP TABLE statement with each database vendor:

Criteria	Oracle	SQL Server	MySQL	PostgreSQL
Drop Multiple Tables	No	Yes	Yes	Yes
Drop Table If Exists	No	Yes	Yes	Yes
Cascade Keyword	Yes	No	No	Yes

 

Conclusion

So, that’s how you can delete tables in SQL. You use the DROP TABLE command to delete a table.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!