In Ruby on Rails, how to create a record with ID 3 when it was destroyed earlier?

Question

I am trying an experimental Rails project and destroyed record 3 (ID == 3), so now there is record 1, 2, 4, and 5.

Is there a way to repopulate this table with record ID from 1 to 5? (this is using mysql)

I can use

if Item.exists?(i)
  item = Item.find(i)
else
  item = Item.new
end

# set some values
item.name = "haha" + i
item.save

Item.new() is to create new records with new autoincrement record IDs (6 in this case), but what about the ones that were deleted earlier?

Answer 1

You can set the ID using:

item = Item.new
item.id = i

You cannot pass in :id => i to Item.new because id is a special attribute, and is automatically protected from mass-assignment.

You can also tell MySQL to start counting from 1 again, which will automatically fill in all missing IDs:

Item.connection.execute("ALTER TABLE items AUTO_INCREMENT = 1")

(Actually, MySQL docs say that this it will reset it to MAX(id) + 1, so it won't end up using 3 in this case.)

Answer 2

MySQL won't do this for you, as you likely know. On every single item creation, you can go back and see if there are any unused ID numbers, and use those instead. (I bet you can set id explicitly, though I haven't tried.) There are two reasons why I wouldn't recommend this:

• It will significantly slow down record creation when you get to a large number of records.
• An item's ID generally is meant to refer to it forever. Especially if you plan to use the ID in URLs, you shouldn't reuse an ID number if it was ever used before, to maintain data consistency. A deleted item should have all traces removed.

That is, if you're talking about using unused ID numbers on each item creation once you reach production. If you're talking about just a one-time deal since not having item #3 bugs you... your time is better spent working on something else.