Re: check constraint

On Mon, Jun 02, 2003 at 16:00:43 +0200,
  erwan ancel <erwan.ancel@free.fr> wrote:
>
> well, no... these are not direct foreign keys. The constraint here is
> that for a given record of D, B pointed by A pointed by the given D must
> be the same as B pointed by C pointed by the given D.

I believe one way to do this is with after triggers.

Another way to do it is by storing the key for B in D. Then change the foreign
key references into A and C to use the primary keys for A and C combined with
the value of the primary key for B stored in D.

Something like:

create table B (
  bkey serial primary key
);

create table A (
  akey serial primary key,
  bkey int references B
);
create unique index aindex on A(akey,bkey);

create table C (
  ckey serial primary key,
  bkey int references B
);
create unique index cindex on C(ckey,bkey);

create table D (
  dkey serial primary key,
  akey int,
  bkey int references B,
  ckey int,
  foreign key (akey, bkey) references A (akey, bkey),
  foreign key (ckey, bkey) references A (ckey, bkey),
);

I haven't actually tested the above statements, so there might be some
syntax errors or typos in them, but it should lay out the idea for you
to use.