Although I agree with the general sentiment expressed by others (if
is not expressed as declarative constraints in DB2 then the RI is
per cent guaranteed), I have to express a few exceptions to the
DB2 does a referential integrity check for every row insertion. You
increase efficiency if your application does a single check of a
the parent table and then makes multiple inserts to the child
should not be the sole determining factor for implementing RI or
though, because program logic can fail (bugs, errors, etc.) or be
from program to program; and ad hoc modifications will not be
If the application processing needs are such that the parent table
before even one child is inserted, consider not implementing DB2
RI. In this
case, DB2 would repeat the read process that the application must
to satisfy its processing needs. Same caveats as above though.
Do not use DB2 RI on tables built from another system that already
referentially intact. If the tables are updated after being built
from the external data source, consider building the RI into the
code where appropriate and ignoring the RI when building or
tables from the referentially intact source.
Do not use DB2 RI if tables are read only. If you need to scrub the
when loading, you still may want to use DB2 RI.
Define a primary (or unique) key to prohibit duplicate table rows.
should be done to ensure entity integrity regardless of whether
tables are related to the table being defined. Entity integrity
each row in a table represents a single, real-world entity.
Avoid large referential sets. Try not to tie together all tables in
system; otherwise, recovery, quiesce, and other utility processing
difficult to develop and administer. For this reason, consider not
referential constraints to tie lookup, code, and domain tables to
tables in your database. These tables are typically "low update"
done via ad hoc SQL.
Another issue to deal with is that not every RI need is covered by
declarative constraints. For example, there is no ON UPDATE
capability. And you can not define a constraint such that when the
child row is deleted for a parent row, the parent row should be
(this is called pendant delete). As of DB2 V6, you can code
enforce these type of RI rules, though. Like declarative RI,
non-bypassable, which is good. But the down side is that you must
logic in the triggers yourself (which can be prone to bugs).
Craig S. Mullins
Director, DB2 Technology Planning
[login to unmask email]
[login to unmask email]
>From: madhavan [mailto:[login to unmask email]
>Sent: Monday, January 24, 2000 6:04 AM
>To: [login to unmask email]
>Subject: DB2 referential integrity
>One of our customer's shop standard is that DB2 referential
>not be used in the normal cases. Referential integrity will be
>through program logic. I was also told that 90% of the DB2
shops do not use
>DB2 referential integrity due to performance reasons.
>I thought referential integrity through DB2 will be performance
>than checking it through the program. Also unless we do a fetch
>there is always a chance of loosing the integrity of data when
>referential check in the programs.
>Can you please give me your experiences on this?