How do I handle auto incrementing primary keys during migration

Question

create table foo (id auto increment primary key, name varchar(255))

I wrote a simple rails migration script which creates a new record on self.up and drops it on self.drop with delete(:id => 1). If I perform db:migrate it creates a new entry with id=1 and if I rollback it gets removed. The problem occurs if I migrate / drop again as the record gets created with primary key id=2 and my drop script fails on a rollback.

It is very important that the primary key is the same every time as I have other dependencies based on that. What should be the right way to handle this.

score 2 · Answer 1 · answered Dec 02 '11 at 08:37

2

You should not use migrations for adding seed data, use the db/seed.rb file instead. This will reinstall your seed data when you do rake db:reset, this will also reset your id counters so the data will have predictable ids. Data added through migrations will not be reloaded when doing a rake db:reset!

answered Dec 02 '11 at 08:37

harald

5,976
1
24
41

Will this drop and re-create the seed data, this might fail since there will be other records which have a foreign key dependency on the seed data. – priya Dec 06 '11 at 04:12
Yes, db:reset will drop and recreate the database containing just the seed data. So it's not the way to go in a production environment where you have data you want to keep in your database. – harald Dec 06 '11 at 07:58

score 0 · Answer 2 · answered Dec 02 '11 at 08:27

0

I think the right way to handle it is dropping the table instead of deleting records. As you create the table in your self.up method you can perform a drop on it.

 def down
    drop_table :system_settings
  end

answered Dec 02 '11 at 08:27

greenLizard

2,326
5
24
30

score 0 · Answer 3 · answered Dec 02 '11 at 08:40

The thing that does this is AUTO_INCREMENT option in MySQL. I dont think ActiveRecord allows you to turn it off. However you could turn it off by removing AUTO_INCREMENT value for the id field in MySQL (you will have to execute a MySQL query for this.)

On second thought, you are maintaining some Orphan records in your database. I dont believe this would be the right thing to do. Try deleting these things from the Rails console with models properly setup to delete dependent records when the mother record gets deleted and then use some rake scripts or something to add the data again and set up required associations.

How do I handle auto incrementing primary keys during migration

3 Answers3