SQL DROP TABLE and DROP DATABASE

DROP removes a database object completely: the table’s structure, data, indexes and triggers, or a whole database with every table in it. It is instant and cannot be undone with ROLLBACK, so it is the one command where a typo hurts most. This lesson shows how to drop safely.

DROP TABLE

CREATE TABLE temp_report (id INT, total INT);SHOW TABLES LIKE 'temp%';DROP TABLE temp_report;SHOW TABLES LIKE 'temp%';

IF EXISTS

Dropping a table that does not exist is an error:

DROP TABLE no_such_table;

ERROR 1051 (42S02): Unknown table 'company.no_such_table'

IF EXISTS turns it into a note, so setup scripts can run again and again:

DROP TABLE IF EXISTS no_such_table;SHOW WARNINGS;

Several tables at once

CREATE TABLE t1 (a INT);CREATE TABLE t2 (a INT);DROP TABLE IF EXISTS t1, t2;SHOW TABLES LIKE 't_';

Tables referenced by foreign keys

A parent table cannot be dropped while a child table references it:

DROP TABLE customers;

ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'orders_ibfk_1' on table 'orders'.

Drop the child first, then the parent:

DROP TABLE order_items;DROP TABLE orders;DROP TABLE customers;SHOW TABLES;

DROP DATABASE

CREATE DATABASE scratch_db;DROP DATABASE scratch_db;DROP DATABASE IF EXISTS scratch_db;SHOW DATABASES;

DROP vs TRUNCATE vs DELETE

Safety checklist

1. Run SELECT DATABASE(); to be sure which database you are in.
2. Take a backup (mysqldump) of anything you might need.
3. Prefer renaming (RENAME TABLE orders TO orders_old_2026) and dropping later, once nobody has complained.
4. In production, grant the DROP privilege only to the accounts that need it (GRANT).

DROP FAQ