Creating/deleting foreign keys during migrations

As far as I know, Rails doesn't use foreign key declarations in your database tables, and therefore has no way of creating and deleting foreign keys in migrations. If you want foreign keys to be added/deleted to/from your database during migration, you can do so by executing SQL statements directly during the migration. I won't go over the merits of having foreign keys, in many ways they are unnecessary with Rails but there may be reasons that you want to have them. I've created a mixin module for MySQL ONLY with two functions to add or remove a foreign key, here is the code:

# a mixin module for adding foreign keys to MySQL databases during migrations.
module ForeignKeyOps
  # adds a foreign key to the table.
  # Parameters:
  # table_name - the name of the table to add to
  # association_name - the name of the constraint to add to the database
  # local_column - the column in table_name that contains the key
  # foreign_table - the name of the foreign table to reference
  # foreign_column - the name of the primary key in the foreign table
  # options - an optional hash with additional parameters for the 
  #   constraint. If the following hash values are present, then the 
  #   additional constraints will be specified:
  #   - on_delete: specify "cascade" or "set null"
  #   - on_update: specify "cascade" or "set null"
  def add_foreign_key(table_name, constraint_name, local_column, 
                      foreign_table, foreign_column, options = {})
    st = "ALTER TABLE #{table_name} ADD CONSTRAINT #{constraint_name} "
    st += "FOREIGN KEY (#{local_column}) REFERENCES #{foreign_table} (#{foreign_column})"
    if options.has_key?(:on_delete)
      st += " ON DELETE #{options[:on_delete]}"
    end
    if options.has_key?(:on_update)
      st += " ON UPDATE #{options[:on_update]}"
    end
    execute st
  end

  # removes a foreign key constraint from a table. This will NOT delete the 
  # column in the table, it only deletes the constraint
  # Parameters:
  #   table_name - the name of the table to remove the foreign key contstraint 
  #                from
  #   constraint_name - the name of the constraint on the table to delete
  def remove_foreign_key(table_name, constraint_name)
    st = "ALTER TABLE #{table_name} DROP FOREIGN KEY #{constraint_name}"
    execute st
  end
end

 
To use this, in add a require for this file to your migration file. Then inside of the migration class, add extend ForeignKeyOps to the beginning of the class.

As an example, say you want to add a new table to your database for phones, and each phone belongs to a user. Here is the migration code, using ForeignKeyOps:

require 'db/migrate/foreign_key_ops'
class AddPhones < ActiveRecord::Migration
  extend ForeignKeyOps
  def self.up 
    create_table :phones do |t|
      t.column :account_id, :integer
      t.column :name, :string
      t.column :number, :string
      t.column :brand, :string
    end
    add_foreign_key('phones', 'fk_accounts_phones', 'account_id', 
                    'accounts', 'id')
  end

  def self.down
    drop_table :phones
  end
end

 
Again as a reminder this only works for MySQL.