Pre-General Availability Draft: 2018-02-25
WITH CHECK OPTION clause can be given for
an updatable view to prevent inserts to rows for which the
WHERE clause in the
select_statement is not true. It also
prevents updates to rows for which the
clause is true but the update would cause it to be not true (in
other words, it prevents visible rows from being updated to
WITH CHECK OPTION clause for an updatable
keywords determine the scope of check testing when the view is
defined in terms of another view. When neither keyword is given,
the default is
WITH CHECK OPTION testing is
LOCAL, the view
WHEREclause is checked, then checking recurses to underlying views and applies the same rules.
CASCADED, the view
WHEREclause is checked, then checking recurses to underlying views, adds
WITH CASCADED CHECK OPTIONto them (for purposes of the check; their definitions remain unchanged), and applies the same rules.
With no check option, the view
WHEREclause is not checked, then checking recurses to underlying views, and applies the same rules.
Consider the definitions for the following table and set of views:
CREATE TABLE t1 (a INT); CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2 WITH CHECK OPTION; CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0 WITH LOCAL CHECK OPTION; CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0 WITH CASCADED CHECK OPTION;
v3 views are
defined in terms of another view,
v2 are checked against its
LOCAL check option, then the check recurses to
v1 and the rules are applied again. The rules
v1 cause a check failure. The check for
v3 also fails:
mysql> INSERT INTO v2 VALUES (2); ERROR 1369 (HY000): CHECK OPTION failed 'test.v2' mysql> INSERT INTO v3 VALUES (2); ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'