Re: Multitable uniqueness ?

On Wed, May 26, 2004 at 05:13:14 +0200,
Andreas <maps(dot)on(at)gmx(dot)net> wrote:
>
> Is there a way to have something like this : UNIQUE (table_1.id,
> table_2.xxx)

Postgres doesn't support database constraints at this time which is
what you would need to do this simply.

You can enforce this constraint by creating a third table that has the
ids, id type and a separate id type field for each possible type that
will be null except for the type field that matches the actual type.
You also need to add a type field to the original two tables. Then you
set up a composite foreign key from the new table to each of the per type
tables using the id and the matching id type fields. Each per type table
should have a foriegn key refernce for id to the combined table.
This will enforce a 1-1 relationship between the combined table and the
union of the per type tables.

It is probably possible to get rid of the redundant copies of the field type
using triggers, but I don't know that there is much of a benefit to doing so.
The redundant values will all be kept in sync with constraints, so there isn't
a data consistancy problem with doing it that way. This method is going to be
more portable than using triggers. This method will probably be within a
constant factor as efficient as anything you do with triggers.