Re: foreign key references two different table?

"Bob Jenkins" <bob_jenkins_at_burtleburtle.net> wrote in message news:a5d787df.0306061454.4c095086_at_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:-( Received on Sat Jun 07 2003 - 01:40:46 CEST