Validation of uniqueness in a database when validation has a condition

Question

Using uniqueness checks in Rails is unsafe when there are several processes, unless the restriction is imposed on the database (in my case, the PostgreSQL database, so see this blog post ).

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

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

  def is_published?
    self.is_published
  end
end

Thus, the model has two attributes: is_published(logical value) and text(text attribute). textmust be unique for all models of the type Modelif is_published- true.

Using a unique index (as suggested in a related blog post) is too limited because it will apply the restriction regardless of value is_published.

Does anyone know of a "conditional" index in a PostgreSQL database? Or another way to fix it?

Answer 1

Yes, use the 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 in PostgreSQL

Answer 2

, - - . . , , (, MySQL <8) ( ).

:

• () Rails , /, .

: https://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html

#save, #destroy , , , , .

. .

, (https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html) , . , - , - . "-" .

:

class Model < ActiveRecord::Base
  validates :text, uniqueness: {
    conditions: ->{ lock.where(is_published: true) }
  }
end

, , -. , .