Post reply

cycles or multiple cascade error

  • February 8, 2005 at 6:44 am

    #66493

     

    Hi

    I'm tryng to connect this 3 simple tables:

    1. employee (EmpID (PK),EmpRoomNum(FK Rooms),...)

    2. Rooms(RoomNum(PK), ...)

    3. Device (DevID(PK),DeVRoomNum(FK Rooms), DevSignByEmp(FK employee),.)

    I See No reason why cant I connect these tables ?

    Where is the cycles  or multiple cascade paths ???

    Thanks

  • February 8, 2005 at 8:55 am

    #541146

    I dont there should be any problem for you getting the resultset .

    Sample query ....

    select * from emp e (nolock)

    inner join room b (nolock) on

    e.emproomno=b.roomno inner join

    device d on

    e.empid=devempid

    and emproomno=devroom

    Kindest Regards,

    Sureshkumar Ramakrishnan

  • June 24, 2005 at 10:54 pm

    #568447

    I'm really late responding to this note.  I'm just now having the same problem, so I was looking for an explanation of why SQL did it this way.  Sorry if you've already found the answer elsewhere.

    I can answer your question, I can't answer why it is true, or the best way to fix this.  (Yet.)  Your multiple cascade path is in Device.  (I believe cycles refers to self referencing tables.)  If you delete Rooms, Device could be deleted because it references Rooms directly, or it could be deleted because of employee being deleted.

    I think you should be able to connect these tables.  But, then I don't spend all my time thinking of ways that this could go terribly wrong.

    In order to get the behavour you want, you would set the ON DELETE NO ACTION for the employee/Device foreign key.  Add an Instead of delete trigger to Rooms

    Delete all the Device rows that won't get deleted by the employee, then delete the Rooms record.  (Maybe you can delete all the rows, but I don't know if the cascading would blow up on the employee portion if you did so.)

    DELETE FROM Device WHERE DeVRoomNum IN (SELECT RoomNum FROM DELETED)

    AND DevSignByEmp NOT IN (SELECT EmpID FROM employee

     WHERE EmpRoomNum IN (SELECT RoomNum FROM DELETED))

    It's even worse for self referencing tables, you have to find out (if children exist, turn on RECURSIVE_TRIGGERS, delete the children, turn off RECURSIVE_TRIGGERS), delete the rows

  • June 24, 2005 at 10:56 pm

    #568448

    Sorry "...ON DELETE NO ACTION for the employee/Device..." should read "...ON DELETE NO ACTION for the Rooms/Device..."

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply