Lesson 14 — Removing Structure

DROP TABLE

DROP TABLE permanently removes a table and all its data. It is irreversible. Understanding the safety guards and cascade options prevents disasters.

SQL — DROP TABLE 
-- DROP TABLE — permanent, irreversible:
DROP TABLE sessions;

-- DROP TABLE IF EXISTS — no error if table doesn't exist:
DROP TABLE IF EXISTS sessions;
DROP TABLE IF EXISTS sessions, temp_data, staging_import;  -- multiple tables

-- Before dropping, check what depends on the table:
-- (MySQL)
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM   information_schema.KEY_COLUMN_USAGE
WHERE  REFERENCED_TABLE_NAME = 'learners';
SQL — Dependency handling 
-- Cannot drop a table referenced by a FK in another table:
-- ERROR: Cannot drop table 'learners' as it is referenced by a foreign key

-- Fix: drop child tables first, or drop constraints first:
-- Option A — drop in dependency order:
DROP TABLE IF EXISTS assessments;   -- references learners
DROP TABLE IF EXISTS placements;    -- references learners
DROP TABLE IF EXISTS learners;      -- safe now

-- Option B — disable FK checks temporarily (MySQL):
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE learners;
SET FOREIGN_KEY_CHECKS = 1;  -- REMEMBER to re-enable!

-- Option C — PostgreSQL CASCADE:
DROP TABLE learners CASCADE;  -- drops learners AND dependent FK constraints
SQL — TRUNCATE vs DROP 
-- TRUNCATE vs DROP:
-- TRUNCATE TABLE learners;  -- removes all ROWS, keeps structure
-- DROP TABLE learners;       -- removes the table entirely (structure + data)

-- Safe script pattern — drop and recreate:
DROP TABLE IF EXISTS learners;
CREATE TABLE learners (
    learner_id INT AUTO_INCREMENT PRIMARY KEY,
    -- ...
);

-- This is used in development/test scripts to reset state

Practice Task

Your Turn

Write a script that safely drops tables in the correct order given these relationships: learners → assessments → assessment_files. Then write a script that drops and recreates the assessments table fresh (for a test environment reset). Include IF NOT EXISTS on the CREATE and IF EXISTS on the DROP.

Common Mistakes

  • Dropping a table that other tables depend on — constraint error. Drop in reverse dependency order.
  • Forgetting FOREIGN_KEY_CHECKS = 1 after setting it to 0 — all FK integrity is disabled for your session.
  • DROP TABLE in production without a backup — catastrophic. Always backup first.
  • Confusing TRUNCATE and DROP — TRUNCATE keeps the table, DROP removes it entirely.

Professional Tip

In production, DROP TABLE requires a database administrator approval process, a backup, and usually a maintenance window. In development, it is a daily tool — drop and recreate freely.

Mini Quiz

What does DROP TABLE IF EXISTS do differently from DROP TABLE?