Thread: Creating a foreign key on the Union of two tables
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. Thanks, -Saad
On Sun, 2003-04-06 at 17:53, Saad Saeed 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. Can't say for MySQL (since this is a PostgreSQL list!!!!!), but creating a foreign key like that to 2 different tables is against spec because one cannot create a unique constraint across 2 tables. That said, you could create a set of pl/pgsql functions that can imitate what a foreign key does and will work in the situation you describe -- if you're using PostgreSQL. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
On Sun, Apr 06, 2003 at 14:53:50 -0700, Saad Saeed <saads@umich.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.
Saad, > 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. IMHO, you cannot do this in MySQL. How about upgrading to PostgreSQL? Then we can help you. -- -Josh BerkusAglio Database SolutionsSan Francisco
Rod Taylor wrote: > > On Sun, 2003-04-06 at 17:53, Saad Saeed 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. > > Can't say for MySQL (since this is a PostgreSQL list!!!!!), but creating > a foreign key like that to 2 different tables is against spec because > one cannot create a unique constraint across 2 tables. > > That said, you could create a set of pl/pgsql functions that can imitate > what a foreign key does and will work in the situation you describe -- > if you're using PostgreSQL. Or seeting up table D plus custom triggers on A and B so that D contains the union of both. That would result in a virtual unique constraint over A and B together since the foreign key constraint now against D requires it's id column to be unique. Without more background on the purpose or the underlying business process it's hard though to tell what's best. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #