Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2010
    Posts
    2

    Unanswered: Conditional foreign key

    Hi, first post. We have an application where the user can define their own rules for a work flow system, but in this same system, we reserve it's use for a specific purpose. Our reserved use of this system ALWAYS points to an Item Master table and verifies that item is in the DB to process the request. However, other user definitions may have nothing to do with items, in fact, they may point to any other DB column, it may be a general purpose work flow doc, not requiring any validation.

    Is there a way to enforce a foreign key ONLY if a value in another columns contains a certain value?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by KenHayes View Post
    Is there a way to enforce a foreign key ONLY if a value in another columns contains a certain value?
    No, that's not possible

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    At the risk of being proven wrong, I am unaware a way to accomplish what you desire.
    An alternative is to enforce the "Foreign Key" relationship in application,
    but in my experience that ALWAYS eventually results in orphan child records.
    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.

  4. #4
    Join Date
    Aug 2010
    Posts
    2
    Can someone tell me how you might accomplish it? One example of how our systems works is an Invoice Line Item. For shipped goods, the Invoice Lint Item points to an Item ID (as a foreign key). However, the user might also want to add a miscellanious charge for engineering services, which does NOT point to an Item ID - it is simply a description line. A third line might be sales tax, again, NOT an Item ID. Yet, all three of these entities reside in the same table for Invoice Line Items. We do it now with a lot of code in several areas to figure out if this is an Item or Not, if it is, look up the item, if not, don't look up the item.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can someone tell me how you might accomplish it?
    IT?
    How would independent observer conclude any posted response is valid solution to your problem?

    Understand, I am really not sure what the problem is.

    It almost sounds like the following:
    Items need to have a parent record.
    Non-items do not need to have a parent record.
    If above is true, then perhaps they should NOT be stored in the same table.

    Ever hear of Third Normal Form?
    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.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by KenHayes View Post
    Can someone tell me how you might accomplish it? One example of how our systems works is an Invoice Line Item. For shipped goods, the Invoice Lint Item points to an Item ID (as a foreign key). However, the user might also want to add a miscellanious charge for engineering services, which does NOT point to an Item ID - it is simply a description line. A third line might be sales tax, again, NOT an Item ID. Yet, all three of these entities reside in the same table for Invoice Line Items. We do it now with a lot of code in several areas to figure out if this is an Item or Not, if it is, look up the item, if not, don't look up the item.
    That sounds like a pretty horrible data model.

    The only way I can think to enforce the FK constraint and still be flexible enough, is to create a dummy ItemID to which the description line and the sales tax can link to.

    Pretty ugly, but could work (probably better than trying to enforce this on various places in the application)

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You could enforce the relationship with triggers, but that would get very ugly very quickly. I would suggest that you create a dummy object in the master item table for the non-item rows in the child table to point at.

    I agree with shammat. The data model appears suspect.

  8. #8
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Cool

    Here's the solution. I use my own two tables, you can substitute your own names for tables. Here goes:

    The following script sets up the two tables. The important thing to notice here is that my FKID column on MAINTBL is nullable (i.e. I am able to set the value null)
    Code:
    dayneo@RMSD> CREATE TABLE RELATEDTBL
      2  (
      3    ID  NUMBER,
      4    PRIMARY KEY(ID)
      5  )
      6  /
    
    Table created.
    
    dayneo@RMSD> CREATE TABLE MAINTBL
      2  (
      3    ID	    NUMBER			     NOT NULL,
      4    FKID	    NUMBER,
      5    DESCRIPTION  VARCHAR2(512 BYTE),
      6    PRIMARY KEY(ID),
      7    CONSTRAINT MAINTBL_R01
      8   FOREIGN KEY (FKID)
      9   REFERENCES RELATEDTBL (ID)
     10  )
     11  /
    
    Table created.
    
    dayneo@RMSD> INSERT INTO RELATEDTBL VALUES(1);
    
    1 row created.
    
    dayneo@RMSD> INSERT INTO MAINTBL VALUES(1, 1, 'RELATED ITEM');
    
    1 row created.
    
    dayneo@RMSD> INSERT INTO MAINTBL VALUES(2, 1, 'ANOTHER RELATED ITEM');
    
    1 row created.
    
    dayneo@RMSD> select * from relatedtbl;
    
            ID
    ----------
             1
    
    dayneo@RMSD> select * from maintbl;
    
            ID       FKID
    ---------- ----------
    DESCRIPTION
    --------------------------------------------------------------------------------
             1          1
    RELATED ITEM
    
             2          1
    ANOTHER RELATED ITEM
    So far you can see that I can insert related items without error.
    Next I show that I can also enter unrelated items:
    Code:
    dayneo@RMSD> INSERT INTO MAINTBL VALUES(3, NULL, 'NOT RELATED TO ANYTHING');
    
    1 row created.
    
    dayneo@RMSD> select * from maintbl;
    
            ID       FKID
    ---------- ----------
    DESCRIPTION
    --------------------------------------------------------------------------------
             1          1
    RELATED ITEM
    
             2          1
    ANOTHER RELATED ITEM
    
             3
    NOT RELATED TO ANYTHING
    Notice my use of NULL in the foreign key id column.
    Now I will demonstrate that the foreign key constraint rules still apply. I.e. If I try to relate a MAINTBL record to RELATEDTBL, then the FKID must match an ID in the RELATEDTBL. Also, if I try to delete a RELATEDTBL row that has child MAINTBL rows, I receive an integrity constraint error.
    Code:
    dayneo@RMSD> INSERT INTO MAINTBL VALUES(4, 2, 'PROOF THAT FK WORKS');
    INSERT INTO MAINTBL VALUES(4, 2, 'PROOF THAT FK WORKS')
    *
    ERROR at line 1:
    ORA-02291: integrity constraint (DAYNEO.MAINTBL_R01) violated - parent key not
    found
    
    
    dayneo@RMSD> DELETE FROM RELATEDTBL;
    DELETE FROM RELATEDTBL
    *
    ERROR at line 1:
    ORA-02292: integrity constraint (DAYNEO.MAINTBL_R01) violated - child record
    found
    Any questions?
    Last edited by dayneo; 09-07-10 at 13:38.

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
  •