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