Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004

    Unanswered: constraint problem

    Hi everybody

    we have the following tables



    Countryid CountyNAme
    IN India
    MY Malaysia
    UK UnitedKingdom
    here Countryid is the primary key.

    2) MainDept

    DeptID Deptname Countryid
    CM CashManagement IN
    CB ConsumerBanking MY
    CS Customer Support IN
    IB InternetBank IN
    here deptid is the primary key

    3) UserMaster

    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 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

    Thanks u verymuch

  2. #2
    Join Date
    Aug 2004

    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.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts