is it possible to create conditional foreign key reference (merged) [message #386540] |
Mon, 16 February 2009 01:33  |
dr.s.raghunathan
Messages: 540 Registered: February 2008
|
Senior Member |
|
|
hi,
Is it possible to create conditional foreign key reference. For example Ledger_mst I have three columns as clfn01, clfn02, clfn03 must refer clfn_mst(clfn_desc) with a condition that clfn01 must have clfn_Desc contents provided clfn_mst(clfn) must have the value of '01', viceversa clfn02 must contain the value stored in clfn_mst(clfn_desc) provided clfn_mst(clfn) must have the value of '02';
Create table clfn_mst ( clfn char(2), clfn_desc varchar2(100));
Insert into clfn_mst values ('01','balance sheet');
Insert into clfn_mst values ('01','Profit and Loss Account');
Insert into clfn_mst values ('02','Liability');
Insert into clfn_mst values ('02','Asset');
Insert into clfn_mst values ('02','Income / Receipts');
Insert into clfn_mst values ('02','Expenditure / Payments');
Insert into clfn_mst values ('03','debit');
Insert into clfn_mst values ('03','credit');
Create Table ledger_mst (ledger_name varchar2(100), clfn01 varchar2(100),
Clfn02 varchar2(100), clfn03 varchar2(100));
Insert into ledger_mst values ('Cash and Bank Balances', 'balance sheet', 'asset', 'debit')
This insert in successful whereas
if I do the following insert, it should throw the error
Insert into ledger_mst values ('Cash and Bank Balances', 'asset', 'balance sheet', 'debit')
For the reason being clfn01 must refer the clfn_desc of clfn_mst and must have value '01 against clfn of clfn_mst.
we can solve through form controls, or check constraint or through any other means. I am interested to know is there a way of conditional foreign key reference checking
yours
dr.s.raghunathan
|
|
|
|
|