MySQL Views- WITH CHECK OPTION

In this tutorial, we will learn about the WITH CHECK OPTION statement that is provided by MySQL to apply the check constraint to the views. So, let’s get started!

Also read: MySQL CHECK Constraint – A Complete Guide

Introduction to WITH CHECK OPTION clause

To understand the WITH CHECK OPTION clause, let’s take an example first.

Let’s say you have created a view to display only particular data from the table using the where condition. Now, you can update the data in the table through the view even if the data is not displayed by the view. This is not what we want.

The view which displays the partial records must handle the update operations for those records only which are visible. To achieve that, MySQL provides us with the WITH CHECK OPTION clause.

Using the WITH CHECK OPTION, you can prevent the view from updating the records that are not visible. Moreover, the view also prevents inserting the new data for which the WHERE clause in the select statement is not true.

Also, the WITH CHECK OPTION clause prevents the visible rows from being updated to nonvisible rows because the update would cause the where condition of the row to be false.

But first, let’s see the syntax of the WITH CHECK OPTION clause.

MySQL WITH CHECK OPTION Syntax

The WITH CHECK OPTION is an optional clause and is applied when the view is created.

CREATE [OR REPLACE VIEW] view_name 
AS select_statement
WITH CHECK OPTION;Code language: SQL (Structured Query Language) (sql)

Here, you just need to add the WITH CHECK OPTION clause without any further conditions.

Note that, the semicolon must be put at the end of this clause instead of the select statement.

MySQL WITH CHECK OPTION Examples

For the demonstration of the WITH CHECK OPTION clause, we will use the following table schema and data.

Now, let’s create a view.

CREATE OR REPLACE VIEW CSStuds AS 
SELECT * FROM students 
WHERE dept = "CS";Code language: SQL (Structured Query Language) (sql)

Let’s execute the select query on the view that we have created.

SELECT * FROM CSStuds;Code language: SQL (Structured Query Language) (sql)

Now, let’s try to modify the record that is not visible using the view.

UPDATE CSStuds SET dept="CS" WHERE id=2;Code language: SQL (Structured Query Language) (sql)

Here, id 2 is not visible in the view data, but still, the record of having id 2 is updated successfully.

Let’s try inserting a new record which doesn’t have the department CS.

INSERT INTO CSStuds (name,dept,subject,marks) VALUES("Zara","Language","Hindi",50);
SELECT * FROM students;Code language: SQL (Structured Query Language) (sql)

The value gets inserted into the ‘students’ table through the CSStuds table. However, it is not visible in the view because it doesn’t have the department CS.

But this is not what we want.

The new data must not get inserted if its department is not CS. The same goes for updating the records.

For this, we will use the WITH CHECK OPTION clause while creating a view.

So, let’s create the same view again.

CREATE OR REPLACE VIEW CSStuds AS 
SELECT * FROM students 
WHERE dept = "CS"
WITH CHECK OPTION;Code language: SQL (Structured Query Language) (sql)

The view is created.

Let’s try inserting a new record having the department language.

INSERT INTO CSStuds (name,dept,subject,marks) VALUES("Naira","Language","Tamil",70);Code language: SQL (Structured Query Language) (sql)

This will raise an error. Let’s insert a record having the department CS.

INSERT INTO CSStuds (name,dept,subject,marks) VALUES("Naira","CS","Tamil",70);Code language: SQL (Structured Query Language) (sql)

The above statement will get executed.

Let’s run both queries and see the output.

As you can see, we get the error ‘check option failed’ for the first statement. Whereas, the second query was executed successfully.

Now, let’s try updating the record and see if the output.

UPDATE CSStuds SET dept="CS" 
WHERE id=2;

UPDATE CSStuds SET dept="Language" 
WHERE id=1;Code language: SQL (Structured Query Language) (sql)

Here, the first update query will update the record which is not visible in the view data. Whereas, the second query will update the record which is visible in the view data but it will change the department which will cause the where condition to become false.

Let’s execute both queries and see the output.

Here, the first query didn’t return any error, but it didn’t update the record too. On the other hand, the second query returned an error ‘check option failed’.

Conclusion

In this tutorial, we have learned about the WITH CHECK OPTION clause which is used with the views to apply the check constraint for the insert and update operations on the view. It is a quite useful feature that you can learn and use in your projects.

References

MySQL official documentation on the VIEW CHECK OPTION.