Using Legacy Primary Keys in Rails
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