Where might MySQL views be inconsistent, and how to ensure their consistency?

#In the case of an updatable view, we are likely to update data that is not visible through the view because the view we create only displays part of the table's data. This update makes the view inconsistent. We can ensure the consistency of the view by using WITH CHECK OPTION when creating or modifying the view. Although the WITH CHECK OPTION clause is an optional part of the CREATE VIEW statement, it is very useful for making the view consistent.

Basically, the WITH CHECK OPTION clause prevents us from updating or inserting rows that are not visible through the view. In simple terms, we can say that after using WITH CHECK OPTION clause, MySQL ensures that the insert or update operation is confirmed by the view definition. Following is the syntax of WITH CHECK OPTION clause-

Syntax

CREATE OR REPLACE VIEW view_name AS Select_statement WITH CHECK OPTION;

Example

To illustrate the above concept, we use the following data from table "Student_info"-

mysql> Select * from student_info;
+------+---------+------------+------------+
| id   | Name    | Address    | Subject    |
+------+---------+------------+------------+
| 101  | YashPal | Amritsar   | History    |
| 105  | Gaurav  | Chandigarh | Literature |
| 125  | Raman   | Shimla     | Computers  |
| 130  | Ram     | Jhansi     | Computers  |
+------+---------+------------+------------+
4 rows in set (0.08 sec)

Now, with the help of the following query, we will create the view name "Info". Here we are not using WITH CHECK OPTION.

mysql> Create OR Replace VIEW Info AS Select Id, Name, Address, Subject from student_info WHERE Subject = 'Computers';
Query OK, 0 rows affected (0.46 sec)

mysql> Select * from info;
+------+-------+---------+-----------+
| Id   | Name  | Address | Subject   |
+------+-------+---------+-----------+
| 125  | Raman | Shimla  | Computers |
| 130  | Ram   | Jhansi  | Computers |
+------+-------+---------+-----------+
2 rows in set (0.00 sec)

Because, we are not using WITH CHECK OPTION, so we can insert/update new rows in "Info" even if it does not match its definition. The following query and its results illustrate this -

mysql> INSERT INTO Info(Id, Name, Address, Subject) values(132, 'Shyam','Chandigarh', 'Economics');
Query OK, 1 row affected (0.37 sec)

mysql> Select * from student_info;
+------+---------+------------+------------+
| id   | Name    | Address    | Subject    |
+------+---------+------------+------------+
| 101  | YashPal | Amritsar   | History    |
| 105  | Gaurav  | Chandigarh | Literature |
| 125  | Raman   | Shimla     | Computers  |
| 130  | Ram     | Jhansi     | Computers  |
| 132  | Shyam   | Chandigarh | Economics  |
+------+---------+------------+------------+
5 rows in set (0.00 sec)

mysql> Select * from info;
+------+-------+---------+-----------+
| Id   | Name  | Address | Subject   |
+------+-------+---------+-----------+
| 125  | Raman | Shimla  | Computers |
| 130  | Ram   | Jhansi  | Computers |
+------+-------+---------+-----------+
2 rows in set (0.00 sec)

The above result set shows that the new row does not match the definition of "Info", so it is not visible in the view. Now, in the following query, we will create the same view "Info"

by using "WITH CHECK OPTION" -

mysql> Create OR Replace VIEW Info AS Select Id, Name, Address, Subject from student_info WHERE Subject = 'Computers' WITH CHECK OPTION;
Query OK, 0 rows affected (0.06 sec)

Now if we try to insert with View "Info" is defined to match rows, MySQL allows us to do this. This can be cleared from the query below and its results.

mysql> INSERT INTO Info(Id, Name, Address, Subject) values(133, 'Mohan','Delhi','Computers');
Query OK, 1 row affected (0.07 sec)

mysql> Select * from info;
+------+-------+---------+-----------+
| Id   | Name  | Address | Subject   |
+------+-------+---------+-----------+
| 125  | Raman | Shimla  | Computers |
| 130  | Ram   | Jhansi  | Computers |
| 133  | Mohan | Delhi   | Computers  |
+------+-------+---------+-----------+
3 rows in set (0.00 sec)

But suppose if we try to insert a row that does not match the definition of view "Info", MySQL will not allow us to do so and throw an error -

mysql> INSERT INTO Info(Id, Name, Address, Subject) values(134, 'Charanjeet','Amritsar','Geophysics');
ERROR 1369 (HY000): CHECK OPTION failed

The above is the detailed content of Where might MySQL views be inconsistent, and how to ensure their consistency?. For more information, please follow other related articles on the PHP Chinese website!