Re: Pros and Cons of DB defined Referential Integrity Constraints

On 19 Oct 2012 15:31, Ken Sims wrote:

On Fri, 19 Oct 2012 00:18:45 -0500, CRPence wrote:

That is, consider this scenario: Is the key I am about to insert
already there? Lookup the key value. Not found, so let's insert.
Argh! Duplicate key error on the write!

Thus since the code likely needs to handle the duplicate key error
irrespective of this advance lookup having been coded to ensure the
key value does not exist, then how much value was there in checking
first in the application? Is it purely overhead? Perhaps just
perform the insert and handle the exception only if\when it
occurs.?

I prefer to check first, and then also handle it on write, so as to
avoid error messages in the job log except in the rare circumstance
where some other job adds the record in between this job's check and
write.

Understood. Especially when a "known" for the application is that the exception circumstance should be or is indeed, "rare".

Merely FWiW:

However when using an interface that is message-based for exception conditions [instead of return-code-based], _I would_ tend *not* to write _both_ of those code paths; i.e. one path pre-operation hoping to avoid the exception *and* another path post-operation that handles the message if\when the exception occurs. I would just code the one path to handle the message if\when the exception occurs. Primarily, because including more code paths often seems to increase the amount of possible defects; even if only introduced as regression from some maintenance. Secondly, because I prefer not to duplicate some data checking if easily avoided. I am somewhat more open to redundant checks when the data checks are dynamic, typically table-driven, or even just generated dynamically into the objects only at compile\create-time. Admittedly there can be great benefits having a separate exception path to learn when or how often the presumed main-flow implementation occurs as compared to the exception path. For example, if the exception path becomes pervasive [i.e. the norm vs the rare exception], then all the more reason to drop the up-front checks, and perhaps also add the code to remove the messages [though I would be removing the messages normally anyway, even as a rarely occurring exception path... unless other logging ensures those exceptions do not cause confusion when not between other messages giving some context].