DB2创建VIEW的时候CHECK OPTION的作用

285572001

创建视图的时候有几种CHECK OPTION

CHECK OPTION
CASCADED CHECK OPTION
LOCAL CHECK OPTION

看看这个例子：

------------------------------ Commands Entered ------------------------------
CREATE TABLE tab1 (col1 SMALLINT );
CREATE VIEW v1 AS SELECT col1 FROM tab1 WHERE col1 > 20 ;
CREATE VIEW v2L AS SELECT col1 FROM v1 WITH LOCAL CHECK OPTION ;
CREATE VIEW v2C AS SELECT col1 FROM v1 WITH CASCADED CHECK OPTION ;
CREATE VIEW v3L AS SELECT col1 FROM v2L WHERE col1 < 50 ;
CREATE VIEW v3C AS SELECT col1 FROM v2C WHERE col1 < 50 ;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.


------------------------------ Commands Entered ------------------------------
INSERT INTO v1 VALUES (10);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
INSERT INTO v2L VALUES (5);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
INSERT INTO v2C VALUES (5);
------------------------------------------------------------------------------
INSERT INTO v2C VALUES (5)
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 operation does not conform
to the view definition.  SQLSTATE=44000



------------------------------ Commands Entered ------------------------------
INSERT INTO v3L VALUES (50);
INSERT INTO v3C VALUES (50);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
INSERT INTO v3L VALUES (100);
INSERT INTO v3C VALUES (100);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.

CASCADED CHECK OPTION 的作用是让当前创建的视图继承它使用到的视图的过滤条件，其他就没有什么区别了，都是为了阻止INSERT和UPDATE不符合过滤条件的数据。