Using legacy primary keys in Rails

Durham, February 5, 2007

I have a very large legacy database which I want to build Rails application around. This database does not conform to the usual conventions in that the natural primary keys are not always auto-incrementing integers. In particular, in the example below, the primary key for a small lookup table begins at zero. MySQL will not accept a value of 0 for a typical Rails auto-increment primary key. Other nonstandard database problems, such as column names, are not quite as difficult to work around. This particular case turned out to be more delicate, but the solution is fairly simple to implement. This approach is also more general in that it will work for other types of nonstandard primary keys such as strings.

One option would be to load each table line by line, look up the corresponding legacy primary key in order to determine the new rails id. This seems terribly inefficient, requiring a SELECT for each INSERT. This might not be a problem for small applications, but the tables in question contain around a hundred million observations. This option would generate too much unnecessary work for the database server.

I had tried simply using a standard migration such as

class CreateLegacyItems  ActiveRecord::Migration
  def self.up
    create_table (:legacy_items, :primary_key => "legacy_key") do |t|
      t.column :description, :string
    end
  end

  def self.down
    drop_table :legacy_items
  end
end

and in the model calling

set_primary_key 'legacy_key'

This wasn’t quite enough because I still wasn’t able to manually set an id equal to zero. With the underlying MySQL code being written with the auto-increment option, this was being ignored.

The solution was to fool Rails into forgetting to auto-increment the id column by pretending that we were creating a join table by setting the attribute :id => false. I will give an example using the code for a simple lookup table which holds several air carrier group descriptions. The model is named CarrierGroup. I used the following migration:

class CreateCarrierGroups  ActiveRecord::Migration

  def self.up
    # Create the table, set the primary key to 'code'
    create_table (:carrier_groups, :id => false, :primary_key => 'code') do |t|
      t.column :code, :integer                    # Primary key
      t.column :description, :string              # Group description
    end

    # Populate the table
    grp = CarrierGroup.new
    grp.id = 0                                    # Set id not code!
    grp.description = 'Foreign Carriers'
    grp.save

    grp = CarrierGroup.new
    grp.id = 1
    grp.description = 'Large Regional Carriers'
    grp.save

    # ...
  end

  def self.down
    drop_table :carrier_groups
  end
end

I also had to manually override the primary key in the model:

class CarrierGroup  ActiveRecord::Base
  set_primary_key 'code'
end