SQL Reference ยท Data Definition
SQL DROP TABLE statement
Reference for SQL DROP TABLE: remove a table and its data for good, use IF EXISTS, deal with foreign keys, and compare DROP, DELETE and TRUNCATE.
DROP TABLE deletes a table completely: its rows, its definition, and the indexes and triggers attached to it. Unlike DELETE, which empties a table but leaves it in place, DROP TABLE makes the table cease to exist.
Treat it as irreversible. Some databases let you roll it back inside a transaction, but outside one the data is gone. Adding IF EXISTS stops the statement from failing when the table is already missing, which makes scripts safe to re-run.
DROP TABLE IF EXISTS scratch_data;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
DROP TABLE [IF EXISTS] table_name [, table_name ...] [CASCADE | RESTRICT];
| Part | Description |
|---|---|
IF EXISTS |
No error if the table does not exist. |
table_name |
The table to remove, optionally schema-qualified. PostgreSQL, MySQL and SQL Server accept a list; SQLite and Oracle take one table. |
CASCADE / RESTRICT |
PostgreSQL: CASCADE also drops dependent objects such as views and foreign-key constraints; RESTRICT (default) refuses. Oracle uses CASCADE CONSTRAINTS. |
Dialect differences
| Dialect | Behavior |
|---|---|
| SQLite | One table per statement. Dropping a parent table with foreign keys on performs an implicit DELETE FROM first, so violations can occur. Space is freed for reuse, not returned to the file until VACUUM. |
| PostgreSQL | Transactional DDL, so DROP TABLE can be rolled back. Fails if views or foreign keys depend on the table unless CASCADE. |
| MySQL | Implicit commit; cannot be rolled back. DROP TEMPORARY TABLE removes only temp tables. |
| SQL Server | IF EXISTS since 2016. Fails if a foreign key references the table. |
| Oracle | No IF EXISTS before 23ai. Dropped tables go to the recycle bin; PURGE skips it. Use CASCADE CONSTRAINTS for referencing keys. |
-- Oracle DROP TABLE scratch_data CASCADE CONSTRAINTS PURGE; -- PostgreSQL DROP TABLE IF EXISTS child_table, parent_table CASCADE;
Usage notes
- Order matters with foreign keys: drop child tables before the parents they reference (or use the dialect's cascade option).
DROPvsDELETEvsTRUNCATE:DROPremoves the table,TRUNCATEremoves all rows but keeps the structure,DELETEremoves chosen rows and is logged row by row.- Dependent objects: indexes and triggers on the table go with it. Views that read from it remain but fail when queried.
- Permissions: dropping often needs ownership or
DROPrights, a separate privilege from writing data. - Never build the name from user input. Table names cannot be bound as parameters; whitelist them.