Title: How do I handle auto incrementing primary keys during migration
Tags: ruby-on-rails;dbmigrate
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 =&gt; 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.



Here is another answer: 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
```


Here is another answer: 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.


Here is another answer: 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```!

Comment for this answer: 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.
Comment for this answer: 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.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2011:00:12/02/11', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<ruby-on-rails><dbmigrate>', 'Title': 'How do I handle auto incrementing primary keys during migration', 'ViewCount': 1024, 'id': 8353395, 'user_AboutMe': '', 'user_AccountId': 985967.0, 'user_DisplayName': 'priya', 'user_Reputation': 22853.0, 'user_Views': 673.0, 'user_id': 1004443.0}}