🌱BeginnerDatabase & Table Creation
DROP TABLE
Permanently remove a table and all its data from the database.
Overview
DROP TABLE permanently removes a table and all of its data from the database. Unlike DELETE (which removes rows but keeps the table structure) or TRUNCATE (which empties rows), DROP TABLE destroys both the data and the schema definition. It is irreversible unless wrapped in a transaction that you later roll back.
PostgreSQL will prevent you from dropping a table that other objects depend on for example, a table that is referenced by a foreign key in another table, or a table used by a view. Use DROP TABLE ... CASCADE to automatically drop those dependent objects as well, or remove the dependencies manually first.
In practice, prefer using IF EXISTS in scripts and migrations to make them idempotent safe to run more than once without throwing errors.
Key Points
- •DROP TABLE removes both the schema and all data permanently
- •Use IF EXISTS to avoid errors if the table does not exist
- •CASCADE drops dependent objects (foreign keys, views) automatically
- •RESTRICT (default) refuses to drop if dependencies exist
- •Drop multiple tables in one statement: DROP TABLE t1, t2, t3
- •You can roll back a DROP TABLE if it is inside a transaction block
Examples
Basic DROP TABLE variants
-- Drop a table (raises error if not found)
DROP TABLE old_logs;
-- Drop only if it exists
DROP TABLE IF EXISTS old_logs;
-- Drop multiple tables at once
DROP TABLE IF EXISTS sessions, temp_data, staging;Drop with CASCADE
-- This fails if 'orders' has a FK referencing 'customers'
DROP TABLE customers;
-- This succeeds, also dropping the FK constraint on orders
DROP TABLE customers CASCADE;CASCADE can silently destroy dependent views and constraints review dependencies with \d tablename before using it.
Safe drop inside a transaction
BEGIN;
DROP TABLE IF EXISTS my_table;
-- If something goes wrong:
ROLLBACK;
-- If everything is fine:
COMMIT;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4