Understanding DB2 Nested Views - Page 3

Views with Check Option

Data that is to be updated or inserted into a view can be validated if the view definition includes conditions (such as a WHERE clause) and if the view is defined using the WITH CHECK OPTION clause. An error is returned if these conditions are not met during an attempted insert or update operation.

MY_EMP_VIEW3 is an example of a view definition using the WITH CASCADED CHECK OPTION clause. If a view is defined with this clause (or the WITH CHECK OPTION clause), the view definition is used to check the validity of data involved in any insert or update operation. Moreover, the view inherits checking behavior from any updatable views on which it depends. It inherits this behavior even if those views were not defined using the WITH CHECK OPTION clause.

In the case of MY_EMP_VIEW3, any row that is to be inserted into this view must include the value 'C01' for the DEPARTMENT column, even though the view definition for MY_EMP_VIEW3 does not have a WHERE clause to that effect. The WITH CASCADED CHECK OPTION clause, however, ensures that this condition, which is part of MY_EMP_VIEW2, applies to the dependent view MY_EMP_VIEW3 as well.

db2 insert into my_emp_view3 (id, firstname, initial, lastname, department, education)
 values ('000420', 'TOM', ' ', 'THUMB', 'D11', 8)

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0161N  The resulting row of the INSERT or UPDATE does not conform to the
view definition.  SQLSTATE=44000

db2 insert into my_emp_view3 (id, firstname, initial, lastname, department, education)
 values ('000420', 'TOM', ' ', 'THUMB', 'C01', 8)

DB20000I  The SQL command completed successfully.

It is important to remember that no data validation during insert or update operations occurs if the WITH CHECK OPTION is not specified in the definition of the dependent view. For example, given the definition of MY_EMP_VIEW2 shown earlier, and a definition of MY_EMP_VIEW3 that does not include the WITH CHECK OPTION, an insert operation specifying a department other than C01 completes successfully:

db2 create view my_emp_view3
  (id, firstname, initial, lastname, department, ext, education)
 as select
  id,
  firstname,
  initial,
  lastname,
  department,
  ext,
  education
 from my_emp_view2

DB20000I  The SQL command completed successfully.

db2 insert into my_emp_view3 (id, firstname, initial, lastname, education)
 values ('000420', 'TOM', ' ', 'THUMB', 8)

DB20000I  The SQL command completed successfully.

In this case, an insert operation with no department specification is acceptable: a value of 'C01' is not enforced and, because the WORKDEPT column in the EMPLOYEE table is nullable, a null value for the corresponding DEPARTMENT column in MY_EMP_VIEW3 is not rejected.

If a view depends on another view that was defined using the WITH CHECK OPTION clause, data validation occurs during insert or update operations involving the dependent view:

db2 create view my_emp_view2
  (id, firstname, initial, lastname, department, ext, education, birthdate, salary)
 as select
  empno,
  firstnme,
  midinit,
  lastname,
  workdept,
  phoneno,
  edlevel,
  birthdate,
  salary
 from employee
 where workdept = 'C01'
with cascaded check option

DB20000I  The SQL command completed successfully.

db2 create view my_emp_view3
  (id, firstname, initial, lastname, department, ext, education)
 as select
  id,
  firstname,
  initial,
  lastname,
  department,
  ext,
  education
 from my_emp_view2

DB20000I  The SQL command completed successfully.

db2 insert into my_emp_view3 (id, firstname, initial, lastname, department, education)
 values ('000420', 'TOM', ' ', 'THUMB', 'D11', 8)

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0161N  The resulting row of the INSERT or UPDATE does not conform to the
view definition.  SQLSTATE=44000

You can also specify the WITH LOCAL CHECK OPTION when creating a view. If a view is defined using the WITH LOCAL CHECK OPTION, the view definition is used to check the validity of data involved in any insert or update operation. However, in this case, the view does not inherit search conditions from any insertable or updatable views on which it depends.