Uniqueness validation in database when validation has a condition on another table

Question

I asked a similar question here at Uniqueness validation in database when validation has a condition but my requirements have changed, hence this question.

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 on another model becomes true. So I have

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

So the model Child has two attributes: parent_id (association with Parent) and text (a text attribute). The model Parent has one attribute: is_published (a boolean). text should be unique across all models of type Child iff its parent.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 that depends on another table? The solution at Uniqueness validation in database when validation has a condition is when your condition depends on an attribute on the same table. Or another way to fix this?

Database-level constraints don't support this. If the is_published attribute was in the same table you could use a partial unique index, but not if it's in another table. I think you're going to have to do whole-table write locking (LOCK TABLE ... IN EXCLUSIVE MODE) and enforce the constraint client-side. — Craig Ringer
@CraigRinger: I think a redundant flag child.is_published, a multi-column fk and a partial unique index should do the job reliably. — Erwin Brandstetter
@ErwinBrandstetter Good point - not beautiful, but entirely effective I think. — Craig Ringer

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2014-07-09T01:22:24

Unfortunately, there is no solution quite as simple and clean as for your previous question.

This should do the job:

Add a redundant flag is_published to the Child table
```
ALTER TABLE child ADD column is_published boolean NOT NULL;
```
Make it DEFAULT FALSE or whatever you typically have in parent columns when inserting.
It needs to be NOT NULL to avoid a loophole with NULL values and default MATCH SIMPLE behaviour in foreign keys:
Two-column foreign key constraint only when third column is NOT NULL
Add a (seemingly pointless, yet) unique constraint on parent(parent_id, is_published)
```
ALTER TABLE parent ADD CONSTRAINT parent_fk_uni
UNIQUE (parent_id, is_published);
```
Since parent_id is the primary key, the combination would be unique either way. But that's required for the following fk constraint.
Instead of referencing parent(parent_id) with a simple foreign key constraint, create a multi-column foreign key on (parent_id, is_published) with ON UPDATE CASCADE.
This way, the state of child.is_published is maintained and enforced by the system automatically and more reliably than you could implement with custom triggers:
```
ALTER TABLE child
ADD CONSTRAINT child_special_fkey FOREIGN KEY (parent_id, is_published)
REFERENCES parent (parent_id, is_published) ON UPDATE CASCADE;
```

Then add a partial UNIQUE index like in your previous answer.

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

Of course, when inserting rows in the child table you are forced to use the current state of parent.is_published now. But that's the point: to enforce referential integrity.

Complete schema

Or, instead of adapting an existing schema, here is the complete layout:

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;

Uniqueness validation in database when validation has a condition on another table

1 Answers

Complete schema