SQL — Drop Table

Syntax

DROP TABLE table_name;

Safe Version

DROP TABLE IF EXISTS temp_data;

What Happens When You Drop

• The table structure is removed
• All data in the table is permanently deleted
• All indexes and constraints on the table are removed
• Other tables are not affected (unless foreign keys reference this table)

Example

-- Create a temporary table
CREATE TABLE temp_results (
  id INT,
  score INT
);

-- Use it for calculations
INSERT INTO temp_results VALUES (1, 95), (2, 87);

-- Drop it when done
DROP TABLE IF EXISTS temp_results;

Foreign Key Constraints

If another table references this table, you need to handle the dependency first:

-- Option 1: Drop the dependent table first
DROP TABLE orders;
DROP TABLE customers;

-- Option 2: Disable foreign key checks (MySQL)
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE customers;
SET FOREIGN_KEY_CHECKS = 1;

-- Option 3: Drop with cascade (PostgreSQL)
DROP TABLE customers CASCADE;

TRUNCATE vs DELETE vs DROP

Operation	Speed	Rollback	Structure
DELETE FROM t	Slow	Yes	Kept
TRUNCATE TABLE t	Fast	Depends	Kept
DROP TABLE t	Fastest	No	Removed

• Use DELETE when you need a WHERE clause or rollback
• Use TRUNCATE when you want to remove all rows fast but keep the table
• Use DROP when you want to remove the table entirely

Dropping Multiple Tables

DROP TABLE IF EXISTS table1, table2, table3;

Practical Pattern: Recreating a Table

-- Common in development: drop and recreate with new schema
DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Best Practices

• Always use IF EXISTS in scripts
• Back up important tables before dropping
• Check foreign key dependencies first
• Never drop production tables without approval
• Use TRUNCATE instead of DROP + CREATE when you only need to clear data

Practice

1. Create a temporary table, insert data, then drop it
2. Create two tables with a foreign key and drop them in the correct order
3. Write a script that safely recreates a table with a new schema