SQL DROP TABLE: Delete a Table Permanently

DROP TABLE deletes a table completely: its rows, its columns, its indexes and constraints. Afterwards the table no longer exists. It is the most destructive common statement in SQL, so it is worth understanding exactly what it does.

Syntax#

DROP TABLE table_name;

Example#

DROP TABLE orders;

SELECT * FROM orders;

ERROR: no such table: orders

The table is gone. Any query that referred to it now fails. (PostgreSQL phrases the error as relation "orders" does not exist.)

DROP TABLE IF EXISTS#

Dropping a table that does not exist is an error, which stops scripts halfway through. IF EXISTS makes the statement do nothing in that case:

DROP TABLE IF EXISTS old_orders;

This is the standard way to start setup and test scripts that rebuild a table from scratch:

DROP TABLE IF EXISTS courses;

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title     VARCHAR(100) NOT NULL
);

IF EXISTS is supported by PostgreSQL, MySQL, SQLite, SQL Server 2016+ and Oracle 23c+.

Dropping several tables#

PostgreSQL, MySQL and SQL Server let you drop several tables at once:

DROP TABLE IF EXISTS temp_sales, temp_customers;

Foreign keys can block a drop#

If another table has a foreign key pointing at the table you want to drop, most databases refuse, because the other table's references would point at nothing. You can:

1. Drop the child table (the one with the foreign key) first, then the parent; or
2. Ask the database to remove the dependent foreign key constraints for you. The child tables and their data stay; only the constraints pointing at the dropped table go:

-- PostgreSQL (also drops views that depend on the table)
DROP TABLE customers CASCADE;

-- Oracle
DROP TABLE customers CASCADE CONSTRAINTS;

DROP vs DELETE vs TRUNCATE#

If you only want an empty table, use TRUNCATE or DELETE, not DROP followed by CREATE, because dropping also loses permissions, indexes and constraints that someone may have added.

Can you undo it?#

• In PostgreSQL and SQL Server, DROP TABLE inside an open transaction can be rolled back.
• In MySQL and Oracle, DROP TABLE commits immediately. Oracle's recycle bin can sometimes restore it with FLASHBACK TABLE.
• Otherwise, the only way back is a backup.

Common mistakes#

Practice#

DROP TABLE IF EXISTS temp_report;

CREATE TABLE temp_report (
  label VARCHAR(100),
  total INTEGER
);

Key takeaway#