WITH CASCADED CHECK OPTION 的用法 ----经典案例

The WITH CASCADED CHECK OPTION specifies that every row that is inserted or updated through the view must conform to the definition of the view. In addition, the search conditions of all dependent views are checked when a row is inserted or updated. If a row does not conform to the definition of the view, that row cannot be retrieved using the view.

For example, consider the following updateable view:

     CREATE VIEW V1 AS SELECT COL1
             FROM T1 WHERE COL1 > 10

Because no WITH CHECK OPTION is specified, the following INSERT statement is successful even though the value being inserted does not meet the search condition of the view.

     INSERT INTO V1 VALUES (5)

Create another view over V1, specifying the WITH CASCADED CHECK OPTION:

     CREATE VIEW V2 AS SELECT COL1
                FROM V1 WITH CASCADED CHECK OPTION

The following INSERT statement fails because it would produce a row that does not conform to the definition of V2:

     INSERT INTO V2 VALUES (5)

Consider one more view created over V2:

     CREATE VIEW V3 AS SELECT COL1
               FROM V2 WHERE COL1 < 100

The following INSERT statement fails only because V3 is dependent on V2, and V2 has a WITH CASCADED CHECK OPTION.

     INSERT INTO V3 VALUES (5)

However, the following INSERT statement is successful because it conforms to the definition of V2. Because V3 does not have a WITH CASCADED CHECK OPTION, it does not matter that the statement does not conform to the definition of V3.

     INSERT INTO V3 VALUES (200)