MySQL Drop Table

Last updated: October 19, 2026Oct 19, 2026 4 min read

When a table is no longer needed, DROP TABLE removes it completely: its structure, all its rows, its indexes and its triggers. Like dropping a database, it is permanent, so this tutorial covers the safe way to do it and the errors you may run into.

1. Syntax

DROP [TEMPORARY] TABLE [IF EXISTS] table1 [, table2, ...] [RESTRICT | CASCADE];

• TEMPORARY: drop only a temporary table, never a permanent one with the same name.
• IF EXISTS: give a note instead of an error if a table is missing.
• Several tables can be dropped in one statement.
• RESTRICT and CASCADE are accepted but do nothing in MySQL.

2. Dropping a Table

SHOW TABLES;

Before

DROP TABLE old_stats;

Query OK, 0 rows affected

SHOW TABLES;

After

3. Dropping Several Tables at Once

DROP TABLE tags, temp_import;
SHOW TABLES;

Result: 3 rows

4. IF EXISTS

DROP TABLE old_stats;

ERROR 1051 (42S02): Unknown table 'blogdb.old_stats'

DROP TABLE IF EXISTS old_stats;
SHOW WARNINGS;

Query OK, 0 rows affected, 1 warning

Result: 1 row

5. Tables Referenced by Foreign Keys

You can't drop a table that another table's foreign key points to. posts.author_id references authors:

DROP TABLE authors;

ERROR 3730 (HY000): Cannot drop table 'authors' referenced by a foreign key constraint 'posts_ibfk_1' on table 'posts'.

Find the referencing tables in information_schema:

SELECT table_name, column_name, referenced_table_name, constraint_name
FROM information_schema.key_column_usage
WHERE referenced_table_schema = 'blogdb' AND referenced_table_name = 'authors';

Result: 1 row

There are three ways forward:

1. Drop the child table first (here posts), or both together in child-then-parent order: DROP TABLE posts, authors;
2. Remove the foreign key: ALTER TABLE posts DROP FOREIGN KEY posts_ibfk_1;
3. Temporarily disable checks (SET foreign_key_checks = 0;), only for controlled maintenance, because it can leave child rows pointing nowhere.

DROP TABLE posts, authors;
SHOW TABLES;

Result: 1 row

6. What Happens to Views

Views that use a dropped table are not removed; they become invalid:

SELECT * FROM post_list;

ERROR 1356 (HY000): View 'blogdb.post_list' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Drop or recreate such views. CHECK TABLE post_list; also reports broken views.

DROP VIEW post_list;

Query OK, 0 rows affected

7. Dropping Temporary Tables

A temporary table can hide a permanent table of the same name. DROP TEMPORARY TABLE guarantees you only remove the temporary one:

CREATE TABLE report (id INT);
INSERT INTO report VALUES (1), (2), (3);
CREATE TEMPORARY TABLE report (id INT);       -- hides the real table in this session
SELECT COUNT(*) AS visible_rows FROM report;   -- the temporary one is empty

DROP TEMPORARY TABLE report;
SELECT COUNT(*) AS visible_rows FROM report;   -- the real table is back

While the temporary table exists

After DROP TEMPORARY TABLE

8. Safety Checklist

1. Check you are on the right server and database: SELECT @@hostname, DATABASE();
2. Count the rows: SELECT COUNT(*) FROM table_name;
3. Back up the table: mysqldump -u root -p dbname table_name > table_name.sql, or keep a copy with CREATE TABLE t_backup AS SELECT * FROM t;
4. Check for foreign keys, views and application code that use it.
5. Remember: DDL commits immediately, so ROLLBACK can't bring the table back.

9. DROP vs TRUNCATE vs DELETE

10. Summary

• DROP TABLE t; removes a table's structure and data permanently.
• Use IF EXISTS in scripts and list several tables to drop them together.
• Drop child tables before parents, or remove the foreign key first.
• Views on a dropped table become invalid; use DROP TEMPORARY TABLE for temporary tables.
• Back up first: there is no undo.