Partial unique constraint spanning multiple tables in postgres

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?

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;

StackExchange Database Administrators Q#89534, answer score: 4