multiple referential integrity

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

Browse pgsql-general by date

  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