Re: check constraint

On Mon, Jun 02, 2003 at 16:00:43 +0200,
erwan ancel <erwan(dot)ancel(at)free(dot)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.