MySQL DROP TABLE: Delete a Table Safely with IF EXISTS

DROP TABLE removes a table, all its rows and its indexes. It cannot be undone.

query.sql

SQL

1
2
3

CREATE TABLE scratch (id INT);
DROP TABLE scratch;
SHOW TABLES;

Output

OUTPUT

Empty set

IF EXISTS

query.sql

SQL

DROP TABLE scratch;

Output

OUTPUT

ERROR 1051 (42S02): Unknown table 'learn.scratch'

query.sql

SQL

1
2

DROP TABLE IF EXISTS scratch;
SHOW WARNINGS;

Output

OUTPUT

+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Note  | 1051 | Unknown table 'learn.scratch' |
+-------+------+-------------------------------+
1 row in set

Drop several tables

query.sql

SQL

CREATE TABLE a1 (id INT);
CREATE TABLE a2 (id INT);
DROP TABLE IF EXISTS a1, a2;
SHOW TABLES;

Output

OUTPUT

Empty set

Foreign keys block the drop

You cannot drop a table that another table’s foreign key points to:

query.sql

SQL

1
2
3

CREATE TABLE parents (id INT PRIMARY KEY);
CREATE TABLE children (id INT PRIMARY KEY, parent_id INT, FOREIGN KEY (parent_id) REFERENCES parents (id));
DROP TABLE parents;

Output

OUTPUT

ERROR 3730 (HY000): Cannot drop table 'parents' referenced by a foreign key constraint 'children_ibfk_1' on table 'children'.

Drop the child first, or drop both in one statement:

query.sql

SQL

1
2

DROP TABLE children, parents;
SHOW TABLES;

Output

OUTPUT

Empty set

You can switch off foreign key checks with SET FOREIGN_KEY_CHECKS = 0, which is occasionally useful when restoring a dump. Switch them back on straight away, and never leave them off in application code.

DROP vs TRUNCATE vs DELETE

	Removes rows	Removes the table	Can roll back	Speed on large tables
`DELETE FROM t`	Yes	No	Yes	Slow
`TRUNCATE TABLE t`	Yes (all)	No	No	Fast
`DROP TABLE t`	Yes	Yes	No	Fast

A safe procedure for production

Confirm it is unused. Search the code. Check the slow log or performance_schema for recent reads and writes.
Rename first, as a soft delete: RENAME TABLE logs TO logs_to_drop_2026_07;. If something breaks, you can rename it back.
Wait a week or a release cycle.
Back up, then drop.

query.sql

SQL

1
2
3

CREATE TABLE old_logs (id INT);
RENAME TABLE old_logs TO old_logs_to_drop;
SHOW TABLES LIKE '%to_drop';

Output

OUTPUT

+----------------------------+
| Tables_in_learn (%to_drop) |
+----------------------------+
| old_logs_to_drop           |
+----------------------------+
1 row in set

dangerous

No recycle bin

MySQL has no “undo drop”. The only way back is a backup. Check which database you are in (SELECT DATABASE();) before every DROP.