cycles or multiple cascade error
-
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
-
where is your query or error-generation command ?
select ...
from employee E
inner join Rooms R
on E.EmpRoomNum = R.RoomNum
inner join Device D
on D.DeVRoomNum = R.RoomNum
and D.DevSignByEmp = E.EmpID
would be my guess
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
DEV Employees
------------ ------------
| DevID | ----------------------------------------| EmpID |
|------------ / ------------
| EmpID |/ -| RoomID |
|------------ / ------------
| RoomID | / | |
|------------ \ / | |
| | \ / | |
| | \ / | |
| | \ / | |
------------ \ / ------------
\ /
\ ROOMS /
\ ------------ /
\--------| RoomID |
|------------
| |
| |
| |
| |
| |
| |
| |
------------
Do you see THE CYCLE now ?
* Noel -
trivial, but overlooked it 
Nice job.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I think there is a problem with you relational model. Could you please add the cardinality?
-
The model was just to show graphically the cycle to the poster it was not meant to be a full blown ER diagram. if you look at is is all written in txt which is tough for a person like me with not a very good drawing background
* Noel -
I understand. Do you have one to many relationships among all the tables? Is Device an independent entity that has one room and Employee and independent entity that have a different room? Or Is Device an associative table? I think the root of your problem is inside the relational model and is not exactly a DB issue.
-
What are you talking about?
I don't have a clue on what the model is supposed to be because is not mine!!!
The root of the problem that the original poster has is shown perfectly well with the simplified diagram. The Poster was trying to set up cascade referential integrity on a cycle which is not allowed for obvious reasons.
Please, read the whole thread
* Noel -
I'm afraid you are both mistaken?
look at the diagram :
A. if I'll change RoomNum in Room table it will update OfficeRoomNum in Employee table that is not connected to any table and LocateInRoomNum in Device table which is also not connected to any table - no cycle!!
B. if I'll change EmpID in Employee table it will update SighnByEmpId in Device table wich is not connected to any other table - no cycle!!
C. if I'll change DevId in Device table it will update no table because is not connected to any table - no cycle!!
where is the cycles or multiple cascade error ??
-
Think about it this way (replace 'delete' for 'update' if necessary).
If you delete a Room you will have to delete an Employee and from there you delete the device OR you delete the device from the FK that points From the Rooms to the Device.
FROM BOL:
...the tree of cascading actions must not have more than one path to any given table.
Therefore YOU are mistaken because there is a cycle.
HTH
* Noel -
In my case I do'nt cascade on delete only on update so where is the problem?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply