How to resuse deleted model id number in Rails?

Question

Say I have a Post model. When I delete last post 'Post 24', I want the next post to take id of Post 24 and not Post 25.

I want to show id in views and I don't want missing numbers. How do I do that?

Thanks for your help.

Answer 1

The purpose of an id is to be nothing more than an internal identifier. It shouldn't be used publicly at all. This isn't a Rails thing, but a database issue. MySQL won't reclaim id's because it can lead to very serious complications in your app. If a record is deleted, its id is laid to rest forevermore, so that no future record will be mistaken for it.

However, there is a way to do what you want. I believe you want a position integer column instead. Add that to your model/table, and then install the acts_as_list plugin.

Install it the usual way:

script/plugin install git://github.com/rails/acts_as_list.git

Then add the "hook" to your model:

class Post < ActiveRecord::Base
  acts_as_list
end

Now the position column of your post model will automatically track itself, with no sequence gaps. It'll even give you some handy methods for re-ordering if you so choose.

Answer 2

Conversely, you could let the SQL do this itself:

SELECT rownum AS id, [whatever other columns you want]
FROM posts_table
WHERE [conditions]
ORDER BY [ordering conditions]

This will add numbers to each row without skipping any like you said.

NOTE: I use Oracle. I don't know if this exact code will work in other flavors.