Re: Some database discussions...

On 5/17/2017 10:26 AM, Nathan Andelin wrote:

I may be missing something. For shops that implement DB defined RI
constraints, how do they handle user interface requirements (i.e. friendly,
meaningful error messages, cursor positioning on relevant columns, and
highlighting)?

alter table Customer
add constraint "00005 Address foreign"
foreign key (addressID) references Address (id)
on delete restrict
on update restrict
;

By giving each constraint a pseudo-ID in the first n characters of
MSGDTA, it's possible to CHAIN to a friendly message file and help the
user out. INSERT, UPDATE, and DELETE all throw different SQLSTATE
errors, with corresponding SQL05nn MSGIDs. That's potentially part of
the logic to figure out where to position the cursor, but the RPG
program generally 'knows' it's trying to delete a row, etc.

Where it gets interesting is when a trigger tries to do that work, and
the trigger runs into RI problems. It happens. I kind of like standard
IBM i message handling for this reason. With the plumbing already in
place, I automagically collect unexpected errors from programs I never
knew were running. (And therefore don't have an 'error message array').