Foreign key and rails migration

written by seb on July 29th, 2006 @ 11:02 AM

In the book Agile Web Development with Rails 2ed I found a great migration helper that need to be shared.

UPDATE: The plugin can make your life even easier as it automatically handles adding foreign key constraints.

UPDATE: This article where moved and comments removed, many tips where added to this article but it seem that the following plugin may make your forget about foreign key problems.

It allow you to set foreign key easily, so let’s go ahead and write some code!

In lib/migration_helpers.rb

module MigrationHelpers
  def foreign_key(from_table, from_column, to_table)
    constraint_name = "fk_#{from_table}_#{from_column}" 

    execute %{alter table #{from_table}
              add constraint #{constraint_name}
              foreign key (#{from_column})
              references #{to_table}(id)}

now in the migration that need a foreign key, example db/migrate/001_create_articles.rb you may need to had 2 lines of code

At the first line of your migration file:

require "migration_helpers" 

inside the class:

extend MigrationHelpers

now let’s see what need to be done to create a foreign key in this migration file:

foreign_key(:articles_users, :article_id, :articles)

the final result could look at something like this:

require "migration_helpers" 

class CreateArticles < ActiveRecord::Migration
  extend MigrationHelpers

  def self.up
    create_table :articles do |t|
      t.column :user_id,    :int
      t.column :title,      :string, :limit => 100
      t.column :body,       :text
      t.column :created_on, :datetime
      t.column :updated_on, :datetime

    foreign_key(:articles, :user_id, :users)

  def self.down
    drop_table :articles

that’s done, you ready to run your migration (rake db:migrate).

Comments are closed