From: | Scott Holdren <scott(at)monsterlabs(dot)com> |
---|---|
To: | pgsql-general(at)hub(dot)org |
Subject: | multiple referential integrity |
Date: | 2000-09-18 17:34:15 |
Message-ID: | Pine.LNX.4.21.0009181233520.10919-100000@131 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
suppose i have two tables whose primary keys i want to be generated from
the same sequence and a third table where i want to establish a foreign
key based on a primary key from either of the two initial tables whose
id's are from the same sequence.
e.g.,
t1 t2
-- --
id id
t3
--
t_id
where t1.id and t2.id each get their values from, say, t_id_seq, and
t3.t_id references t1.id and references t2.id.
i thought this might be possible in postgres by specifying two references
as constraints in t3, e.g.,
create table t3 (
t_id <type> references t1( id ) references t2( id )
this is valid in postgres. unfortunately, the behavior seems to be that
it expects _both_ tables t1 and t2 to have the same value in order to
insert successfully into t3, e.g.,
insert into t1 ( id ) values( 1 );
insert into t3 ( t_id ) values( 1 );
will cause an error because it can't find "1" in t2.id.
is there any way to have stronger referential integrity in such a
situation than by merely relying on the unique values of a sequence such
as t_id_seq, which is shared by two (or more) tables for generation of
primary key values?
any suggestions/explanations would be much appreciated
-tfo
From | Date | Subject | |
---|---|---|---|
Next Message | Alexey V. Borzov | 2000-09-18 18:07:23 | WTF is going on with PG_VERSION? |
Previous Message | Patrick Welche | 2000-09-18 17:15:38 | Re: Re: LOCK TABLE |