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

database-design, indexing, postgresql, ruby-on-rails, unique-index

Solution

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;

Problem

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?

Original source