MySQL/MariaDB FAQ: How can I drop a series of database tables that have foreign key relationships between them?
If you ever have a problem where you need to drop a series of MySQL database tables that have foreign key relationships between them, the key to doing this is setting the
FOREIGN_KEY_CHECKS variable before and after your MySQL
DROP TABLE queries.
For example, something like this should work:
# 1) tell mysql to ignore foreign keys for a little while SET FOREIGN_KEY_CHECKS = 0; # 2) drop your tables drop table if exists foo; drop table if exists bar; drop table if exists baz; # 3) turn the mysql foreign keys check back on SET FOREIGN_KEY_CHECKS = 1;
I explain this more in the section that follows, but that’s the basic technique/solution.
Dropping and re-creating MySQL tables with FOREIGN_KEY_CHECKS
This is pretty obscure, but I thought I'd post it here so I wouldn't forget how to do this ... if you ever have a situation when using MySQL where:
- You're just starting to use a database table
- The table joins to other tables
- The table doesn't have any data in it (or possibly doesn't have much data in it)
- You want to change the MySQL table schema
- You may be able to use this technique
In my case I have a MySQL database table named 'entities' that looked fine when I was designing the table, but as I began using it, I realized there were a few things I wanted to change. I may have been able to make these changes using the MySQL ALTER TABLE syntax — I don't know, I didn't have to look it up — but instead I was able to use a MySQL foreign keys drop table trick/tecnique I wrote about long ago.
In short, to make the changes to my MySQL database table named entities, I issued these commands to drop and then re-create the database table, even though it had foreign keys to other database tables, and other tables had foreign keys to it:
# tell mysql to ignore foreign keys for a little while SET FOREIGN_KEY_CHECKS = 0; # drop my old database table drop table if exists entities; # re-create my table create table entities ( id int unsigned auto_increment not null primary key, project_id int unsigned not null, name varchar(64) not null, type character(3), num_rets int, num_dets int, complexity varchar(7), foreign key (project_id) references projects (id) on delete cascade ) ENGINE = InnoDB; # turn the mysql foreign keys check back on SET FOREIGN_KEY_CHECKS = 1;
As mentioned, this is a bit of an obscure technique, combining this MySQL foreign keys check with the need to drop a database table and then re-create the table, while the table had very little data in it, but hey, if it helps anyone else, I'm happy with it.