Validation of uniqueness in a database when validation has a condition for another table

Question

I asked a similar question here, Uniqueness check in the database, when the check has a condition , but my requirements have changed, so this question.

Using uniqueness checking in Rails is unsafe if there are several processes, if the restriction also does not apply in the database (in my case it is a PostgreSQL database, see http://robots.thoughtbot.com/the-perils-of-uniqueness-validations )

In my case, the uniqueness check is conditional: it should be used only if another attribute on the other model becomes true. So i

class Parent < ActiveRecord::Base
  # attribute is_published
end

class Child < ActiveRecord::Base
  belongs_to :parent

  validates_uniqueness_of :text, if: :parent_is_published?

  def parent_is_published?
    self.parent.is_published
  end
end

, Child : parent_id ( Parent) text ( ). Parent : is_published (boolean). text Child, parent.is_published .

, http://robots.thoughtbot.com/the-perils-of-uniqueness-validations, , is_published.

- "" PostgreSQL, ? , , , . ?

Answer 1

, , .

:

• is_published Child

  ALTER TABLE child ADD column is_published boolean NOT NULL;
  

  DEFAULT FALSE , .
  NOT NULL, NULL MATCH SIMPLE :
  , NOT NULL

• ( , , parent(parent_id, is_published)

  ALTER TABLE parent ADD CONSTRAINT parent_fk_uni
  UNIQUE (parent_id, is_published);
  

  parent_id , . fk.

• parent(parent_id) , (parent_id, is_published) ON UPDATE CASCADE.
  , child.is_published , :

  ALTER TABLE child
  ADD CONSTRAINT child_special_fkey FOREIGN KEY (parent_id, is_published)
  REFERENCES parent (parent_id, is_published) ON UPDATE CASCADE;
  

• UNIQUE, .

  CREATE UNIQUE INDEX child_txt_is_published_idx ON child (text)
  WHERE is_published;
  

, Child parent.is_published. : .

, , :

CREATE TABLE parent(
    parent_id serial PRIMARY KEY
  , is_published bool NOT NULL DEFAULT FALSE
--, more columns ...
  , UNIQUE (parent_id, is_published)   -- required for fk
);

CREATE TABLE child (
    child_id serial PRIMARY KEY
  , parent_id integer NOT NULL
  , is_published bool NOT NULL DEFAULT FALSE
  , txt text
  , FOREIGN KEY (parent_id, is_published)
      REFERENCES parent (parent_id, is_published) ON UPDATE CASCADE
);

CREATE UNIQUE INDEX child_txt_is_published_idx ON child (text)
WHERE is_published;