current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
    
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 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 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 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 at 11:03

1 Answer 1

up vote 0 down vote

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);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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