I'm encountering some exceptions in my web application.
The setup
To simplify, consider 2 SQL tables. Both have an ID column The first table has a foreign key that references the second table. There is a foreign key constraint such that the foreign key must be valid.
The situation
- Data is being inserted into the first table.
- The row (being referenced) in the second table is deleted just before the insert.
- The insert fails.
This is as you should expect. The insert should fail because the foreign key is now invalid.
The problem
I don't want to have any exceptions in my application.
The question
What is the best way to handle this? I consider a couple of options but both are unsatisfying.
Options
- Locking - I could lock the row referenced by the foreign key while inserting. BUT, I dislike locks in databases. They're a pain to manage and update. Every new table or new column must consider these locks. I could end up with a lot of locks which could slow things down, could result in deadlocks if I'm not very careful. Locks could introduce other unforeseen problems.
- Exception handling - Perhaps better than locks. Must also be maintained for each similar case. Must be careful to only catch this kind of exception and not others. It does not stop the problem from occurring but it does handle it.