Checking RI in a program is basically wishful thinking. It just isn't
going to happen unless you have someone to stand over every developer
and check everything they do and even then it is not going to happen.

Coding in the database insures that the check is done and people
cannot write garbage to your database. Second, your DBA should be the
one deciding what RI and check constraints are needed. Much easier to
check that than every program.

Also, efficiency. The check is done at the DB level and is a lot more
efficient than coding checks into your programs.

Downsides

1. Our biggest single problem is locks. You write or update a table
that say checks the part number for validity but that record is
locked. Your program bombs out. We run into this all the time.
2. RI system table corruption. In the beginning we had a ton of this
but it has gotten better and we have a process in place that checks
for DB corruption on a regular basis and fixes it if it finds it.

However, this week we've been cleaning up a mess in our database that was caused by our failing to implement an RI constraint in one of our programs. One might ask, where were our QA procedures? While that's a valid concern, the problem could have also been prevented if we added a step to our development process to define RI constraints using DDL or ADDPFCST whenever we add new tables to our database? We're adding new tables every month!

I suppose that one pro for using DB defined constraints is that you use a fairly simple declarative syntax as opposed to program logic. While we generally limit DB inserts and updates to a single RPG I/O module per table, I suppose that another pro for DB defined constraints is that they apply to any other interface that shops may chose to update the DB.

This mailing list archive is Copyright 1997-2016 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact