Проверка уникальности в базе данных, когда проверка имеет условие для другой таблицы

Question

Я задал подобный вопрос здесь при проверке уникальности в базе данных, когда проверка имеет условие, но мои требования изменились, отсюда и этот вопрос.

Использование проверок уникальности в Rails небезопасно, когда есть несколько процессов, если только ограничение не наложено на базу данных (в моем случае база данных PostgreSQL, см. http://robots.thoughtbot.com/the-perils-of-uniqueness-validations).

В моем случае проверка уникальности является условной: ее следует применять только в том случае, если другой атрибут в другой модели становится истинным. Так что я

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 (логическое). 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 поведение во внешних ключах:
  Ограничение внешнего ключа из двух столбцов, только если третий столбец НЕ равен 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;
  

• Затем добавьте частичный УНИКАЛЬНЫЙ индекс, как в предыдущем ответе.

  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;