Where MySQL views can be inconsistent and how can we ensure their consistency?

MySQLMySQLi Database

In case of updateable views, it is quite possible that we update the data that is not visible through the view because we create a view to revealing only the partial data of a table. Such kind of updates makes the view inconsistent. We can ensure the consistency of views by using WITH CHECK OPTION while creating or modifying the views. Although WITH CHECK OPTION clause is an optional part of CREATE VIEW statement but it is very useful to make views consistent.

Basically, the WITH CHECK OPTION clause prevents us from updating or inserting the rows which are not visible through a view. In a simple sense we can say that after using WITH CHECK OPTION clause, MySQL ensures that the insert or update operation is confirmed by the definition of the view. 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 are using 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 names ‘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)

As, we have not used WITH CHECK OPTION, so we can insert/update a new row in ‘Info’, even if it is not matched with its definition. It is illustrated in the following query and its result −

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 did not match with the definition of ‘Info’ hence it is not visible in the view. Now, in the following query, we are creating 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 will try to insert a row that matches with the definition of view ‘Info’, MySQL permits us to do so. It can be cleared from the following query and its result.

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 will try to insert the row that did not match with the definition of view ‘Info’, MySQL will not permit us to do and throws the error −

mysql> INSERT INTO Info(Id, Name, Address, Subject) values(134, 'Charanjeet','Amritsar','Geophysics');
ERROR 1369 (HY000): CHECK OPTION failed
raja
Published on 22-Feb-2018 15:21:16
Advertisements