Re: Pros and Cons of DB defined Referential Integrity Constraints

On 18 Oct 2012 16:18, Nathan Andelin wrote:

The only con I consider of significance is the lack of support for
QTEMP.

That comes across as humorous, because objects in QTEMP go away,
anyway ;-)

Well the issue for constraints in QTEMP is effectively, that the foreign key for the QADBFCST data tracking constraints is the (DBXLIB,DBXFIL) of the QADBXREF\database data tracking physical files. Since there is no tracking of QTEMP [at least in part] because there is no uniqueness requirement for names across QTEMP of different jobs, that integrity definition is not possible; i.e. such constraints can not be tracked because a physical file in QTEMP is not tracked. Albeit tracking to the internal QTEMP library name or its address could be possible, to enable that support. Anyhow, until they are tracked, a constraint can not be added. Trying to create, restore, or duplicate the object with constraints will fail to add the constraint even if the object and data can be produced by the request.

It is possible that with the longer schema name support, something has changed to enable constraints in QTEMP.?

What about occasional needs to delete or clear tables that have DB
defined dependencies, including constraints?

Was never a problem for me, but some care is required for order of operations, or the [value specified on the] RMVCST parameter on DLTF, though I normally stick with the SQL DROP TABLE or sometimes ALTER TABLE DROP CONSTRAINT. Some [effective; i.e. perhaps embedded] scripts run to CREATE and ADD [and GRANT, GRTOBJAUT, RVKOBJAUT] to replace them after DROP. Some specific recovery actions might CHGPFCST to disable, run some actions that if only temporarily might not meet RI restrictions, then repopulate\correct data, then re-enable RI.

What about managing DB defined constraints? We are operating under a
SAAS model where we set up new environments for new customers, and we
look forward to migrating a number of organizations off MS SQL Server to
IBM i in coming years. We could have potentially thousands of DB defined
RI constraints, in addition to primary key constraints. Each customer
has their own DB library. But they share a common code base. What might
we use to copy constraints from one DB library to another?

My rule was *always* "CREATE" objects, never "copy" objects, only copy data. Sometimes restore, for effectively identical "copies" [with data], but generally only to another system for a mirror-copy and thus also applying journaled changes; probably OK for organizational copies using QDFTJRN support to establish a new\separate journal. For existing TABLE objects, ALTER TABLE ADD CONSTRAINT. Typically only install\upgrade and\or run-time to effect changes to existing objects, or creating new and copying old data and authorities, taking care to ensure that code assumptions for any run-time object changes are verified to exist.