Submitted by: Bill Oliver (verbguy)

Here is a minor error-message issue I saw while re-benching some old tests.

Here is the SQL:

recreate table works (empnum char(3) not null, pnum char(3) not null, hours decimal(5,0) );
recreate view subsp as SELECT EMPNUM,PNUM,HOURS
FROM WORKS
WHERE EMPNUM='E3'
WITH CHECK OPTION;
insert into works values ('E3', 'P2', 20);

UPDATE SUBSP SET EMPNUM = 'E9' WHERE PNUM = 'P2';

The update produces this error:

Statement failed, SQLCODE = -297
Operation violates CHECK constraint on view or table SUBSP
-At trigger 'CHECK_1'

There is no name for the CHECK constraint, so the null string is inserted between "constraint" and "on". This produces 2 spaces between those words. Not a big deal, but it got me thinking about the error here.

If the update statement did not have the CHECK constraint, it would succeed. Then, the view would no longer contain this row - the row would "disappear" from the view. The WITH CHECK OPTION on the view prevents this from happening.

A friendlier message would be to catch this case and provide a message like

"attempt to remove record from view %s defined using WITH CHECK OPTION failed"