Adding a foreign key colum via migration

Hi.

Rails doesn't use foreign keys to perform his backend tasks.
This because some db like sqlite doesn't allow foreign keys on its tables.
So Rails doesn't provide an helper to build a foreign key.

If you are using MySql, you can build a library like this:

#migration_helpers.rb
module MigrationHelpers
  def add_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)}
  end

  def remove_foreign_key(from_table, from_column)
    constraint_name = "fk_#{from_table}_#{from_column}"
    execute %{alter table #{from_table}
              drop foreign key #{constraint_name}}
  end
end

and then build your migration like this:

require "migration_helpers"
class AddToPostUserForeignKey < ActiveRecord::Migration
  extend MigrationHelpers
  
  def self.up
    add_foreign_key(:posts, :user_id, :users)
  end
  def self.down
    remove_foreign_key(:posts, :user_id)
  end
end

Remember that using this new migration, your application will no longer be database agnostic.

I hope you find my reply useful.

Cheers,
Stefano

This may assist you as well, but I don't know if you can use it via the comand line in script/generate

TableDefinition#references lets you put something like this is your migration:

def self.up
  create_table :products do |t|
    # ...
    t.references :product_line
    # ...
  end
end

which gives you an integer column named product_line_id in your products table

Ah, well I don't think thats possible.  As far as I know script/generate migration can only make migrations for new tables.

dudes, i got the same doubt... if is possible to add a references in a model after it be created...

cause i like these references without foreign key... but it's hard to can't make future changes...

something like:
./script/generate migration add_user_to_comment user:references

would be very nice... have some way?!

Last edited by tiagomac (2009-01-06 22:18:19)

Hi Guys,

I've been working on a version of the migration helper to add a FK to a table using migration tools.

# migration_helpers.rb
module MigrationHelpers
  
  def add_foreign_key(from_table, from_column, to_table)
    constraint_name = "fk_#{from_table}_#{from_column}"
    column_name = "#{from_table}_#{from_column}"
    #execute %{alter table #{to_table}
    #          add #{column_name} INT(11) not null,
    #          add index #{column_name},
    #          add CONSTRAINT #{constraint_name}
    #          foreign key #{column_name}
    #          references #{from_table}(#{from_column})
    #          }
    execute %{alter table #{to_table} add #{column_name} INT(11) not null}
    execute %{alter table #{to_table} add index (#{column_name})}
    execute %{alter table #{to_table} add constraint #{constraint_name} foreign key (#{column_name}) references #{from_table}(#{from_column})}
  end

  def remove_foreign_key(from_table, from_column, to_table)
    constraint_name = "fk_#{from_table}_#{from_column}"
    column_name = "#{from_table}_#{from_column}"
    execute %{alter table #{to_table} drop foreign key #{constraint_name}}
    execute %{alter table #{to_table} drop INDEX #{column_name}}
    execute %{alter table #{to_table} drop #{column_name}}
  end
end

and the migration

#add_user_to_post.rb
require "migration_helpers"
class AddUserToPost < ActiveRecord::Migration
   extend MigrationHelpers
   def self.up
     #add_foreign_key (:mainentity, :mainentity_id, secundaryentity)
     add_foreign_key(:users, :id, :posts)
   end
   def self.down
     #add_foreign_key (:mainentity, :mainentity_id, secundaryentity)
     remove_foreign_key(:users, :id, :posts)
   end
end

How to use:

1. Create the migration ./script/generate migration add_user_to_post
2. Edit db/migrate/XXXXXXXX-add_user_to_post.rb
3. Run rake db:migrate, even works downgrading migration version.

Enjoy and comment.

Cheers.

Thanks nice guys!!

Below my modified version. Actually, mysql creates index automatically if you specify the index name. So I add it to alter table command for foreign key and remove create index command.
I also make two method for create/remove index. :-)

module MigrationHelpers
  def add_foreign_key(from_table, from_column, to_table, options = {})
    constraint_name = foreignkey_name(from_table, to_table)
    column_name = foreignkey_column_name(from_table, from_column)
    
    execute %{alter table #{to_table} add #{column_name} INT not null} if options[:add_column]
    execute %{alter table #{to_table} 
                add constraint #{constraint_name} foreign key
                #{index_name(column_name)} (#{column_name}) references #{from_table}(#{from_column})
                #{ options[:cascade].map{ |cas| " ON #{cas} CASCADE" }.join if options[:cascade] }
            }
  end
  def remove_foreign_key(from_table, from_column, to_table, options = {})
    constraint_name = foreignkey_name(from_table, to_table)
    column_name = foreignkey_column_name(from_table, from_column)
    execute %{alter table #{to_table} drop foreign key #{constraint_name}}
    execute %{alter table #{to_table} drop #{column_name}} if options[:remove_column]
  end
  def create_index(table, *columns)
    execute %{create index #{index_name(columns)} on #{table} (#{columns.join(',')}) }
  end
  def remove_index(table, *columns)
    execute %{drop index #{index_name(columns)}on #{table}}
  end
  private
  def foreignkey_column_name(from_table, from_column)
     "#{from_table.to_s.singularize}_#{from_column}"
  end
  def foreignkey_name(from_table, to_table)
    "fk_#{from_table}_to_#{to_table}"
  end
  def index_name(*columns)
    "idx_#{columns.join('_')}"
  end
end

Hi, I'm currently struggling with (I guess) a foreign key issue that relates directly to your, (presumably hypothetical) migration script...

I am attempting to enable display of the (logged on) Users name along with the comment.

I have a comments table with both story_id:int AND user_id:int columns.

I added user has_many :comments, story has_many :comments, and comment belongs_to :story [and] :user in the respective [model].rb's.

Comment form and display both happen on the story page:

  <%=h @story.name %></span></a><br />
  <span class="caption">
     <%= render :partial => 'comment', :collection => @story.comments %>
  </span><br />
  
  <% form_for @comment do|f| %>
  <%= f.error_messages %>
  <%= f.hidden_field :story_id %>
  <%= f.hidden_field :user_id %>
<p>
  <%= f.label :"Add a Comment" %><br />
  <%= f.text_area :comment %>
</p>
<p>
  <%= f.submit "Submit" %>
</p>
    <% end %>

And here is my comments_controller.rb:

class CommentsController < ApplicationController
  before_filter :login_required, :only => [ :create, :destroy ]
  def create
    @comment = Comment.new(params[:comment])
    
    if @comment.save
      flash[:notice] = 'Comment was successfully created.'
      redirect_to(@comment.story)
    else
      flash[:notice] = "Error creating comment: #{@comment.errors}"
      redirect_to(@comment.story)
    end
  end
  
  def destroy
    @comment = Comment.find(params[:id])
    @comment.destroy
    
    redirect_to(@comment.story)
  end
end

When I post a comment and review the output in the console, I can see the user_id is being flagged correctly in mysql, but it isn't being passed along by CommentsController#create to be saved to the record (saved as "null").

The story_id is being flagged and saved. along with the body of the comment and timestamp:

---- console output ---
Processing CommentsController#create (for 127.0.0.1 at 2009-07-09 16:57:15) [POST]
  Parameters: {"comment"=>{"comment"=>"...", "story_id"=>"3", "user_id"=>""}, "commit"=>"Submit", "authenticity_token"=>"..."}

[0mSELECT * FROM `users` WHERE (`users`.`id` = 1) LIMIT 1
[0;1mINSERT INTO `comments` (`comment`, `created_at`, `updated_at`, `story_id`, `user_id`) VALUES('...', '2009-07-09 20:57:15', '2009-07-09 20:57:15', 3, NULL)
----------------------
So, in a nutshell, the new comment record is picking up the story_id, but not the user_id.

Any ideas on how this can be fixed? Is it a foreign key issue?

I feel like I'm just a line or two of code away from getting this to work...

Thanks!