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

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

    Hello,

    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.

    PERSONEL
    -----------
    ID NUMBER(9)
    FK_DEPA NUMBER(9)
    FK_DEPB NUMBER(9)
    NAME ...
    SURNAME ...
    ....

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

    DEPB
    ------
    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 07:49.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thread closed. please respond here if you have a suggestion:
    http://www.dbforums.com/database-con...ml#post6455836

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
  •