Re: foreign key references two different table?

susana73_at_hotmail.com (Susan Lam) wrote in message news:<7186ed56.0305301413.50ce90f7_at_posting.google.com>...
> I have 3 tables.
>
> parent1 (id, ...)
> parent2 (id, ...)
> child (id, memberid, type)
>
> child.memberid came from parent1.id and parent2.id. child.type
> distinguishs where the memberid are from parent1 or parent2 table.
>
> I would like to have a fk relationship between child.memberid ->
> parent1.id and parent2.id. However, I can't create two separate fks
> because parent1.id and parent2.id together are not unique.
>
> Is there a way to create one fk to two different tables?
>
> Thanks.
> Susan

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. Received on Fri Jun 06 2003 - 17:54:45 CDT