当验证对另一个表有条件时，在数据库中进行唯一性验证

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(一个布尔值)。如果parent.is_published为真，那么text在Child类型的所有模型中应该是唯一的。

像http://robots.thoughtbot.com/the-perils-of-uniqueness-validations中建议的那样使用唯一索引约束太大，因为无论is_published的值如何，它都会强制执行约束。

有人知道PostgreSQL数据库上的"条件"索引依赖于另一个表吗?当验证具有条件时，数据库中唯一性验证的解决方案是当您的条件依赖于同一表上的属性时。或者有其他解决办法吗?

Answer 1

不幸的是，没有像你上一个问题那样简单明了的解决办法。

这应该可以完成工作:

• 为Child表添加冗余标志is_published

  ALTER TABLE child ADD column is_published boolean NOT NULL;
  

  将它设置为DEFAULT FALSE或者在插入时父列中通常使用的其他格式。
  它需要NOT NULL来避免NULL值和默认MATCH SIMPLE行为在外键中的漏洞:
  

• 在parent(parent_id, is_published)上添加一个(看似毫无意义的)唯一约束

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

  由于parent_id是主键，所以无论哪种组合都是唯一的。但这对于以下fk约束是必需的。

• 不使用简单的外键约束引用parent(parent_id)，而是使用ON UPDATE CASCADE在(parent_id, is_published)上创建多列外键。
  这样，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;