Skip to content
Tutorials Center
My learning

Topics

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.

SQL
DROP TABLE IF EXISTS scratch_data;

Try it

SQL Drop a table and check it is gone ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL IF EXISTS, and a missing table (fails on purpose) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
SQL
-- 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).
  • DROP vs DELETE vs TRUNCATE: DROP removes the table, TRUNCATE removes all rows but keeps the structure, DELETE removes 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 DROP rights, a separate privilege from writing data.
  • Never build the name from user input. Table names cannot be bound as parameters; whitelist them.