SQL guide · Tables, views & databases (DDL)

SQL DROP TABLE: How to Delete a Table and Its Data

The DROP TABLE statement removes a table from the database, including its structure, all of its rows, its indexes and its triggers. After it runs the table no longer exists, and any query that references it fails until it is recreated. This is different from deleting rows, which leaves an empty table behind, and from truncating, which also empties the table but keeps its definition.

People drop tables to clear out temporary or staging tables, to remove obsolete structures after a migration, and to rebuild a table from scratch in a development database. Because the change is usually permanent and committed immediately in many systems, it deserves the same care as any destructive command. Confirm the table name, check what depends on it, and keep a backup if the data matters.

Dependencies are the common source of trouble. A table that is the target of a foreign key cannot normally be dropped until the referencing constraint or table is removed first, and views or procedures that mention the table may break. Some systems offer CASCADE to remove dependent objects automatically, but use that option sparingly because it can delete more than you intended. If you only need to change a table, use ALTER TABLE instead of dropping it.

Basic syntax

DROP TABLE [IF EXISTS] table_name [, table_name2 ...] [CASCADE | RESTRICT];

IF EXISTS avoids an error when the table is absent. Listing several names removes them in one statement in most dialects. CASCADE also drops dependent objects, while RESTRICT, the default in PostgreSQL, refuses to drop when dependencies exist.

SQL DROP TABLE examples

Drop a single table

This removes the table and every row it holds. Nothing is left behind, so make sure this is a scratch or retired table before running it.

DROP TABLE old_customers;

Drop only if the table exists

With IF EXISTS the statement does not raise an error when the table is missing, which makes migration and test scripts safe to run repeatedly. It is the usual first line when rebuilding a staging table.

DROP TABLE IF EXISTS staging_orders;

CREATE TABLE staging_orders (
  order_id INTEGER,
  total DECIMAL(10,2)
);

Drop several tables at once

Many databases accept a comma-separated list, which is convenient when cleaning up a set of temporary tables. List child tables before the tables they reference if foreign keys are involved.

DROP TABLE IF EXISTS tmp_order_items, tmp_orders, tmp_customers;

Drop with CASCADE in PostgreSQL

When views or foreign keys depend on the table, a plain drop fails. CASCADE removes those dependent objects as well, so review what depends on the table first, because the views will disappear too.

DROP TABLE customers CASCADE;

Where SQL dialects differ

PostgreSQL: Supports IF EXISTS, multiple tables, and CASCADE or RESTRICT. DROP TABLE is transactional, so inside a transaction you can roll it back before committing.

MySQL: Supports IF EXISTS and multiple tables. The CASCADE and RESTRICT keywords are accepted but ignored. The statement causes an implicit commit and cannot be rolled back. Use DROP TEMPORARY TABLE to avoid accidentally removing a permanent table of the same name.

SQL Server: IF EXISTS is available from SQL Server 2016. There is no CASCADE; you must drop foreign keys or dependent tables yourself. Temporary tables begin with # and disappear automatically at the end of the session.

SQLite: Supports IF EXISTS. When foreign keys are enabled, SQLite performs an implicit DELETE FROM first, which can fail if rows are referenced. There is no CASCADE keyword.

Oracle: Use DROP TABLE table_name CASCADE CONSTRAINTS to remove referencing foreign keys. Since 10g the table goes to the recycle bin unless you add PURGE, so it can often be restored with FLASHBACK TABLE.

Common mistakes

  • Confusing DROP TABLE with DELETE or TRUNCATE. Dropping removes the table itself, whereas the others only remove rows.
  • Dropping a parent table while child tables still reference it, causing a foreign key error. Remove the child tables or constraints first.
  • Using CASCADE without reviewing the dependencies, which can silently remove views and constraints that other code relies on.
  • Dropping a table in production without a backup or a tested restore path. In MySQL the operation cannot be rolled back at all.
  • Typing the wrong schema or table name. Qualify names, such as analytics.events, when more than one schema exists.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator