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
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
create_table (:legacy_items, :primary_key => "legacy_key") do |t|
t.column :description, :string
and in the model calling
This wasn’t quite enough because I still wasn’t able to manually set an
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
column by pretending that we were creating a join table by setting the
: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
# 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
# Populate the table
grp = CarrierGroup.new
grp.id = 0 # Set id not code!
grp.description = 'Foreign Carriers'
grp = CarrierGroup.new
grp.id = 1
grp.description = 'Large Regional Carriers'
I also had to manually override the primary key in the model:
class CarrierGroup ActiveRecord::Base