If you have learned about the WITH CHECK OPTION clause for the views, then you must learn and understand the LOCAL and CASCADED options as well. These two options in the WITH CHECK OPTION clause are a little bit confusing and difficult to understand, but we will make it a lot easier.
Prior knowledge of the WITH CHECK OPTION clause is necessary to understand this tutorial. You can read the WITH CHECK OPTION tutorial here.
So in this tutorial, we will be seeing detailed information about the CASCADED option in the WITH CHECK OPTION of MySQL views. So, let’s get started!
Introduction to the CASCADED Option
The working of LOCAL and CASCADED options in the previous version of the mysql (5.6) was totally different. Starting from version 5.7.6, these options work differently.
The WITH CHECK OPTION clause sets the rule for the DML statements like INSERT, UPDATE and DELETE queries on the view. It checks the new data against the rules and ensures the data is correct as per the rules.
You can create a view based on other views also. Therefore the rules are checked for the dependent views.
MySQL provides us with two options to define the scope of this check- LOCAL and CASCADED.
When no option is specified with the WITH CHECK OPTION clause, MySQL treats it as a CASCADED CHECK.
In this tutorial, we will learn about the CASCADED option only.
WITH CASCADED CHECK OPTION in MySQL View
The official documentation of MySQL says-
With CASCADED, the view’s WHERE clause is checked first. After which, it checks recurses to all underlying views then adds the WITH CASCADED CHECK OPTION option, and the same rules are applied.
Let’s understand it by the example.
Let’s create a simple table to store the values.
CREATE TABLE valuess(
id INT );Code language: SQL (Structured Query Language) (sql)
Now let’s create a view on the “valuess” table.
CREATE VIEW v1 AS
SELECT * FROM valuess WHERE id > 30;Code language: SQL (Structured Query Language) (sql)Here, we haven’t specified any check option. Therefore, we can insert any value through view v1 and we won’t get any error message. Let’s try.
INSERT INTO v1 VALUES(10);
INSERT INTO v1 VALUES(40);Code language: SQL (Structured Query Language) (sql)
As you can see, we didn’t get any errors.
Now let’s create a view v2 based on view v1 with the WITH CASCADED CHECK OPTION.
CREATE VIEW v2 AS
SELECT * FROM v1
WITH CASCADED CHECK OPTION;Code language: SQL (Structured Query Language) (sql)Here, we have specified no condition. But it contains the CASCADED option.
Now let’s try to insert a value in the v2.
INSERT INTO v2 VALUES(10);
INSERT INTO v2 VALUES(40);Code language: SQL (Structured Query Language) (sql)
Here you can see, when we tried to insert 10, it produced an error. This is because, when we insert a value in the v2, it checks back the parent view v1 and adds the WITH CASCADED CHECK OPTION. The v1 doesn’t have the WITH CHECK OPTION clause. However, it is now explicitly added and therefore it raised an error.
Now let’s create a view v3 based on view v2 without the WITH CHECK OPTION.
CREATE VIEW v3 AS
SELECT * FROM v2
WHERE id < 50;Code language: SQL (Structured Query Language) (sql)Here, we have specified a condition that the value must be less than 50. Now let’s try inserting different values in the table through v3.
INSERT INTO v3 VALUES(10);
INSERT INTO v3 VALUES(40);
INSERT INTO v3 VALUES(60);Code language: SQL (Structured Query Language) (sql)
Here, when we try to insert value 10, it generates an error even if the value fulfills the condition of view v3 which is “value must be less than 50”. This is because the value 10 doesn’t fulfill the condition of view v1.
As said earlier, the CASCADED option adds the same check option to its underlying views and checks for the rules.
When we try to insert 60, it gets accepted even if it is greater than 50. This is because view v3 doesn’t have the WITH CHECK OPTION. Therefore, it will not check the value. After that, view v1 checks the value and because the value is greater than 30, it gets accepted.
Note that, the CASCADED option is applied to its underlying views and not the next views.
Conclusion
In the conclusion, the CASCADED option checks the rules of a view, adds the WITH CASCADED CHECK OPTION clause to its underlying views and checks their rules.
The following image illustrates the working of the CASCADED option in the above examples.
References
MySQL official documentation on the VIEW CHECK OPTION clause.
Detailed tutorial on the VIEW CHECK OPTION clause.