Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!newsfeed1.earthlink.net!elnk-nf2-pas!newsfeed.earthlink.net!newshub.sdsu.edu!headwall.stanford.edu!newsfeed.stanford.edu!newsfeeder2.randori.com!news.sanjose1.Level3.net!Level3.net!news.oracle.com!not-for-mail From: "Mikito Harakiri" Newsgroups: comp.databases.oracle.server,comp.databases.oracle.tools References: <7186ed56.0305301413.50ce90f7@posting.google.com> Subject: Re: foreign key references two different table? Lines: 26 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Message-ID: Date: Fri, 6 Jun 2003 16:40:46 -0700 NNTP-Posting-Host: 130.35.178.126 X-Trace: news.oracle.com 1054943276 130.35.178.126 (Fri, 06 Jun 2003 16:47:56 PDT) NNTP-Posting-Date: Fri, 06 Jun 2003 16:47:56 PDT Xref: core-easynews comp.databases.oracle.server:188941 comp.databases.oracle.tools:59394 X-Received-Date: Fri, 06 Jun 2003 16:45:59 MST (news.easynews.com) "Bob Jenkins" wrote in message news:a5d787df.0306061454.4c095086@posting.google.com... > No, foreign keys referencing multiple primary keys isn't supported. > > What you could do is > parent1 (id, ...) > parent2 (id, ...) > child (id, memberid1, memberid2, type) > with memberid1 a foreign key to parent1 and memberid2 a foreign key to > parent2. If type=1, memberid1 is filled in and memberid2 is null, and > if type=2, vice versa. > > A view on top of child could combine them into one memberid column > select id, decode(type, 1, memberid1, 2, memberid2) memberid, type > ... > > You'd want indexes on memberid1 and memberid2 to aid foreign key > performance and concurrency. Oracle doesn't check for a matching > primary key when foreign keys containing nulls. Oracle doesn't store > all-null keys in indexes, so you won't spend time or space indexing > the nulls in the irrelevant columns. This request usually comes up with more than 2 parent tables, and, moreover, the number is potentially growing whenever new type of parent is added:-(