Partial unique constraint spanning multiple tables in postgres

Question

To enforce partial uniqueness in postgres, it is a well known workaround to create a partial unique index instead of an explicit constraint, like so:

CREATE TABLE possession (
  possession_id serial PRIMARY KEY,
  owner_id integer NOT NULL REFERENCES owner(owner_id),
  special boolean NOT NULL DEFAULT false
);
CREATE UNIQUE INDEX possession_unique_special ON possession(owner_id, special) WHERE special = true;

This would restrict each owner to having no more than one special possession at the database level. It obviously isn't possible to create indices spanning multiple tables, so this method cannot be used to enforce partial uniqueness in a supertype & subtype situation where the columns exist in different tables.

CREATE TABLE possession (
  possession_id serial PRIMARY KEY,
  owner_id integer NOT NULL REFERENCES owner(owner_id)
);

CREATE TABLE toy (
  possession_id integer PRIMARY KEY REFERENCES possession(possession_id),
  special boolean NOT NULL DEFAULT false
);

As you can see, the earlier method does not allow for restricting each owner to no more than one special toy in this example. Assuming each possession must implement exactly one subtype, what is the best way to enforce this constraint in postgres without substantially altering the original tables?

lpd · Accepted Answer · 2015-12-06T12:07:10.807

Postgres directly supports table inheritance, which does the trick:

CREATE TABLE base_possession (
  possession_id SERIAL PRIMARY KEY
);

CREATE TABLE possession (
  possession_id INTEGER NOT NULL REFERENCES base_possession(possession_id),
  owner_id INTEGER NOT NULL REFERENCES owner(owner_id)
);

CREATE TABLE toy (
  special boolean NOT NULL DEFAULT false,
  PRIMARY KEY (possession_id),
  FOREIGN KEY (possession_id) REFERENCES base_possession(possession_id),
  FOREIGN KEY (owner_id) REFERENCES owner(owner_id)
) INHERITS (possession);
CREATE UNIQUE INDEX toy_unique_special ON toy(owner_id, special) WHERE special = true;

lpd · Answer 2 · 2021-08-11T13:06:42.587

Not an answer for postgres, but may be useful nevertheless to someone using Oracle who stumbles across this:

Oracle allows partial uniqueness to be enforced across multiple tables using a fast refreshing materialised view. Tom Kyte describes it here. In short, if a join produces any rows on commit, it violates a constraint on the materialised view.

Untested, but in principle it should work like so:

create materialized view log
  on possession with rowid;
create materialized view log
  on toy with rowid;
create materialized view one_special_toy_per_owner
  refresh fast
  on commit
  as select p.owner_id, count(t.special) as special
     from possession p, toy t
     where p.possession_id = t.possession_id
     group by p.owner_id
     having count(t.special) > 1;
alter table one_special_toy_per_owner
  add constraint owner_has_only_one_special_toy
  check (owner_id is null and special is null);

Partial unique constraint spanning multiple tables in postgres

2 Answers2