Re: Cross-table constraints

On Tue, Jan 28, 2003 at 20:05:15 +1300,
Rodger Donaldson <rodgerd(at)diaspora(dot)gen(dot)nz> wrote:
>
> The problem: I have two tables, add_queue and sites, on a postgresql
> 7.2.x database. add_queue is where items go to be reviewed by a human
> before being moved into sites. One of the things I'd like to do is to
> cut down on the amount of work done by humans filtering out dupes and
> sundry other problems.

Create a third table with three id columns. The first is the primary
key and the other two tables should reference it. The second and third
should allow for nulls and each reference the id field in one of the
other two tables. You also need a table constraint that forces exactly
one of these two fields to be null. And two more constraints to make
sure they match the primary key when they aren't null.

This should make sure each id is in exactly one of the two original tables
and that any id in the two original tables is in the new table.