The 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 WHERE
clause is true but the update would cause it to be not true (in
other words, it prevents visible rows from being updated to
nonvisible rows).
In a WITH CHECK OPTION
clause for an updatable
view, the LOCAL
and CASCADED
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 CASCADED
. The
LOCAL
keyword restricts the CHECK
OPTION
only to the view being defined.
CASCADED
causes the checks for underlying views
to be evaluated as well.
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;
Here the v2
and v3
views are
defined in terms of another view, v1
.
v2
has a LOCAL
check option,
so inserts are tested only against the v2
check. v3
has a CASCADED
check option, so inserts are tested not only against its own
check, but against those of underlying views. The following
statements illustrate these differences:
mysql> INSERT INTO v2 VALUES (2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'