Quick question on SQL best practise,... should you validate data in a stored procedure before calling an insert or update and throw exceptions when needed or, should you wrap your insert or update in a try-catch block, then check the exception that is caught (eg. foreign key constraint) then throw a new user friendly exception?
In the validate first example, you would still rely on the foreign key constraints, they would still exist but they would be a fall back. You would try to make sure everything is correct before the insert or update and throw friendly messages.
The in second example you assume all data is correct, perform the action inside the try catch block and then, if an exception did occur try to essentially override it with a user-friendly version of the exception.
If you are looking at an OLTP system, where you are inserting single rows, and need each transaction to run very fast, it may be best to go with the catching the foreign key error, instead of incurring the lookup (twice, in fact) for each insert. Less locking, quicker insert, happier end users.
If you have an OLAP system, where you are inserting large batches, and are worried about the rollback time, it would probably be best to do the validation check beforehand. It takes a little longer, but you save on time rolling back the transaction, and re-doing the insert. Testing would need to be done to see if the time spent on the validation check is worth it, of course.
I have always preferred letting database handle the constraints. I guarantee it will do RI checks and check constraints faster than you will within your code. Very easy to code for the specific RI/Check violation. I have worked on systems, where they had no RI defined for "speed", but they invariably had issues with bad/missing data(childless/orphan data), same with some data that had constraints where, all of a sudden someone would notice that this Y/N field had spaces or an F.