DROP TABLE Statement

What is DROP TABLE?

The DROP TABLE statement permanently removes a table and all of its data from the database. Once dropped, the table cannot be recovered unless you have a backup.

This is a Data Definition Language (DDL) command. Unlike DELETE, which removes rows, DROP TABLE removes the entire table structure, data, indexes, constraints, and triggers associated with it.

Syntax

DROP TABLE table_name;

To avoid an error when the table does not exist:

DROP TABLE IF EXISTS table_name;

To also drop dependent objects:

DROP TABLE table_name CASCADE;

When to Use

• Removing obsolete tables during schema migrations
• Cleaning up temporary tables after processing
• Resetting a development or test database
• Dropping tables that were created by mistake

Key Points

1. Irreversible — DROP TABLE permanently deletes the table and all its data. Always double-check before running it.
2. IF EXISTS — Adding IF EXISTS prevents an error if the table is already gone. This is useful in migration scripts.
3. CASCADE — The CASCADE option also drops objects that depend on the table, such as foreign key constraints or views.
4. RESTRICT — The default behavior is RESTRICT, which refuses to drop the table if other objects depend on it.
5. Permissions — You must be the table owner or a superuser to drop a table.