Take the 2-minute tour ×

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 know 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?

share|improve this question
In the database world, you rarely ever care about CPU. It is all about memory and disk IO so double checking FK existence might result in double disk read (depending on server load and whether page gets swapped out before insert occurs) –  billinkc Aug 15 '11 at 17:59
add comment

3 Answers

up vote 2 down vote accepted

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)

This scales very well to high volumes.

share|improve this answer
The other posted answers are similar to this one but gbn provided additional links. –  Jason Aug 17 '11 at 12:29
add comment

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...

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer


