Oracle Challenge Solution

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s