DROP TABLE Statement
SQL · Working with Tables
Theory
DROP TABLE removes a table completely: its rows, its column definitions, its indexes, constraints and triggers, and the permissions granted on it. After DROP TABLE old_employees, any query mentioning old_employees fails because the table no longer exists. DROP TABLE IF EXISTS avoids an error when the table might already be gone, which is useful in scripts that re-run.
Dropping is usually permanent; you need a backup to recover, although some platforms offer recovery features (for example Snowflake UNDROP or Oracle's recycle bin). If other tables reference it through foreign keys, the drop is blocked, or with CASCADE (where supported) the dependent constraints are removed too. Views built on the table become broken. If you only want to empty a table and keep its structure, use TRUNCATE or DELETE instead.
Why we use it
Old staging tables, test copies and retired features leave clutter that confuses people and uses storage. DROP TABLE removes the whole object cleanly when it is no longer needed.
When to use it
Use DROP TABLE when the table itself should disappear. To clear data but keep the table, use TRUNCATE (all rows) or DELETE (some rows). Check dependencies such as views and foreign keys before dropping.
How it works
- DROP TABLE old_employees
- Does the table exist? Yes: Check dependencies. No: Error (or do nothing with IF EXISTS).
- Do foreign keys reference it? Yes: Block the drop (or remove them with CASCADE). No: Continue.
- Remove rows, columns, indexes, constraints and triggers
- Table name is free; the object is gone
Syntax
DROP TABLE table_name;Example
DROP TABLE old_employees;Key points
- Irreversible: usually cannot be undone without a backup
- Different from TRUNCATE, which keeps the table structure
- DROP TABLE IF EXISTS is safe for re-runnable scripts
- Blocked by referencing foreign keys unless CASCADE is used
- Views that depend on the table break