Seeding a Rails Database with Pre-set IDs

I have an XML file containing seed data that I'm trying to load into my production database.

The problem being that I have associations between different data types / nodes in the XML file, and so I need the specified IDs to be the same when loaded into MySQL from Rails.

This works fine with SQLite during development, I just use a line like the one below after iterating through each node in the XML file:

CardSet.connection.execute("UPDATE card_sets SET id = #{xml_set.attributes['id']} WHERE id = #{set.id}")

My question is: how can I force pre-set IDs when seeding the database, and then be able to turn auto_increment back on for Rails to function as normal?

I've attempted this with these two lines before creating the database entry:

CardSet.connection.execute("ALTER TABLE card_sets CHANGE id id INT(11) DEFAULT NULL")
CardSet.connection.execute("ALTER TABLE card_sets DROP PRIMARY KEY")

and this开发者_StackOverflow社区 after:

CardSet.connection.execute("ALTER TABLE card_sets CHANGE id id INT(11) DEFAULT NULL auto_increment PRIMARY KEY")

which MySQL fires back to me as:

Mysql::Error: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '18' for key 'PRIMARY': ALTER TABLE card_sets CHANGE id id INT(11) DEFAULT NULL auto_increment PRIMARY KEY

My schema:

create_table "card_sets", :force => true do |t|
  t.string   "name"
  t.datetime "created_at"
  t.datetime "updated_at"
end

create_table "card_sets_cards", :id => false, :force => true do |t|
  t.integer "card_set_id"
  t.integer "card_id"
end

create_table "cards", :force => true do |t|
  t.text     "question",   :default => ""
  t.text     "answer",     :default => ""
  t.datetime "created_at"
  t.datetime "updated_at"
end

And models:

class CardSet < ActiveRecord::Base
  has_and_belongs_to_many :cards, :uniq => true
end

class Card < ActiveRecord::Base
    has_and_belongs_to_many :card_set, :uniq => true
end

Any ideas would be appreciated.

My way around the MySQL error was to create the model instance normally (CardSet.create), and then force an update to the ID, and then reload the instance:

c = CardSet.create(...blah....)
CardSet.connection.execute("UPDATE card_sets SET id = #{real_card_set_id} WHERE id = #{c.id}")
c = CardSet.find(real_card_set_id)
c.save!

If you create your objects in block form, it'll work.

p = Post.new do |post|
  post.id = 1000
end

p.save