MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » October 2012

Re: Pros and Cons of DB defined Referential Integrity Constraints



fixed

On 18 Oct 2012 16:02, Nathan Andelin wrote:
As I think about this, wouldn't you want to implement constraints
using program logic whether constraints are DB defined or not, in
order to validate data prior to "insert" or "update" <<SNIP>>

Perhaps, but...

Concurrency can easily vitiate the intention of such pre-check logic. Coding in a proactive manner can easily fail identically to how having coded in a reactive manner would not fail, but only due to its monitor and handling that error. Often there can be little value to the overhead of duplicated data checking; for both maintenance and performance.

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

Obviously those scenarios where there can be no concurrency for the validated I\O, the application need not worry that its check is not going to be consummate with regard to concurrent operations. However because effectively the same work is going to be performed again by the database, questioning how much value there is in checking in the application first, still seems valid. I suppose for completeness or hope to avoid missing something, someone might want to ensure all of the checks are performed in both places. However having to 'code' corrections\additions\removals to the business rules once in the application and again in the database, might be somewhat burdensome. Of course this same dilemma for repeated and redundant data checking comes up for the user interface all the way down to the database.

Hmmm... that reminds me, that others have complained about the requirements for exclusive allocations to change business rules implemented in the database. So I suppose that is another possible con. However an application could be designed\coded to allow for enabling such changes to go into production with a small impact; even if not as small and simple as moving a new program into production.






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact