If you haven’t read the challenge, don’t read the solution yet. Click here and try to solve it before you read the answer.
If you tried (and have or don’t have a solution), this is the explanation of the situation I described.
I described the situation exactly as my customer presented it to me. It took me some time to make sure I didn’t miss anything, since it doesn’t make any sense. I even thought that there might be a bug, or Oracle shows the wrong FK, and I guess some of you thought like that as well.
After believing that this is indeed the case I thought how to debug this. The tool I used was SQL trace. It is important to know that SQL trace contains ALL queries, including recursive SQL (which are internal and run as a side effect of our queries or operations).
In the trace, I looked for the error and this was the failing query:
delete from A where parent_id=:b1
But wait! What is PARENT_ID and why am I deleting rows according to it? Then I took another look and understood everything:
Table A has a foreign key to itself, PARENT_ID is referencing the ID in table A with “on delete cascade”. The row I tried to delete from table A (name=’x’) had children in table A. Because of the “on delete cascade” Oracle tried to delete those children automatically, and one of these child records had a child in table B. Because the FK from B to A wasn’t configured with “on delete cascade” or “on delete set null”, this caused the violation.
To some it up, Oracle did give the error on the correct FK, but what I didn’t know is that the problematic row in A was not the record I tried to delete (it was its child record)…
Hope you enjoyed, it was quite challenging for me trying to understand what the hell is happening while sitting with the customer at the same computer .
Liron