TL;DR: If you can’t quickly see why your DROP TABLE statement is failing, check for typos in your table names.

In MySQL, foreign key constraints can stop you from dropping a table. But that’s not the only thing that can go wrong.

Let’s create a simple example using MySQL, in order to demo the problem. As the root user, sign in to MySQL using your favorite client. I like to use the command line mysql client, like this: mysql -u root -p.

Next, create a new test database, CREATE DATABASE hamsters;

Then:

USE hamsters;
create table toys (
   toy_id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(50) NOT NULL,
   PRIMARY KEY ( toy_id )
);
create table toy_makers (
   toy_maker_id INT NOT NULL AUTO_INCREMENT,
   toy_id INT,
   name VARCHAR(25),
   FOREIGN KEY ( toy_id ) REFERENCES toys ( toy_id ),
   PRIMARY KEY ( toy_maker_id )
);

After doing this, verify the tables exist.

mysql> show tables;
+--------------------+
| Tables_in_hamsters |
+--------------------+
| toy_makers         |
| toys               |
+--------------------+
2 rows in set (0.00 sec)

When experimenting with MySQL, you’ll often want to be running CREATE and DROP scripts. Here is my DROP script:

USE hamsters;
DROP TABLE IF EXISTS toys;
DROP TABLE IF EXISTS toy_makes;

Let’s look at the output:

mysql> USE hamsters;
Database changed
mysql> DROP TABLE IF EXISTS toys;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> DROP TABLE IF EXISTS toy_makes;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Well, that’s a bit frustrating, but it’s actually a pretty simple problem. The primary key toy_id in table toys is referenced as a foreign key in table toy_makers. You cannot drop toys first, even though there’s no data in either table! You’ll have to drop any tables that reference toys.toy_id as a foreign key prior to dropping toys. In my case, the solution is simple. Just do a switcheroo on our DROP statements, and DROP the toys table last:

USE hamsters;
DROP TABLE IF EXISTS toy_makes;
DROP TABLE IF EXISTS toys;

Here’s the output:

mysql> USE hamsters;
Database changed
mysql> DROP TABLE IF EXISTS toy_makes;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS toys;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

If you’re looking at this, you might be confused. I switched the order in which the tables were dropped, but, hm, I’m still getting the same complaint! Let’s look at our database by using the SHOW TABLES command:

mysql> SHOW TABLES;
+--------------------+
| Tables_in_hamsters |
+--------------------+
| toy_makers         |
| toys               |
+--------------------+
2 rows in set (0.00 sec)

At this point, I hope you’ve noticed the problem! There was a typo in my DROP TABLE script. The command DROP TABLE IF EXISTS toy_makes; ran without an error because it has the option IF EXISTS in it. There’s no table called toy_makes here – the table is called toy_makers.

What if we just run DROP TABLE toy_makes? That does produce an error: ERROR 1051 (42S02): Unknown table 'hamsters.toy_makes'.

Let’s fix my script:

USE hamsters;
DROP TABLE IF EXISTS toy_makers;
DROP TABLE IF EXISTS toys;

Both lines run successfully now, with output like Query OK, 0 rows affected (0.97 sec).