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.
