MySQL Tutorial/View/Check

Check Options

When creating an updatable view, MySQL allows you to specify how much the parser will do.

An updatable view is part of an UPDATE, INSERT, or DELETE statement

This is done with the WITH CHECK OPTION syntax.

Enabling check options tells the parser to review the WHERE clause when processing a statement to update a record or set of records in the view.

With check options enabled, you can only insert, update, or delete any records existing from the view.

Two keywords can be added to the WITH CHECK OPTION statement: LOCAL and CASCADING.

LOCAL tells that when updating a view, MySQL should ensure that the data being updated is part of the view.

The default is LOCAL.

WITH CHECK OPTION is used with only an updatable view.

If the algorithm is set to TEMPTABLE, or the SQL statement uses syntax or a keyword that makes the view not updatable, specifying WITH CHECK OPTION will result in a MySQL error: ERROR 1368 (HY000) at line 5: CHECK OPTION on non-updatable view.

The CASCADING option checks both the current view, and if the current view is based on another view, the check looks at that view as well to verify that the change conforms to the view definition.

With the CASCADING keyword, the query parser continues down through all views until the parser reaches a table to verify that all column and row changes that are in the issued statement are defined in the hierarchy of views.

Creating views based on other views is covered in the "Defining Views of Views" section later in this chapter.

Quote from Pro MySQL

by Michael Kruckenberg (Author), Jay Pipes (Author)

# Paperback: 768 pages

# Publisher: Apress (July 25, 2005)

# Language: English

# ISBN-10: 159059505X

# ISBN-13: 978-1590595053

Creating a View with Check Options

mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(8,"James",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> CREATE OR REPLACE VIEW myView AS
    -> SELECT id, first_name, city FROM employee
    -> WHERE id = 1 WITH LOCAL CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from myView;
+------+------------+---------+
| id   | first_name | city    |
+------+------------+---------+
|    1 | Jason      | Toronto |
+------+------------+---------+
1 row in set (0.00 sec)
mysql>
mysql>
Illegal Update of a View with Check Options
mysql>
mysql> UPDATE myView SET city = "new" WHERE id = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
mysql>
mysql>
mysql> drop view myView;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>