here Countryid is the primary key.
DeptID Deptname Countryid
CM CashManagement IN
CB ConsumerBanking MY
CS Customer Support IN
IB InternetBank IN
here deptid is the primary key
Uid Uname Deptid Countryid
001 Chris CM IN
002 Raja CS IN
003 Ram CB MY
here Uid is the primary key.
The problem is when i change the countryid from one country to another for a deptname.
THe change is not reflected in the usermaster table as it still shows the previous countryid.
For eg. user Chris belongs to dept Cash management which is situated in india.
Now if i change in mainDept table the cash management from india(IN) to say malaysia(MY).the corresponding change is not reflected in usermaster table.it still shows india. So when i query for chris in usermaster i get an error
as i am searching in india for cash mangement.
i tried using on update cascade but here it did not work as i have to make DeptID & countryID in MainDept table as composite key & use Deptid & countryid in usermaster as refernce key.
Since i have 20-25 tables also referencing the above 2 tables i have to set reference key in all these tables & these tables are in turn referenced elsewhere in other tables. Thus i end up creating a large no. of composite keys.
IS there any other way to solve this problem?
note : In sqlserver we can give on update cascade still it has the above problem
but in Oracle on update cascade is not possible
Can anybody suggest a solution for this in both sql server and in oracle
How abt using a trigger in maindept table, which will update all the countryids in usermaster table for the corresponding deptid. ?
But in my opinion the countryid column in usermaster table looks like a redundant data. Since u have the deptid in usermaster table u can always get the countryid by the referencing the corresponding column in maindept (i am assuming that none of ur other tables r using ur usermaster table)