Uniqueness validation in database when validation

问题:

Using uniqueness validations in Rails is not safe when there are multiple processes unless the constraint is also enforced on the database (in my case a PostgreSQL database so see http://robots.thoughtbot.com/the-perils-of-uniqueness-validations).

In my case, the uniqueness validation is conditional: it should only be enforced if another attribute in the model becomes true. So I have

class Model < ActiveRecord::Base
  validates_uniqueness_of   :text, if: :is_published?

  def is_published?
    self.is_published
  end
end

So the model has two attributes: is_published (a boolean) and text (a text attribute). text should be unique across all models of type Model iff is_published is true.

Using a unique index as suggested in http://robots.thoughtbot.com/the-perils-of-uniqueness-validations is too constraining because it would enforce the constraint regardless of the value of is_published.

Is anyone aware of a "conditional" index on a PostgreSQL database? Or another way to fix this?

回答1:

Yes, use a partial UNIQUE index.

CREATE UNIQUE INDEX tbl_txt_is_published_idx ON tbl (text) WHERE is_published;

Example:
How to add a conditional unique index on PostgreSQL