Dropping those pesky foreign keys

So, we have always been annoyed with dropping those MySQL foreign keys. They required manual SQL and some thinkin’ to delete. (You couldn’t just write a common DDL statement to do it that will not fail in some environments, since different environments may have different names for the key. Hibernate has tools to generate schemas, but the generated foreign key names are different than the ones developers used to seed the key originally.) All of this meant that Operations has to do manual SQL which takes time, isn’t automated, and has the risks of doing manual SQL in production.

We developers kept saying “wait until we upgrade to MySQL 5, we will be able to do it then!” Well, we upgraded a while back, and we finally got around to doing it.

Simple answer for youDevise developers:

When writing DB upgrades, just use the DropForeignKey class. It will do it for you, and has the SQL magic built-in.

Long answer for the curious:

We use some clever SQL to get it to work:

select @keytodrop := CONCAT(
where TABLE_SCHEMA = (select database())
and TABLE_NAME='--table_name--' and COLUMN_NAME='--column_name--';

PREPARE stmt FROM @keytodrop;

Note: The –table_name– and –column_name– are inserted by the DropForeignKey class. If you want to run this by hand, you will have insert the names manually.

Dissecting this a bit, in the first SQL statement, we are setting the MySQL variable @keytodrop to the value of an alter table statement that will drop the foreign key.

We get the name of the foreign key by querying the INFORMATION_SCHEMA database. This INFORMATION_SCHEMA database is a “meta-database” of sorts that stores all of the database schema information about all of the other databases. We can query the metadata tables just like normal tables, and use it in further queries. (For those out there used to the USER_* tables in Oracle, then you will be right at home.) From this metadata, we can query the foreign key name by only knowing the table name and the column name that the foreign key is attached.

At this point, we have a MySQL variable that is holding the value of an alter table statement that will drop the foreign key. Now, how to run it?

On the MySQL variable page above, they mention all sorts of neat tricks to do with them like row numbers or temporary calculations. But in the article, they mention “Dynamic SQL” and suggest using the PREPARE, EXECUTE, and DEALLOCATE series of steps to run the variable content.

Now, for the YouDevise developers out there, this is all bundled into the DropForeignKey class, so you do not even have to think about it. Rest of the world: we can’t share our code on this blog, but you shouldn’t have much trouble writing a similar class once you have the nifty SQL above.

One thing to keep in mind is that this is very hard-coded to MySQL 5, so don’t go switchin’ your local DB to Oracle or anything.