DROP TABLE permanently removes a table, its structure and every row of data inside it, in one statement, scoped to a single table rather than an entire database.
Learning Objectives
- Use DROP TABLE and understand exactly what it removes.
- Use IF EXISTS for a safely re-runnable version.
- Explain why a table referenced by a foreign key can't simply be dropped.
Basic DROP TABLE
The table and all its rows are gone immediately, scoped to just this one table, the rest of the database is untouched. Same irreversibility as DROP DATABASE, just at a smaller scope.
DROP TABLE reviews;
IF EXISTS
The same safely-re-runnable pattern from CREATE and DROP DATABASE, do nothing instead of erroring if the table's already gone, useful in setup or teardown scripts.
DROP TABLE IF EXISTS reviews;
Dropping a Table Other Tables Depend On
Every database in this course blocks this by default, departments can't be dropped while employees.department_id still has a FOREIGN KEY pointing at it, the database protects against leaving employees with a reference to a table that no longer exists. The dependent table (employees) would need to be dropped first, or the foreign key removed, before departments can go.
DROP TABLE departments; -- Error: employees has a FOREIGN KEY referencing departments(id)
Try It Yourself
Write a safely re-runnable statement that drops the reviews table created in the previous lesson.
DROP TABLE IF EXISTS reviews;
Frequently Asked Questions
What's the difference between DROP TABLE and DELETE FROM table (with no WHERE)?
DELETE FROM table removes every row but keeps the table's structure, columns, types, and constraints, intact and ready for new data. DROP TABLE removes the table itself entirely, the structure is gone too, it would need to be recreated with CREATE TABLE before any data could be stored there again.
Can a dropped table's foreign key constraints be removed instead of dropping the dependent table?
Yes, ALTER TABLE (covered next) can drop a specific foreign key constraint, after which the referenced table can be dropped safely without touching the dependent table itself.
Common Beginner Mistakes
Expecting DROP TABLE departments to succeed while employees still references it
It's blocked by design, exactly the safeguard this lesson covers. Drop or alter the dependent table's foreign key first, or drop tables in the reverse of the order they were created in.
Confusing DROP TABLE with TRUNCATE TABLE
DROP TABLE removes the table structure entirely. TRUNCATE TABLE (not covered in depth in this course) empties all rows while keeping the structure, similar in effect to DELETE FROM table with no WHERE, but typically faster and with its own specific rules.
Best Practices
- Drop tables in the reverse order they were created in, dependents before the tables they reference.
- Use IF EXISTS in any script meant to be safely re-runnable.
- Double-check a table name and target database before running DROP TABLE, the same discipline DROP DATABASE deserves.
Interview Questions
Why would DROP TABLE departments fail in Beacon Robotics' schema?
employees.department_id has a FOREIGN KEY REFERENCES departments(id). The database refuses to drop departments while that reference still exists, to prevent employees rows from pointing at a department table that no longer exists. The foreign key, or the employees table itself, would need to be dropped first.
What's the structural difference between DROP TABLE and DELETE FROM table?
DELETE FROM table removes rows but keeps the table's definition, columns, types, and constraints intact. DROP TABLE removes the table's definition entirely, nothing is left to insert into until it's recreated with CREATE TABLE.
Summary
DROP TABLE permanently removes one table and its data, scoped more narrowly than DROP DATABASE. A table still referenced by another table's foreign key can't be dropped until that dependency is removed first, a safeguard against orphaned references.
What's Next?
ALTER TABLE closes this module, changing an existing table's structure, adding, removing, or modifying columns, without dropping and recreating it.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.