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

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

    Hello,

    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 on the Oracle forum since we use Oracle and we wondered whether there is also an Oracle specific solution or not.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, it's called a CHECK constraint
    Code:
    CREATE TABLE personel
    ( id ...
    , fk_depa ...
    , fk_depb ...
    , CHECK ( fk_depa IS NULL AND fk_depb IS NOT NULL
           OR fk_depa IS NOT NULL AND fk_depb IS NULL )
    );
    no idea if oracle supports this, and by the way, please don't post the same question in more than one forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Grrr - damned forum blew up on me.

    Without more information I couldn't say for sure but my gut is I don't like the design.
    However, this is easy to do:
    Implementing Table Interfaces - SQLTeam.com
    The above uses SQL Server syntax but it will be possible to add the same in Oracle using similar (possibly identical) syntax.

    Thanks for letting us know re the other thread. I am going to lock it and link to here - we don't allow cross posting. If we fail you here then you can repost in the Oracle forum once this thread is written off.

  4. #4
    Join Date
    Oct 2002
    Posts
    37
    thanks for the quick reply.

    ok, I won't cross post if it's forbidden. no prob.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Don't worry about it. I suppose forbidden sounds a bit strong. It is simply that sometimes you get people putting in effort on questions that have already been covered off in another thread. AS such, we like to only have one live thread at a time. You did the right thing by mentioning the two threads.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    >PERSONEL is in this case the child table of DEPA AND DEPB.
    Design flaw!
    Only single DEPARTMENT table should exist with column for DEPT_NAME; which contains DEPA, DEPB, etc.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by anacedent View Post
    Design flaw!
    Only single DEPARTMENT table should exist with column for DEPT_NAME; which contains DEPA, DEPB, etc.
    It would seem so but the problem appears to have been abstracted and simplified so much that we cannot say this definitively.

  8. #8
    Join Date
    Oct 2002
    Posts
    37
    Quote Originally Posted by pootle flump View Post
    It would seem so but the problem appears to have been abstracted and simplified so much that we cannot say this definitively.
    Exactly.

    The other way it would be too complicated to explain and my English knowledge isn't that good.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sure.
    I only differ from Anacedent though in that I am not prepared to say it is definitely a design flaw, only that it looks like one! If you are confident it is the correct design and are confident in your modelling skills then it is your database to do with as you like

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    The logic becomes untenable when tens or hundreds of different departments exist!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Assuming he has a table per department then yes. But again you are inferring a lot from an abstracted problem.

  12. #12
    Join Date
    Dec 2003
    Posts
    1,074
    If you cannot get the constraint to work, then you can implement the same logic, with a tailored error message in a table trigger

    Code:
    create or replace trigger personnel_bt
    before insert or update on personnel
    for each row
    begin
      if (:NEW.FK_DEPA is null and :NEW.FK_DEPB is not null)
         or
         (:NEW.FK_DEPA is not null and :NEW.FK_DEPB is null) then
          raise_application_error(-20500, 'Each employee can only belong to one department.');
      end if;
    end personnel_bt;
    --=cf

  13. #13
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by msegmx View Post
    is there a way of declaring such a constraint/relationship in an RDBMS (such as Oracle) ?
    Create a supertype table representing both DEPA and DEPB. Then reference that supertype table with a single foreign from PERSONEL. Make the foreign key non-nullable.

Posting Permissions

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