Module 6 — DROP TABLE & TRUNCATE

1. DROP TABLE — Remove a Table Entirely

What it is: DROP TABLE is a DDL command that completely removes a table from the database — the structure, all data, indexes, constraints, and triggers are permanently deleted.

Why we use it: To remove tables that are no longer needed — deprecated features, test tables, or temporary data structures. It's also used in development to recreate tables from scratch.

When we use it: During cleanup of test data, when removing deprecated features, or in development when you need to reset a table's structure.

-- Remove a table
DROP TABLE products;
 
-- Safe removal (prevents error if table doesn't exist)
DROP TABLE IF EXISTS products;

Handling Dependencies

If other tables reference this table via foreign keys, DROP TABLE will fail. Use CASCADE to remove dependencies too.

-- Fails if other tables reference this one
DROP TABLE users;
 
-- Removes the table AND all foreign key references
DROP TABLE users CASCADE;

2. TRUNCATE TABLE — Clear All Rows Instantly

What it is: TRUNCATE TABLE is a DDL command that instantly removes all rows from a table without scanning each row individually. Unlike DELETE, it doesn't check WHERE conditions or fire row-level triggers — it just empties the table.

Why we use it: When you need to clear a table quickly (test data, temporary data, logs), TRUNCATE is orders of magnitude faster than DELETE because it doesn't need to scan and delete rows one by one.

When we use it: During test cleanup, when resetting tables for new data imports, or when clearing temporary/staging tables.

-- Clear all data from a table
TRUNCATE TABLE products;
 
-- Clear multiple tables at once
TRUNCATE TABLE products, orders, users;

Why TRUNCATE instead of DELETE?

-- Truncate and reset the auto-increment counter
TRUNCATE TABLE products RESTART IDENTITY;
 
-- Truncate and cascade to referenced tables
TRUNCATE TABLE products CASCADE;

3. DROP vs TRUNCATE vs DELETE

What it is: These three commands all remove data, but they differ in scope, speed, and reversibility. Understanding when to use each is critical for safe database management.

Why we use it: Choosing the wrong command can lead to data loss (DROP when you meant TRUNCATE) or performance issues (DELETE when TRUNCATE would be instant). This comparison helps you pick the right tool.

When we use it: Every time you need to remove data — the choice depends on whether you want to keep the structure, how much data to remove, and whether you need WHERE filtering.

Common Mistakes to Avoid

1. Dropping the wrong table — Always run \dt first to verify the table name
2. Forgetting CASCADE — Foreign key constraints will block the drop
3. Using DELETE instead of TRUNCATE for clearing — DELETE is much slower for large tables
4. Not resetting sequences — After TRUNCATE, auto-increment IDs continue from where they left off unless you use RESTART IDENTITY

Practice Exercise

1. Create a table called temp_data with columns id SERIAL PRIMARY KEY and value TEXT
2. Insert 5 rows into temp_data
3. Verify the data with SELECT * FROM temp_data
4. Use TRUNCATE TABLE temp_data RESTART IDENTITY to clear it
5. Verify it's empty with SELECT * FROM temp_data
6. Insert 3 new rows and verify IDs start from 1
7. Drop the table with DROP TABLE IF EXISTS temp_data
8. Verify it's gone with \dt