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

    Smile How abt a trigger

    hai saj,

    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)

    with regards

Posting Permissions

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