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?