I have written a small module which let you add MySQL foreign key constraints in Rails migrations. This functionality is missing in Rails 2.*.
migration_helper.rb
module MigrationHelper
#add foreign key constraint
#Options can be :override_column => string, :referential_action => string
#:override_column - Override column name. It is inferred from 'from_table'
#:referential_action - can be any or concatenation of both strings.
#'ON UPDATE [RESTRICT | CASCADE | SET NULL | NO ACTION]'
#'ON DELETE [RESTRICT | CASCADE | SET NULL | NO ACTION]'
def add_foreign_key(from_table, to_table, options={})
if options[:override_column].blank?
column_name = foreignkey_column_name(to_table)
else
column_name = options[:override_column]
end
constraint_name = foreignkey_name(from_table, to_table, column_name)
query = "alter table #{from_table}
add constraint #{constraint_name}
foreign key (#{column_name})
references #{to_table}(id) "
query = query + options[:referential_action].to_s unless options[:referential_action].blank?
execute query
end
#removes foreign key
#Options: :override_column
#override_column - Override column name. See above.
def remove_foreign_key(from_table, to_table, options={})
if options[:override_column].blank?
column_name = foreignkey_column_name(to_table)
else
column_name = options[:override_column]
end
constraint_name = foreignkey_name(from_table, to_table, column_name)
execute %{alter table #{from_table} drop foreign key #{constraint_name}}
end
private
def foreignkey_column_name(table)
"#{table.to_s.singularize}_id"
end
def foreignkey_name(from_table, to_table, column_name)
"fk_#{from_table}_#{column_name}_to_#{to_table}"
end
end
Usage:-
- Copy migration_helper to lib folder of your rails project.
- Add the following line of code in rails initializer. If you are using RAILS 2.3.5 or above add this line toinitializers/new_rails_defaults.rb:-
ActiveRecord::Migration.extend(MigrationHelper)
- Now, in migrations you can add foreign key constraints as:-
add_foreign_key(from_table, to_table, options={})
- To remove foreign_Key remove_foreign_key(from_table, to_table, options={})
To see the available options refer comments in a file.
Feel free to give your feedback or give me a call for any queries.
