DROP TABLE

Definition

DROP TABLE permanently removes a table's structure and all of its data from the database. Unlike TRUNCATE (empties data, keeps structure) or DELETE (removes rows selectively), DROP TABLE erases the object entirely — columns, constraints, indexes, and rows all disappear.

sql
DROP TABLE orders;

DROP TABLE IF EXISTS orders;

DROP TABLE orders CASCADE;   -- also drops dependent views/FKs that reference it

How It Works

The engine removes the table's catalog entry and reclaims its storage. If other objects depend on the table — a foreign key from another table, or a view built with SELECT * FROM orders — the default behavior (RESTRICT) blocks the drop with an error. CASCADE forces the drop to proceed, taking all dependent objects down with it.

Worked example:

sql
CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id));
CREATE VIEW customer_orders AS SELECT c.name, o.id FROM customers c JOIN orders o ON o.customer_id = c.id;

DROP TABLE customers;            -- ERROR: still referenced by orders.customer_id and by the view
DROP TABLE customers CASCADE;    -- succeeds, but silently drops the FK constraint on orders AND the view

That second statement is exactly why CASCADE deserves caution: it doesn't just fix the immediate blocker, it deletes every dependent object in one shot, possibly things the person running the command didn't even know existed.

Edge Cases and Pitfalls

• Irreversible outside a transaction: once committed, the table and its data are gone. In engines with transactional DDL (PostgreSQL, SQL Server), running DROP TABLE inside an explicit transaction can be rolled back before COMMIT. In MySQL, DDL causes an implicit commit — there is no rollback once the statement runs.
• Dependent views break silently if dropped without CASCADE being noticed — always check dependencies first (e.g. via information_schema or engine-specific dependency views) before dropping in production.
• No `WHERE` clause exists for DROP TABLE — it is all-or-nothing on the entire object, unlike DELETE.
• Best practice before a destructive drop in production: rename the table first (ALTER TABLE orders RENAME TO orders_pending_delete;), let it sit for a safety window, then drop it once confident nothing still depends on it.

Key Takeaways / Q&A

Q: DROP TABLE vs TRUNCATE TABLE vs DELETE — what's the core difference? A: DROP TABLE removes the table object itself (structure + data + indexes + constraints). TRUNCATE TABLE empties all rows quickly but keeps the table structure. DELETE removes rows transactionally, row-by-row, optionally filtered with WHERE, and is the only one of the three that can target a subset of rows.

Q: Can DROP TABLE be rolled back? A: Only inside an explicit transaction on engines with transactional DDL (e.g., PostgreSQL) and only before commit. On MySQL, no — DDL auto-commits.

Q: What does CASCADE do that RESTRICT (the default) does not? A: CASCADE also drops every object that depends on the table (foreign keys, views, etc.) instead of erroring out.