Re: 1 foreign key to 2 different tables?

On Sat, May 01, 2004 at 14:51:14 -0400,
Ryan Riehle <rkr(at)buildways(dot)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.