how to add index conditionally

Question

say i have a model

class Post < ActiveRecord::Base
  validates_uniqueness_of :title, :unless => Proc.new {|p| p.deleted?}
end

The constraint is i can have only 1 post having "foobar" as its title while it's not deleted, and 1+ posts, which are all having deleted to be true, also having "foobar" as their titles. Since ActiveRecord can not guarantee the uniqueness of the title from this link, I'm trying to add a unique index to the table posts, on columns [:title, :deleted], it will fail the scenario when I try to insert a new deleted post to the db.

Answer 1

It's Possible in postgresql

add_index :table_name, :columns, unique: true, where: "(deleted_at IS NULL)"

Answer 2

It is not possible to have a conditional database index with most databases e.g. MySQL.

Probably the best option if you absolutely must guarantee uniqueness would be to have a separate table of deleted posts. You have the unique index on the main table, but not on the table containing deleted records. You would end up with a smaller posts table and simpler coding - there's no longer a need to filter out deleted posts in any queries.

Have you considered what should happen if you undelete a post - can two posts then have the same title?

Some other options are:

1. Change the title when you delete a post, e.g. add a timestamp.
2. Raise an exception after the model is saved if you can find 2 or more instances of the same title.
3. Lock the entire table before saving (pessimistic locking).
4. Just hope that it works (how often are you going to have two people posting the same title at the exact same time?)