Using DB2 RI
On the other hand, if you're inserting a row into an invoice table, you want to make sure the parent order stays intact. And if you delete the order, you want to make sure the invoice gets deleted as well. It's in these situations that I prefer DB2 RI.
Once you choose DB2 RI, you must decide whether to use DELETE CASCADE or DELETE RESTRICT. With DELETE CASCADE, you can delete the parent ORDER row, and DB2 will delete the child INVOICE row(s) for you. DELETE RESTRICT requires that you delete the child rows in INVOICE before deleting the parent rows in ORDER.
While DELETE CASCADE offers greater convenience, DELETE RESTRICT gives you control over the size of the logical unit of work by issuing a commit. If hundreds of rows are being deleted for each parent row, you can delete the children rows first and issue a commit every few hundred rows to avoid a long rollback if something goes wrong. DELETE CASCADE is much easier to use, but you must be careful with it. DETELE CASCADE can lead to lock escalation, dead lock and timeouts, and, in some cases, long rollback times when a deadlock situation occurs.
With either DB2 RI option, you must ensure that the table indexes support the referential integrity. I can't tell you how shops I've seen incur performance problems because they didn't have the proper indexes defined to support the RI.
Next week I'll provide some sample SQL that reports on missing supporting indexes for foreign key relationships.