Dropping tables in MySQL

Say you have access to a MySQL database, and you need to restore it from a previous copy. You could just run the SQL file from the dump, but that is slightly unclean, since it won’t delete tables that it doesn’t know about. Now, let’s assume you don’t have full admin access to the server, so you can’t drop and recreate the db. You have to drop all the tables. Here’s what happens:

mysql> show tables;
+——————————+
| Tables_in_cem |
+——————————+
| academic_periods |
| admin_leases |
| admin_services |
| admins |
| course_adds_kills |
| course_gateways |
| course_organizations |
| course_services |
| course_types |
| courses |
| instructors |
| organization_course_gateways |
| organization_gateways |
| organizations |
| roles |
| section_params |
| sections |
| services |
| transactions |
+——————————+
19 rows in set (0.00 sec)

mysql> drop table academic_periods, admin_leases, admin_services, admins, course_adds_kills, course_gateways, course_organizations, course_services, course_types, courses, instructors, organization_course_gateways, organization_gateways, organizations, roles, section_params, sections, services, transactions;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

mysql> show tables;
+——————+
| Tables_in_cem |
+——————+
| academic_periods |
| course_gateways |
| course_types |
| courses |
| organizations |
| services |
+——————+
6 rows in set (0.00 sec)

mysql> drop table academic_periods, admin_leases, admin_services, admins, course_adds_kills, course_gateways, course_organizations, course_services, course_types, courses, instructors, organization_course_gateways, organization_gateways, organizations, roles, section_params, sections, services, transactions;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

mysql> show tables;
+——————+
| Tables_in_cem |
+——————+
| academic_periods |
| course_types |
+——————+
2 rows in set (0.00 sec)

mysql> drop table academic_periods, admin_leases, admin_services, admins, course_adds_kills, course_gateways, course_organizations, course_services, course_types, courses, instructors, organization_course_gateways, organization_gateways, organizations, roles, section_params, sections, services, transactions;
ERROR 1051 (42S02): Unknown table ‘admin_leases,admin_services,admins,course_adds_kills,course_gateways,course_organizations,course_ser’

mysql> show tables;
Empty set (0.00 sec)

Mission accomplished. You have to run the command n number of times, where n is the depth of the foreign key constraints, but keep feeding mysql the command and the tables will eventually be gone. I would contend that a “real” database would work out the dependencies and drop them all in one feel swoop.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s