Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Posts
    36

    Unanswered: doubt constraint violation

    Hi i have a doubt.
    i have a table dept where deptno is primary key
    and emp deptno referencing to it..
    what is my doubt is

    for ex in dept i have deptno =10,20,30

    and in emp i have deptno=10,20,30

    now what i want is with out disabling the child table cnstraint or removing
    the record in the child table i want to update dept deptno from 10 to 40
    i know it is not possible but any thing else there??

    Rgds
    Bujji

  2. #2
    Join Date
    May 2003
    Location
    Dublin, Ireland
    Posts
    44

    Re: doubt constraint violation

    insert into dept (deptno,...) select 40,..... from dept where deptno=10;
    update emp set deptno=40 where deptno=10;
    delete from dept where deptno=10;

    Cheers,

    Jacek

    Originally posted by vadlamanibujji
    Hi i have a doubt.
    i have a table dept where deptno is primary key
    and emp deptno referencing to it..
    what is my doubt is

    for ex in dept i have deptno =10,20,30

    and in emp i have deptno=10,20,30

    now what i want is with out disabling the child table cnstraint or removing
    the record in the child table i want to update dept deptno from 10 to 40
    i know it is not possible but any thing else there??

    Rgds
    Bujji

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    you want a cascade update trigger.

    create a trigger with before update clause that updates the emp table.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    The update trigger will work, or you can defer the constraints until commit time. This way many relationships can be modified while the updates are made and will be revalidated/enforced at the commit point.

    Hth
    Bill

  5. #5
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Solution

    Hi

    If this is just one time operation that you have to do, then instead of going into triggers and rest, follow the following steps.

    1. Insert a deptno 40 in the dept name.
    2. Now update the emp table for all employees belonging to dept 10, and set it to 40.
    3. Delete 10 from dept table.

    Solution is neat and clean and faster ... only if this is a one time operation, else triggers are necessary to avoid all the steps.

    Thanx and Regards
    Aruneesh

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    I think "sjacek" already suggested that...

Posting Permissions

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