Topic 7 of 52 · Beginner
DROP TABLE
Delete a table completely: structure, data, indexes and permissions.
01DROP TABLE
Module 02 – Database & Table Management (DDL)
02What is it?
DROP TABLE removes the table itself from the database. Its rows, columns, indexes, triggers and constraints all disappear. It cannot be undone with ROLLBACK.
03Why do we need it?
Temporary or staging tables must be cleaned up after a pipeline run, and obsolete tables should be removed to keep the schema understandable.
04Syntax
DROP [TEMPORARY] TABLE [IF EXISTS] table1 [, table2 ...];05Parameter explanation
- TEMPORARY
- MySQL: only drops temporary tables (safety guard).
- IF EXISTS
- No error if the table isn't there — useful in scripts.
- table1, table2
- MySQL can drop several tables at once.
06Sample database
Every example and exercise below starts from a fresh copy of this data.
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
dept_id INT,
salary DECIMAL(10,2),
hire_date DATE
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'HR');
INSERT INTO employees VALUES
(1,'Asha',10,85000,'2021-04-12'),
(2,'Ravi',20,52000,'2022-01-03'),
(3,'Meena',10,97000,'2019-09-20'),
(4,'Karan',30,46000,'2023-06-15'),
(5,'Divya',20,61000,'2020-11-30');
CREATE TABLE old_logs (id INT, msg TEXT);
INSERT INTO old_logs VALUES (1,'boot'),(2,'login');07Basic coding example
08Step-by-step execution
- 1.MySQL checks old_logs exists (and no foreign key from another table blocks it).
- 2.The table definition and all rows are removed.
- 3.The name is free to be reused.
09Before and after output
Generated live by running the basic example against the sample database.
Running on the SQL engine…
10Intermediate coding example
11Real-world business scenario
Re-runnable ETL scripts start with DROP TABLE IF EXISTS stg_x; CREATE TABLE stg_x ... so each run starts clean and never fails because a previous run left the table behind.
12Common mistakes
DROP TABLE reports;
Table doesn't exist → error stops the script.
DROP TABLE IF EXISTS reports;
DROP TABLE departments; -- while employees references it
With an enforced foreign key, MySQL blocks dropping a parent table.
Drop the child table or the FK first.
DROP TABLE employees; -- wanted to empty it
This deletes the table itself.
TRUNCATE TABLE employees;
13Important comparisons
| DROP TABLE | TRUNCATE | DELETE | |
|---|---|---|---|
| Removes structure | Yes | No | No |
| Removes rows | All | All | Filtered or all |
| WHERE allowed | No | No | Yes |
| Rollback (InnoDB) | No | No | Yes in a transaction |
14Practical exercises
Beginner1. Drop old_logs and list the remaining tables.
Reveal solution (15)
Only departments and employees remain.
Beginner2. Drop a table called ghost safely even though it doesn't exist.
Reveal solution (15)
IF EXISTS avoids the error.
Beginner3. Create t1, then drop it.
Reveal solution (15)
0 rows: it's gone.
Intermediate4. Build a summary table of salary per department, show it, then drop it.
Reveal solution (15)
Typical temporary-result pattern.
Intermediate5. Show the error when querying a dropped table.
Reveal solution (15)
Expected: no such table.
Intermediate6. Write a re-runnable script that recreates stg_emp from employees.
Reveal solution (15)
Running it twice proves it's idempotent.
Advanced7. Back up employees, drop it, and restore it from the backup.
Reveal solution (15)
Note: CREATE TABLE AS SELECT doesn't copy keys/indexes.
Advanced8. Drop two scratch tables in one script.
Reveal solution (15)
MySQL allows DROP TABLE IF EXISTS a, b; in one statement.
16Key takeaways
- DROP TABLE removes structure and data permanently.
- Use IF EXISTS in scripts.
- Foreign keys can block dropping a parent.
- Want to keep the table? Use TRUNCATE or DELETE.
17Knowledge check
1. What survives DROP TABLE?
2. Which keyword prevents an error if the table is missing?
3. To keep the table but remove all rows quickly, use…
Interview questions
Why might DROP TABLE fail on a parent table?
Another table has a FOREIGN KEY referencing it. Drop the child/constraint first, or (carefully) disable FOREIGN_KEY_CHECKS.
Lesson test
Take an 8-question test on “DROP TABLE” — concepts, output prediction, bug spotting and interview questions.