ActiveRecord: Change foreign key to a field other than the join table’s id

A good example for this implementation is when you have a table of retail stores that references a table containing US states. By convention, when adding a state reference to the stores table, a state_id column is created:

class AddStateToStores < ActiveRecord::Migration
  def change
    add_reference :states, :store, index: true, foreign_key: true
  end
end

But say you want to see the human readable state code rather than its id when querying stores because joining two tables every time is too much work. You may want to change the foreign key from state_id to state_code.

Here is how it’s done step by step (referencing this SO answer):

– Add a state_code column to stores table and update the new state_code column during migration using a join statement:

class AddStateCodeToStores < ActiveRecord::Migration
  def up
    add_column   :stores, :state_code, :string
    
    query = "UPDATE stores AS A SET state_code = B.code FROM states AS B" +
            "WHERE A.state_id = B.id"
    ActiveRecore::Base.execute(query)
  end

  def down
    remove_column :stores, :state_code, :string
  end
end

– Specify a different foreign key for states:

class Store << ActiveRecord::Base
  belongs_to     :state, foreign_key: 'state_code',
                         primary_key: 'state_code'
end

– Now you can safely drop the state_id column from the stores table.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s