Summary: How to drop MySQL database tables and recreate them when you have foreign key relationships between the tables.
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:
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.