VIEWs have been a handy feature of MySQL for many years but do you know about the WITH CHECK OPTION clause? The WITH CHECK OPTION clause is used for a updatable views to prohibit the changes to those views that would produce rows which are not included in the defining query.
VIEW Definition
Since a lot of SQL novices read my blog, I'd like to start with the definition of a view "Views are stored queries that when invoked produce a result set. A view acts as a virtual table" according the MySQL Manual.
SQL > CREATE VIEW x_city AS
SELECT Name,
CountryCode,
District
FROM city;
Query OK, 0 rows affected (0.0358 sec)
SQL > SELECT * FROM x_city LIMIT 4;
+----------------+-------------+----------+
| Name | CountryCode | District |
+----------------+-------------+----------+
| Kabul | AFG | Kabol |
| Qandahar | AFG | Qandahar |
| Herat | AFG | Herat |
| Mazar-e-Sharif | AFG | Balkh |
+----------------+-------------+----------+
4 rows in set (0.0200 sec)
SQL > SELECT Name, District FROM x_city limit 4;
+----------------+----------+
| Name | District |
+----------------+----------+
| Kabul | Kabol |
| Qandahar | Qandahar |
| Herat | Herat |
| Mazar-e-Sharif | Balkh |
+----------------+----------+
SQL > SELECT * FROM x_city LIMIT 4;
+----------------+-------------+----------+
| Name | CountryCode | District |
+----------------+-------------+----------+
| Kabul | AFG | Kabol |
| Qandahar | AFG | Qandahar |
| Herat | AFG | Herat |
| Mazar-e-Sharif | AFG | Balkh |
+----------------+-------------+----------+
4 rows in set (0.0200 sec)
SQL > SELECT Name, District FROM x_city limit 4;
+----------------+----------+
| Name | District |
+----------------+----------+
| Kabul | Kabol |
| Qandahar | Qandahar |
| Herat | Herat |
| Mazar-e-Sharif | Balkh |
+----------------+----------+
Views are used to obfuscate column names, capture vital queries for later reuse, and many other reasons. For the most part we can treat it as a table. The above example joins data from two tables but we can treat the view as a separate, different table. As can be seen above we can select all the columns from the view or a subset of the columns defined in the view.
Updatable Views
Some views can be used to specify tables to be updated in data change statements. In the example below we will use a simple one integer column table.
.
SQL > create view v_2 as select * from t2;
Query OK, 0 rows affected (0.0072 sec)
SQL > select * from v_2;
+----+
| id |
+----+
| 1 |
| 3 |
| 5 |
| 7 |
| 9 |
| 11 |
+----+
6 rows in set (0.0009 sec)
SQL > insert into v_2 (id) values (99);
Query OK, 1 row affected (0.0060 sec)
SQL > select * from v_2;
+----+
| id |
+----+
| 1 |
| 3 |
| 5 |
| 7 |
| 9 |
| 11 |
| 99 |
+----+
The v_2 view updated the underlying table t2.
WITH CHECK OPTION
The WITH CHECK OPTION prevents inserts to rows for which the WHERE clause in the select_statement is not true. The view v_2a only retrieves data where the id value is greater than 4. If it is used in an attempt to update a value less than 5 it will raise and error.
SQL > CREATE VIEW v_2a AS
SELECT * FROM t2 WHERE id > 4 WITH CHECK OPTION;
Query OK, 0 rows affected (0.0102 sec)
SQL > SELECT * FROM v_2a;
+----+
| id |
+----+
| 5 |
| 7 |
| 9 |
| 11 |
| 99 |
+----+
5 rows in set (0.0009 sec)
SQL > insert into v_2a (id) values (2);
ERROR: 1369: CHECK OPTION failed 'demo.v_2a'
And note the error provides the name of the view and the schema to helpt track it down.
For more details please refer to the manual page on The View WITH CHECK OPTION Clause
