3

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?

  • 1.Add owner_id to toy. 2.Add unique constraint on possession(owner_id, possession_id). 3.Change the FK to include both columns (owner_id, possession_id) 4.Then you can add the desired partial unique index on the subtype table. – ypercubeᵀᴹ Jan 17 '15 at 12:47
  • While neither owner_id or special are likely to be later updated for my purposes, I'd still prefer not to store the column twice if it's reasonably possible. A solution that doesn't might help someone else with a similar problem too. – lpd Jan 17 '15 at 14:15
  • Why is storing a column twice a problem? You don't seem to object that for the possession_id column. I don't think there is any other way to enforce what you want with DRI. – ypercubeᵀᴹ Jan 17 '15 at 23:48
  • Storing possession_id in both tables is fairly necessary, otherwise it would be impossible to join the attributes of both the supertype and subtype. (Sure, there are alternatives, but it's beside the point.) Unlike possession_id, owner_id is actually modifiable data and not an arbitrary identifier so it may be subject to updates and inconsistency. Increasing storage requirements by duplicating columns is also obviously undesirable. – lpd Jan 18 '15 at 11:03
2

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);
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.