Re: Creating a foreign key on the Union of two tables

On Sun, Apr 06, 2003 at 14:53:50 -0700,
Saad Saeed <saads(at)umich(dot)edu> wrote:
> I have 2 tables "A" and "B" in mySQL. They both have an attribute 'id'
> which is unique between the 2 tables. I want the union of these 2
> attributes to be a foreign key in a 3rd table "C". How do I specify
> this constraing in sql (mySQL) in table C? And for complex reasons I
> cannot merge the 2 tables A and B.

One way to do this is to have two foreign key references from C. One to
A and the other to B. To make sure just one of these is used, you can
use a constraint in C to make sure exactly one of the columns is null.
This technique doesn't need for the IDs in A and B to be unique accross
both tables.