Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002

    Question Unanswered: Two foreign keys, one must have a value the other not


    It's maybe a theoretical question, but since we're using Oracle I wanted to post here.

    We have a table PERSONEL and 2 parent tables, say DepA and DepB.
    now PERSONEL has 2 foreign key columns, one for each parent table.

    ID NUMBER(9)
    NAME ...
    SURNAME ...

    ID NUMBER(9)
    COLA1 ......
    COLA2 ......

    ID NUMBER(9)
    COLB1 .......
    COLB2 .......

    PERSONEL is in this case the child table of DEPA AND DEPB.
    but it can only be the child table of one parent table at a time.
    so either FK_DEPA or FK_DEPB must have a value but neither can these 2 columns be null nor can they have a value at the same time.

    we have to declare both FK fields as nullable, but that's not entirely true, because one of these columns must always have a value.

    is there a way of declaring such a constraint/relationship in an RDBMS (such as Oracle) ?

    or is there a better solution to this problem (like using only one FK field, if possible) ?

    Note : I also posted this question on the Concepts & Design forum.
    Last edited by msegmx; 04-09-10 at 06:49.

  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    Thread closed. please respond here if you have a suggestion:

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts