Modifying Database IDs from Rails Console?

Question

I have a small database and have been adding entries through a Rails page. I "destroyed" one of the entries and now my sequence of IDs are skipping by one. For example, I now have 42 then 44, instead of the obvious: 42, 43, 44.

I was wondering if there was a way to edit the ID number of a new object through the console. I have tried:

record.id = 43
record.save

and

record = record.new
record.attributes = { :id => 43 }

but both didn't work. I'm fairly certain there has to be a console method for this, but I can't seem to find much specific on Google and I probably read the Rails API incorrectly... Would I possibly have to do this through direct SQL in sqlite?

Thanks

Answer 1

Would I possibly have to do this through direct SQL in sqlite?

Yes.

The whole point of ActiveRecord is that is abstracts DB functions and just returns collections of data. You shouldn't be worrying about the ID of a record, that is something specific to the DB. Off the top of my head I can't think of any reasons to reference the model's ID.

If your app depends on having a sequenced number then you should add another field to the model which has this. For instance, if I have a store with products (a Product model) and I give the ID number the DB provides to other vendors. Two weeks later, my boss asks me to have a unique, but similar ID for two variations of products: "45a" and "45b". Nuts. The ID field should only be used by to the database and ActiveRecord, not you or your users, to identify the record.

There is a small chance that there might be an obscure method which force sets the ID if the DB allows it, but it is obscure for a reason. Don't try and find it :)

All that being said, type ruby script/dbconsole to quickly pull up the sqlite interface without having to type your password.

Also, if you delete the sqlite database that will reset the counter and start at 0. With great power comes great responsibility.

EDIT

If I remember correctly Dave Thomas wrote about this somewhere. Perhaps here?

Answer 2

Actually, you can set the id manually for new objects:

record = Record.new
record.id = 1234
record.save

However, what you're trying to do is update the id of an existing object. When you set record.id = 43 and then call save what happens is that ActiveRecord will try to generate SQL like this:

update records set id = 43 where id = 43

Notice that the id it's looking for to update is the same as the one you're trying to change. That's why it doesn't work.

So yes, you would have to use SQL to change this. Whether or not that's a good idea is another issue, but sometimes it needs to be done.

Answer 3

The best way to do it is to execute the SQL directly, and solve this temporal glitch in the sequence.

Try accessing the console (ruby script/console) and type:

>> sql = "update records set id=43 where id=44"
>> ActiveRecord::Base.connection.execute(sql)

Where 44 is the newly created object's id, and 43 is the one you were missing in your table

Good luck!