X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 33579476022 for ; Mon, 7 Apr 2003 12:50:26 -0400 (EDT) Received: from wolff.to (wolff.to [66.93.249.74]) by postgresql.org (Postfix) with SMTP id 618F0475FFF for ; Mon, 7 Apr 2003 12:50:25 -0400 (EDT) Received: (qmail 17838 invoked by uid 500); 7 Apr 2003 16:50:30 -0000 Date: Mon, 7 Apr 2003 11:50:30 -0500 From: Bruno Wolff III To: Saad Saeed Cc: pgsql-sql@postgresql.org Subject: Re: Creating a foreign key on the Union of two tables Message-ID: <20030407165030.GA17741@wolff.to> Mail-Followup-To: Saad Saeed , pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.4i X-Spam-Status: No, hits=-35.3 required=5.0 tests=BAYES_20,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_MUTT autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/101 X-Sequence-Number: 12802 On Sun, Apr 06, 2003 at 14:53:50 -0700, 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. 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.