Monday, November 26, 2007

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.

5 comments:

Hugo said...

Good job! It's excellent for me :) Regards!

muthhus said...

Hi,

Its nice to have such module. but when i was trie i got following error:

** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:migrate
rake aborted!
undefined method `Migration' for ActiveRecord:Module
./db/migrate//20100416111740_add_foreign_key_to_upload.rb:2
/home/shanmuga/upload/vendor/rails/activesupport/lib/active_support/dependencies.rb:502:in `load'
/home/shanmuga/upload/vendor/rails/activesupport/lib/active_support/dependencies.rb:502:in `load'
/home/shanmuga/upload/vendor/rails/activesupport/lib/active_support/dependencies.rb:354:in `new_constants_in'
/home/shanmuga/upload/vendor/rails/activesupport/lib/active_support/dependencies.rb:502:in `load'
/home/shanmuga/upload/vendor/rails/activerecord/lib/active_record/migration.rb:454:in `migrations'
/home/shanmuga/upload/vendor/rails/activerecord/lib/active_record/vendor/mysql.rb:188:in `inject'
/home/shanmuga/upload/vendor/rails/activerecord/lib/active_record/migration.rb:440:in `each'
/home/shanmuga/upload/vendor/rails/activerecord/lib/active_record/migration.rb:440:in `inject'
/home/shanmuga/upload/vendor/rails/activerecord/lib/active_record/migration.rb:440:in `migrations'
/home/shanmuga/upload/vendor/rails/activerecord/lib/active_record/migration.rb:406:in `migrate'
/home/shanmuga/upload/vendor/rails/activerecord/lib/active_record/migration.rb:357:in `up'
/home/shanmuga/upload/vendor/rails/activerecord/lib/active_record/migration.rb:340:in `migrate'
/home/shanmuga/upload/vendor/rails/railties/lib/tasks/databases.rake:99
/usr/lib/ruby/1.8/rake.rb:546:in `call'
/usr/lib/ruby/1.8/rake.rb:546:in `execute'
/usr/lib/ruby/1.8/rake.rb:541:in `each'
/usr/lib/ruby/1.8/rake.rb:541:in `execute'
/usr/lib/ruby/1.8/rake.rb:508:in `invoke_with_call_chain'
/usr/lib/ruby/1.8/rake.rb:501:in `synchronize'
/usr/lib/ruby/1.8/rake.rb:501:in `invoke_with_call_chain'
/usr/lib/ruby/1.8/rake.rb:494:in `invoke'
/usr/lib/ruby/1.8/rake.rb:1931:in `invoke_task'
/usr/lib/ruby/1.8/rake.rb:1909:in `top_level'
/usr/lib/ruby/1.8/rake.rb:1909:in `each'
/usr/lib/ruby/1.8/rake.rb:1909:in `top_level'
/usr/lib/ruby/1.8/rake.rb:1948:in `standard_exception_handling'
/usr/lib/ruby/1.8/rake.rb:1903:in `top_level'
/usr/lib/ruby/1.8/rake.rb:1881:in `run'
/usr/lib/ruby/1.8/rake.rb:1948:in `standard_exception_handling'
/usr/lib/ruby/1.8/rake.rb:1878:in `run'
/usr/bin/rake:28


Would you please help me fix the issues;

Thanks

Rgdsmuthhus

Brent said...

muthhus - I'm not really sure what would cause that, it sounds like something is not correct with ActiveRecord. What version of Rails are you using?

Anonymous said...

Same error here (Undefined method `Migration` for ActiveRecord:Module).

version? Rails 3

Anonymous said...

Fixed. The problem was:

class MyMigration < ActiveRecord::Migration extend MigrationHelpers

instead of:

class MyMigration < ActiveRecord::Migration
extend MigrationHelpers