{"id":4175,"date":"2015-11-14T22:56:12","date_gmt":"2015-11-15T06:56:12","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=4175"},"modified":"2024-01-04T20:06:20","modified_gmt":"2024-01-05T03:06:20","slug":"mysql-view-with-check-option","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-views\/mysql-view-with-check-option\/","title":{"rendered":"MySQL WITH CHECK OPTION Clause"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to ensure consistency of the database views using the WITH CHECK OPTION<\/code> clause.<\/p>\n\n\n\n

Introduction to WITH CHECK OPTION clause<\/h2>\n\n\n\n

Sometimes, you\u00a0create a view<\/a> to reveal the partial data of a table. However, a simple view is updatable<\/a>, and therefore, it is possible to update<\/a> data that is not visible through the view. This update\u00a0makes the view inconsistent. <\/p>\n\n\n\n

To ensure the consistency of the view, you use the WITH CHECK OPTION<\/code> clause when you create or modify the view<\/a>.<\/p>\n\n\n\n

The WITH CHECK OPTION<\/code> is an optional clause of the CREATE VIEW<\/code> statement. This WITH CHECK OPTION<\/code> prevents you from updating<\/a> or inserting<\/a> rows that are not visible through the view. <\/p>\n\n\n\n

In other words, whenever you update or insert a row of the base tables through a view, MySQL ensures that the insert or update operation conforms with the definition of the view.<\/p>\n\n\n\n

The following illustrates the syntax of the WITH CHECK OPTION<\/code> clause:<\/p>\n\n\n

CREATE<\/span> OR<\/span> REPLACE<\/span> VIEW<\/span> view_name \nAS<\/span>\n  select_statement\nWITH<\/span> CHECK<\/span> OPTION<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you add the WITH CHECK OPTION<\/code> clause at the end of the CREATE VIEW<\/code> statement.<\/p>\n\n\n\n
MySQL WITH CHECK OPTION example<\/h2>\n\n\n\n
Let’s explore an example of using the WITH CHECK OPTION<\/code> clause.<\/p>\n\n\n\n
First, create a table called contacts:<\/p>\n\n\n
CREATE DATABASE mydb;\n\nUSE<\/span> mydb<\/span>;\n\nCREATE TABLE employees(\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    type VARCHAR(50<\/span>) NOT NULL<\/span>,\n    name VARCHAR(255<\/span>) NOT NULL<\/span>\n);\n\nINSERT INTO employees (type, name) \nVALUES\r\n('Full-time'<\/span>, 'John Doe'<\/span>),\r\n('Contractor'<\/span>, 'Jane Smith'<\/span>),\r\n('Temp'<\/span>, 'Alice Johnson'<\/span>),\r\n('Full-time'<\/span>, 'Bob Anderson'<\/span>),\r\n('Contractor'<\/span>, 'Charlie Brown'<\/span>),\r\n('Temp'<\/span>, 'David Lee'<\/span>),\r\n('Full-time'<\/span>, 'Eva Martinez'<\/span>),\r\n('Contractor'<\/span>, 'Frank White'<\/span>),\r\n('Temp'<\/span>, 'Grace Taylor'<\/span>),\r\n('Full-time'<\/span>, 'Henry Walker'<\/span>),\r\n('Contractor'<\/span>, 'Ivy Davis'<\/span>),\r\n('Temp'<\/span>, 'Jack Turner'<\/span>),\r\n('Full-time'<\/span>, 'Kelly Harris'<\/span>),\r\n('Contractor'<\/span>, 'Leo Wilson'<\/span>),\r\n('Temp'<\/span>, 'Mia Rodriguez'<\/span>),\r\n('Full-time'<\/span>, 'Nick Carter'<\/span>),\r\n('Contractor'<\/span>, 'Olivia Clark'<\/span>),\r\n('Temp'<\/span>, 'Pauline Hall'<\/span>),\r\n('Full-time'<\/span>, 'Quincy Adams'<\/span>);\r\n\nSELECT * FROM employees;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----+------------+---------------+\r\n| id | type       | name          |\r\n+----+------------+---------------+\r\n|  1<\/span> | Full-time  | John Doe      |\r\n|  2<\/span> | Contractor | Jane Smith    |\r\n|  3<\/span> | Temp       | Alice Johnson |\r\n|  4<\/span> | Full-time  | Bob Anderson  |\r\n|  5<\/span> | Contractor | Charlie Brown |\r\n|  6<\/span> | Temp       | David Lee     |\r\n|  7<\/span> | Full-time  | Eva Martinez  |\r\n|  8<\/span> | Contractor | Frank White   |\r\n|  9<\/span> | Temp       | Grace Taylor  |\r\n| 10<\/span> | Full-time  | Henry Walker  |\r\n| 11<\/span> | Contractor | Ivy Davis     |\r\n| 12<\/span> | Temp       | Jack Turner   |\r\n| 13<\/span> | Full-time  | Kelly Harris  |\r\n| 14<\/span> | Contractor | Leo Wilson    |\r\n| 15<\/span> | Temp       | Mia Rodriguez |\r\n| 16<\/span> | Full-time  | Nick Carter   |\r\n| 17<\/span> | Contractor | Olivia Clark  |\r\n| 18<\/span> | Temp       | Pauline Hall  |\r\n| 19<\/span> | Full-time  | Quincy Adams  |\r\n+----+------------+---------------+\r\n19<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create a view called contractors based on the employees<\/code> table:<\/p>\n\n\n
CREATE OR<\/span> REPLACE VIEW contractors \nAS<\/span> \nSELECT id, type, name \nFROM \n  employees \nWHERE \n  type = 'Contractor'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, query data from the contractors<\/code> view:<\/p>\n\n\n
SELECT * FROM contractors;<\/code><\/span><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----+------------+---------------+\r\n| id | type       | name          |\r\n+----+------------+---------------+\r\n|  2<\/span> | Contractor | Jane Smith    |\r\n|  5<\/span> | Contractor | Charlie Brown |\r\n|  8<\/span> | Contractor | Frank White   |\r\n| 11<\/span> | Contractor | Ivy Davis     |\r\n| 14<\/span> | Contractor | Leo Wilson    |\r\n| 17<\/span> | Contractor | Olivia Clark  |\r\n+----+------------+---------------+\r\n6<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT INTO contractors(name, type)\nVALUES('Andy Black'<\/span>, 'Contractor'<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Query<\/span> OK<\/span>, 1 row<\/span> affected<\/span> (0.00<\/span> sec<\/span>)<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
The statement inserts a new employee with the type Contractor<\/code> into the employees<\/code> table via the contractors<\/code> view.<\/p>\n\n\n\n
Fourth, retrieve data from the contractors<\/code> view:<\/p>\n\n\n
+----+------------+---------------+\r\n| id | type       | name          |\r\n+----+------------+---------------+\r\n|  2<\/span> | Contractor | Jane Smith    |\r\n|  5<\/span> | Contractor | Charlie Brown |\r\n|  8<\/span> | Contractor | Frank White   |\r\n| 11<\/span> | Contractor | Ivy Davis     |\r\n| 14<\/span> | Contractor | Leo Wilson    |\r\n| 17<\/span> | Contractor | Olivia Clark  |\r\n| 20<\/span> | Contractor | Andy Black    |\r\n+----+------------+---------------+\r\n7<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The output shows that Andy Black<\/code> has been added successfully.<\/p>\n\n\n\n
The problem is that you can add an employee with other types such as Full-time<\/code> into the employees<\/code> table via the contractors<\/code> view. For example:<\/p>\n\n\n
INSERT INTO contractors(name, type)\nVALUES('Deric Seetoh'<\/span>, 'Full-time'<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Query<\/span> OK<\/span>, 1 row<\/span> affected<\/span> (0.00<\/span> sec<\/span>)<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
This statement successfully inserts a new row that is not visible via the contractors<\/code> view into the employees<\/code> table.<\/p>\n\n\n\n
To prevent this, you need to add the WITH CHECK OPTION<\/code> clause to the CREATE OR REPLACE VIEW<\/code> statement like this:<\/p>\n\n\n
CREATE OR<\/span> REPLACE VIEW contractors \nAS<\/span> \nSELECT id, type, name \nFROM \n  employees \nWHERE \n  type = 'Contractor'<\/span>\nWITH CHECK OPTION;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
If you attempt to insert or update rows that are not visible via the contractor, you’ll get an error. For example:<\/p>\n\n\n
INSERT INTO contractors(name, type)\nVALUES('Brad Knox'<\/span>, 'Full-time'<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
ERROR 1369<\/span> (HY000): CHECK OPTION failed 'mydb.contractors'<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Because of the WITH CHECK OPTION<\/code>, MySQL checks if the INSERT<\/code> statement conforms with the SELECT statement that defines the view. If the INSERT<\/code> statement does not conform, MySQL rejects it and issues an error.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Include the WITH CHECK OPTION<\/code> clause in the CREATE VIEW<\/code> statement to ensure the consistency of the view.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
The contractors<\/code> view is updatable<\/a>. For example, you can insert a new row into the employees<\/code> table via the contractor view:<\/p>\n\n\n