Deleting un-named foreign keys in a migration

So, we use foreign-key constraints in our db. It's pretty annoying to get Rails to work nicely with that, but we have some basic helper methods (which I didn't write, so I cant share).

However I recently had some trouble dropping an old table. I kept getting these errors:

Mysql::Error: Error on rename of './mydbname/#sql-1ca8_f7842' to 
   './mydbname/my_table_name' (errno: 150): DROP INDEX `my_table_name_idx3` 
   ON my_table_name

I discovered that this was because it had foreign-key constraints. The awful error message is itself listed as a bug on mysql... but the real problem is that it doesn't want to drop an index I've asked it - because there's a foreign-key constraint on the column referenced by that index*.

Fair enough.

Unfortunately, the "remove_foreign_key" code we have assumes the foreign-key has been named in a certain way (table_name_column_name) but in this case it wasn't. So Trying a drop index on that caused it to give an equally unhelpful message along the same lines...

This foreign key has been hanging around since the dawn-o-time, and it has one of those automagically-generated constraint-names built by the db itself... something like: my_table_name_ibfk_16.

Now that'd be fine to drop if we just had one client. with one database... you can easily put the following into a migration:

   execute "ALTER TABLE `my_table_name` DROP FOREIGN KEY `my_table_name_ibfk_16`" 

Unfortunately for us, we have 150 clients - each with their own db... and it looks like that constraint-name differs depending on which order the foreign-keys got created. ie sometimes it's my_table_name_ibfk_16 and sometimes it's my_table_name_ibfk_3 - and if you drop it by number - you could be killing the WRONG foreign-key constraint... which would just be embarrassing.

The way out of this quandary is to query the information_schema table to find the foreign-key's actual constraint-name. and here it is for your amusement. (note: put this into initializers eg by saving it as config/initializers/migrations.rb

class ActiveRecord::Migration
  # grab the db-name out of the connection and persist it
  # it's not going to change over the course of a single migration
  def self.fetch_database_name
    @@database_name ||= connection.database_name
  end

  # Use this if the foreign-key was created without an explicit name - 
  # and has one of the automatically-generated constraint-names.
  #
  # This method queries the information-schema table to fetch out the key
  # name before continuing to drop the foreign-key
  #
  # Use this in your migrations with:
  #    remove_legacy_foreign_key :table_name, :field_name
  # eg:
  #    remove_legacy_foreign_key :widgets, :wodget_id
  def self.remove_legacy_foreign_key(table, column_name)
    # first pull the foreign-key name from the information schema
    result = execute "select constraint_name from information_schema.key_column_usage as ke where ke.table_schema = '#{fetch_database_name}' and ke.table_name = '#{table}' and ke.column_name = '#{column_name}';"
    name = result.fetch_hash['constraint_name']
    raise "Got no foreign key by that name" unless name.present?
    execute "ALTER TABLE `#{table}` DROP FOREIGN KEY `#{name}`"
  end
end

---

[*] Note: mysql also gives a similar error if you're dropping a foreign-key constraint that doesn't exist at all by the name you give it:

ERROR 1025 (HY000): Error on rename of './mydbname/my_table_name' to 
   './mydbname/#sql2-1ca8-f6cc5' (errno: 152)