创建视图的时候有几种CHECK OPTION
看看这个例子:
CASCADED CHECK OPTION 的作用是让当前创建的视图继承它使用到的视图的过滤条件,其他就没有什么区别了,都是为了阻止INSERT和UPDATE不符合过滤条件的数据。
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不符合过滤条件的数据。