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
First of all, I think you don't need the "Countryid" field in your "UserMaster" table, as you will be able to retrieve the country of a user with its "Deptid" (by joining on MainDept.Deptid), which will make your problem easier.
Now, concerning the updates, in SQL Server use Update Cascade if it exists (you will now only have a simple FK, not a composite one) , and in Oracle you can use triggers to update the other tables in cascade.