Re: Pros and Cons of DB defined Referential Integrity Constraints

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.


On Thu, Oct 18, 2012 at 2:05 PM, Nathan Andelin <nandelin@xxxxxxxxx> wrote:

Up until now I've been content with coding RI constraints in DB I/O modules. For example:

http://www.radile.com/rdweb/temp/sscd100db.txt


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.

What about other pros and cons?

-Nathan

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.