LOCAL In MySQL View WITH CHECK OPTION

In this tutorial, we will see detailed information about the LOCAL in the WITH CHECK OPTION clause. To understand the LOCAL concept, you must possess knowledge of the WITH CHECK OPTION clause. If you are not aware of the WITH CHECK OPTION clause, you can read and learn it here. Now, without further ado, let’s get started with the CASCADED option.

Also read: CASCADED In MySQL View WITH CHECK OPTION

Introduction to WITH LOCAL CHECK 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 is used to set the rules on the views to prevent inserting or updating the records through the DML queries.

However, just checking the rules on only one view is not enough because a view can be created based on other views as well. Therefore, we have to check all underlying views of a particular view.

Now, MySQL provides us with the two scopes of the check- CASCADED and LOCAL. We have already covered the CASCADED in WITH CHECK OPTION clause.

The official documentation of the MySQL blog says,

“With LOCAL, the view WHERE clause is checked, then checking recurses to underlying views and applies the same rules”.

It’s a little bit confusing if you are reading this for the first time. Let me explain it through the example.

WITH LOCAL CHECK OPTION Example

The LOCAL scope is simpler than the CASCADED in the WITH CHECK OPTION clause. Let’s take an example to understand it effectively.

But before that, let’s create a table to hold the integer values.

We have already created a table of the below description. The table need not necessarily consists of any data.

Now, let’s create a view v1 on the table “valuess”.

CREATE VIEW v1 
AS SELECT * FROM valuess
WHERE id > 10;Code language: SQL (Structured Query Language) (sql)

Here, we have created a view and applied the condition that the value of ‘id’ must e greater than 10. However, we haven’t set the WITH CHECK OPTION clause. Therefore, the view will accept any value that we insert.

Let’s try inserting values in view v1.

INSERT INTO v1 VALUES(20);
INSERT INTO v1 VALUES(5);Code language: SQL (Structured Query Language) (sql)

Note that, because we haven’t set the WITH CHECK OPTION, both values will get inserted.

As you can see, we did not get an error.

Now let’s create a view v2 based on view v1.

CREATE VIEW v2 AS 
SELECT * FROM v1
WHERE id < 10
WITH LOCAL CHECK OPTION;Code language: SQL (Structured Query Language) (sql)

Here, we have created a view v2 with the WHERE condition as well as the WITH CHECK OPTION. Note that, if the value that we insert is greater than 10, we will get an error.

Let’s try inserting values in view v2.

INSERT INTO v2 VALUES(20);
INSERT INTO v2 VALUES(5);Code language: SQL (Structured Query Language) (sql)

Here, when we try to insert the value 20 in view v2, we get an error, because we have set the rule on it of accepting values less than 10 only.

If you note that, the value 5 gets accepted here. However, view v1 has a rule that only values greater than 10 are permitted. Even though the value 5 breaks the rule of view v1, it gets accepted.

This is because the LOCAL scope checks the rule for the current view and then recursively checks its underlying views.

Because we don’t have the WITH CHECK OPTION clause on view v1, no error is raised.

Now let’s compare it with the CASCADED scope so that you will understand the difference between the LOCAL and CASCADED scopes.

Difference Between LOCAL and CASCADED Scopes

We can easily spot the difference between the LOCAL and CASCADED scopes by creating another view.

Let’s create a view v3 on v1. (Remember, we are creating a view v3 on top of v1).

CREATE VIEW v3 AS
SELECT * FROM v1
WHERE id < 10
WITH CASCADED CHECK OPTION;Code language: SQL (Structured Query Language) (sql)

Here, we have set the same condition on the v3 as of the v2 but it has the CASCADED scope.

Now let’s try inserting values in the v3.

INSERT INTO v3 VALUES(20);
INSERT INTO v3 VALUES(5);Code language: SQL (Structured Query Language) (sql)

Here, we are trying to insert the same values as of the previous one.

Let’s first check the output and then I will simplify how we got that.

You can see here, both values are declined to get inserted. Let me explain it why.

The CASCADED scope checks the rule of the current view, then it recursively adds the WITH CASCADED CHECK OPTION to all its underlying views and checks for the rules.

In this case, the value 5 is checked against the rule on v3. It is less than 10 so it is permitted. However, because the view has the CASCADED scope, it will apply the WITH CASCADED CHECK OPTION to its underlying views, in this case, view v1.

Now, view v1 also has the WITH CASCADED CHECK OPTION, therefore the value 5 will get checked against the rule. Here, the value is less than 5 and is not permitted. Therefore, MySQL raises an error “CHECK OPTION FAILED”.

Conclusion

The following image illustrates the working of the WITH LOCAL CHECK OPTION and the WITH CASCADED CHECK OPTION.

I hope this tutorial is easy to understand and that I made it easier through the examples. The official guide is not so descriptive and complicates the new learners as it doesn’t explain the topic very well. Don’t forget to share this tutorial with your friends who are willing to learn interesting topics in MySQL.