Oppolzer - Informatik / Blog

Very good statement and explanation.

At our site, we have large application systems which use RI
and others, which don't. This is a matter of history, that is:
it depends a little on the time when the application was built etc. -
we started with large DB2 applications in the early 1990s.

My experience is: if the application code is of high quality, you
don't have much problems, if you don't use RI. But my normal
rule of thumb is: you wil get some low percentage of RI violations,
if you rely only on correct code, because there will be some errors
in your code. And then you will have to fix this by setting up regular
correction batch runs etc.

So if you don't have severe restrictions from the performance point
of view, I would recommend to use RI today.

Kind regards

Bernd



Am 10.10.2013 21:30, schrieb J.B.:
> Give this man a prize !
>
> -----Original Message-----
> From: L.L.
> Sent: Thursday, October 10, 2013 9:38 AM
> To: db2-l@lists.idug.org
> Subject: [DB2-L] - RE: Referential integrity is it a good choice?
>
> Esteemed List,
>
> I have watched this thread with great interest. Lots of good points,
> anecdotes, tips and tricks.
>
> One thing not yet said (and may be obvious, but being an old guy I'll point it
> out anyway): Referential Integrity is a property of the logical data model that
> models business rules. For example, if there is a one-to-many relationship
> between Customers and Orders, and you require that Orders only exist for valid
> (i.e. existing) Customers, this is a property of your data model.
>
> It is a separate (but related) issue of implementing the business logic that
> has been discussed so far. If your business model and rules require that Orders
> exist (e.g. in the Orders Table) only if the corresponding Customer exists
> (Customer Table), you have several options for implementing this rule:
>
> o DBMS-enforced referential integrity, implemented as Primary/Foreign Keys and Delete Rules.
> o DBMS-enforced referential integrity, implemented as Triggers.
> o Application-enforced referential integrity, implemented as code.
> o A combination/hybrid of the above.
>
> One of the touted (logical) advantages of DBMS-enforced referential integrity
> is that business rules are defined with the physical data model. (In a similar
> sense, domain integrity (valid values) can be enforced in the DBMS using column
> attributes, defaults, column constraints, fieldprocs, etc.).
>
> Consider this question: "What happens to the Orders if we Delete a Customer?".
> With DBMS-enforced RI, you can look at the catalog. Indeed, all RI-related
> questions are answered in one spot, using SQL.
>
> With application-enforced RI, you have to look at (and understand!)
> application coding and logic. This may not be a simple process, as the "RI" code
> may be strewn across multiple applications, maybe across multiple hardware
> platforms in multiple languages!
>
> In a similar fashion, what if there is a requirement to change a business rule
> such that the RI changes? ("No, DON'T Delete all orders when Deleting a
> Customer!"). The change is accomplished easily by Drop/Create of a Foreign Key
> changing the Delete rule. With application-enforced RI, it's a coding change.
>
> Despite all of the above, many times performance (physical) issues trump
> logical design issues. Witness the times that we've had to "denormalize for
> performance".
>
> As CPU and DBMS performance increases, many of the past performance problems
> with DBMS-enforced RI are less of an issue. Still, it remains a necessity that
> the DBA staff (application DBAs and systems DBAs) be included *early* in the
> application and database design process to address these issues. Among others :)
>
> - L.L.
>
>
>
> From: E.M.
> To: db2-l@lists.idug.org
> Sent: Wednesday, October 9, 2013 8:40 AM
> Subject: [DB2-L] - Referential integrity is it a good choice?
>
> Hi ,
> In our current DB2 system, we have no table with referential integrity.
> The developers have asked us about this possibility, but although it seems an
> advantage from the point of view of development, do not know if it can be very
> problematic for db2 administration.
> Does anyone have experience with this?
>
> Thanks!
>
> E.M.
>
>
> -----End Original Message-----
>
>
> -----End Original Message-----
>
>