Example: Cascaded check option

 

This example shows how the check option is enforced on a number of dependent views that are defined with or without a check option.

Use the following table and views:

     CREATE TABLE T1 (COL1 CHAR(10))
 
     CREATE VIEW  V1 AS SELECT COL1
           FROM T1 WHERE COL1 LIKE 'A%'
 
     CREATE VIEW V2 AS SELECT COL1
           FROM V1 WHERE COL1 LIKE '%Z'
                                  WITH LOCAL CHECK OPTION
 
     CREATE VIEW V3 AS SELECT COL1
           FROM V2 WHERE COL1 LIKE 'AB%'
 
     CREATE VIEW V4 AS SELECT COL1
           FROM V3 WHERE COL1 LIKE '%YZ'
                                   WITH CASCADED CHECK OPTION
 
     CREATE VIEW V5 AS SELECT COL1
           FROM V4 WHERE COL1 LIKE 'ABC%'

Different search conditions are going to be checked depending on which view is being operated on with an INSERT or UPDATE statement.

• If V1 is operated on, no conditions are checked because V1 does not have a WITH CHECK OPTION specified.

• If V2 is operated on,

  • COL1 must end in the letter Z, but it doesn't need to start with the letter A. This is because the check option is LOCAL, and view V1 does not have a check option specified.

• If V3 is operated on,

  • COL1 must end in the letter Z, but it does not need to start with the letter A. V3 does not have a check option specified, so its own search condition must not be met. However, the search condition for V2 must be checked because V3 is defined on V2, and V2 has a check option.

• If V4 is operated on,

  • COL1 must start with 'AB' and must end with 'YZ'. Because V4 has the WITH CASCADED CHECK OPTION specified, every search condition for every view on which V4 is dependent must be checked.

• If V5 is operated on,

  • COL1 must start with 'AB', but not necessarily 'ABC'. This is because V5 does not specify a check option, so its own search condition does not need to be checked. However, because V5 is defined on V4, and V4 had a cascaded check option, every search condition for V4, V3, V2, and V1 must be checked. That is, COL1 must start with 'AB' and end with 'YZ'.

If V5 were created WITH LOCAL CHECK OPTION, operating on V5 means that COL1 must start with 'ABC' and end with 'YZ'. The LOCAL CHECK OPTION adds the additional requirement that the third character must be a 'C'.

 

Parent topic:

WITH CHECK OPTION on a view