Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to add index conditionally

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.

like image 876
leomayleomay Avatar asked Dec 01 '22 02:12

leomayleomay


2 Answers

It's Possible in postgresql

add_index :table_name, :columns, unique: true, where: "(deleted_at IS NULL)"
like image 117
veeresh yh Avatar answered Dec 03 '22 16:12

veeresh yh


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?)
like image 26
Cameron Walsh Avatar answered Dec 03 '22 15:12

Cameron Walsh