SQL FK and Pre-Checking for Existence

I was wondering what everyone's opinion was with regard to pre-checking foreign key look ups before INSERTS and UPDATES versus letting the database handle it. As you kn开发者_运维问答ow the server will throw an exception if the corresponding row does not exist.

Within .NET we always try to avoid Exception coding in the sense of not using raised exceptions to drive code flow. This means we attempt to detect potential errors before the run-time does.

With SQL I see two opposite points

1) Whether you check or not the database always will. This means that you could be wasting (how much is subjective) CPU cycles doing the same check twice. This makes one lean towards letting the database do it only.

2) Pre-checking allows the developer to raise more informative exceptions back to the calling application. Instead of receiving the generic "foreign key violation" one could return different error codes for each check that needs to be done.

What are your thoughts?

---

Don't test before:

• the DB engine will check anyway on INSERT (you have 2 reads of the index, not one)
• it won't scale without lock hints or semaphores which reduce concurrency and performance (an 2nd overlapping concurrent call can pass the EXISTS before the first call does an INSERT)

What you can do is to wrap the INSERT in it's own TRY/CATCH and ignore error xxxx (foreign key violation, sorry don't know it). I've mentioned this before (for unique keys, error 2627)

• Only inserting a row if it's not already there
• Select / Insert version of an Upsert: is there a design pattern for high concurrency?
• SQL Server 2008: INSERT if not exits, maintain unique column

This scales very well to high volumes.

---

Data integrity maintanence is the Databases's job, so I would say you let the DB handle it. Raised exceptions in this case is a valid case, and even though it could be avoided, it is a correctly raised exception, because it means something in the code didn't work right, that it is sending an orphaned record for insert (or something failed in the first insert - however way you are inserting it). Besides, you should have try/catch anyway, so you can implement a meaningful way to handle this...

---

I don't see the benefit of pre-checking for FK violations.

If you want more informative error statements, you can simply wrap your insert in a try-catch block and return custom error messages at that point. That way you're only running the extra queries on failure rather than every time.