Re: Pros and Cons of DB defined Referential Integrity Constraints

On 18 Oct 2012 15:05, Nathan Andelin wrote:

<<SNIP>>

What about other pros and cons?

The data integrity provided is very helpful. Cascaded deletes are too. But a more obscure value is that a query can use the established RI rules to fully eliminate some inquiries which might otherwise be implemented and performed, costing real time, CPU, and memory when the database had been left uninformed and thus unaware that a constraint known only to the application would have precluded the relationship; i.e. performance gains.

The only con I consider of significance is the lack of support for QTEMP. Testing using QTEMP is suddenly problematic. This is a nuance of the relationship of constraints with the *DBXREF, which like file long names, had always been [and I presume still] maintained [for uniqueness] by the database rather than the context\library index support. That also means they are dependent upon the System Cross Reference Database for actions against them, including create\restore. While the SQL has some apparent job-specific long-name support which can [with unknown limitations] enable access for at least TABLE\VIEW\ROUTINE objects in the library QTEMP of a job even though the *DBXREF provides no tracking, AFaIK no such support had been extended for the constraints; i.e. I believe a -7008 or sql7008 rc10 per CPD32B0 rc15 will still diagnose use of QTEMP as a restriction.