Re: 1 foreign key to 2 different tables?

For what I am reading now it looks like this is an opportunity to use CREATE
ASSERTION, but this functionality appears to be unstable so far and is not
recommended for production environments.  Is this correct?   Otherwise, can
someone post an example of implementing a check constraint or trigger since
I have not created a check onstraint that is above common complexity and and
have never tried a trigger.

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 Ryan Riehle
Sent: Saturday, May 01, 2004 4:57 PM
To: 'Bruno Wolff III'
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 1 foreign key to 2 different tables?


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: [GENERAL] 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