| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
08-27-10, 09:34
|
|
Registered User
|
|
Join Date: Aug 2010
Posts: 2
|
|
|
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?
|
|
08-27-10, 09:42
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,407
|
|
Quote:
Originally Posted by KenHayes
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
|
|
08-27-10, 09:44
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,407
|
|
|
|
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.
|
|
08-27-10, 10:00
|
|
Registered User
|
|
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.
|
|
08-27-10, 10:06
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,407
|
|
>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.
|
|
08-27-10, 10:08
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,407
|
|
Quote:
Originally Posted by KenHayes
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)
|
|
08-27-10, 10:08
|
|
Wage drone 24601
|
|
Join Date: Jan 2003
Location: Massachusetts
Posts: 4,899
|
|
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.
|
|
09-07-10, 12:34
|
|
Registered User
|
|
Join Date: Oct 2002
Location: Cape Town, South Africa
Posts: 161
|
|
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 12:38.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Conditional foreign key
