Sorry; Arrows are going the wrong way (seems like that caused some confusion). Corrected: a c b +----------+ +---------+ +----------+ | PriKey1 |<---| ForKey |--->| PriKey2 | | | | Flag | | | +----------+ +---------+ +----------+ Kind Regards, -Ryan Riehle http://www.buildways.com -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bruno Wolff III Sent: Saturday, May 01, 2004 4:18 PM To: Ryan Riehle Cc: pgsql-general@postgresql.org Subject: Re: 1 foreign key to 2 different tables? On Sat, May 01, 2004 at 14:51:14 -0400, Ryan Riehle <rkr@buildways.com> wrote: > Hi, > > I have a key structure like so: > > a c b > +----------+ +---------+ +----------+ > | PriKey1 |--->| ForKey |<---| PriKey2 | > | | | Flag | | | > +----------+ +---------+ +----------+ > > ...where c.ForKey is a value from PriKey1 OR PriKey2, which are > different values. All fields have the same data type; a.PriKey1 and > b.PriKey2 are sequences. How does one enfore referential integrity in > this structure so that c.ForKey references a.PriKeya when Flag is True > or references b.PriKey2 when Flag is False? Looked pretty hard through > the lists and on Google last night with no luck :( Your diagram seems to indicate something other than what you said. If c is supposed to reference a or b from one field I don't think you will be able to do that without writing your own triggers. If you can use two fields you call use NULL in the one that isn't active and use constraints to make sure exactly the one that is suppused to be nonNULL is. If you really have a and b pointing to c, then duplicate flag and a and b and use a combined foreign key reference of the primary key and the flag into c. Use constraints to make sure the flag field is always true for a and always false for b. ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
