Home » SQL & PL/SQL » SQL & PL/SQL » is it possible to create conditional foreign key reference (merged)
is it possible to create conditional foreign key reference (merged) [message #386540] Mon, 16 February 2009 01:33 Go to next message
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



Re: is it possible to create conditional foreign key reference (merged) [message #386549 is a reply to message #386540] Mon, 16 February 2009 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
400+ post and you don't know that code MUST be post between code tags?

Foreign key is on a whole (unique/primary) key not part of it, so add clfn to the child table.

Regards
Michel

[Updated on: Mon, 16 February 2009 02:30]

Report message to a moderator

Re: is it possible to create conditional foreign key reference (merged) [message #386555 is a reply to message #386549] Mon, 16 February 2009 02:29 Go to previous message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
Embarassed , thank you sir and Sad

yours
dr.s.raghunathan
Previous Topic: Just a bit of help
Next Topic: 'PLS-00363: expression..' error when executing a stored procedure with in out parameters
Goto Forum:
  


Current Time: Fri Feb 07 15:30:28 CST 2025