Detailed explanation of ensuring the consistency of MySQL views (with check option)

Detailed explanation of ensuring the consistency of MySQL views (with check option)

This article uses an example to illustrate how to ensure the consistency of MySQL views (with check option). Share with you for your reference, the details are as follows:

Sometimes we create a view to display part of the data in a table. We know that simple views are transparent, so data that is not visible through the view can be updated, but this update will make the view inconsistent. To ensure the consistency of a view, use the WITH CHECK OPTION updatable clause when creating or modifying a view. Let's look at the grammatical structure of the WITH CHECK OPTION updatable clause:

CREATE OR REPLACE VIEW view_name 
AS
 select_statement
 WITH CHECK OPTION;

We should note that we put the semicolon (;) at the end of the WITH CHECK OPTION clause instead of at the end of the select statement to define the view. Now let's try to create a view named vps based on the employees table to display employees whose position is VP, such as VP Marketing and VP Sales:

CREATE OR REPLACE VIEW vps AS
  SELECT 
    employeeNumber,
    lastname,
    firstname,
    jobtitle,
    extension,
    email,
    officeCode,
    reportsTo
  FROM
    employees
  WHERE
    jobTitle LIKE '%VP%';

Next, we query the data from the vps view using the following statement:

mysql> SELECT * FROM vps;
+----------------+----------+-----------+---------------+-----------+----------------------+------------+-----------+
| employeeNumber | lastname | firstname | jobtitle | extension | email | officeCode | reportsTo |
+----------------+----------+-----------+---------------+-----------+----------------------+------------+-----------+
| 1056 | Hill | Mary | VP Sales | x4611 | [email protected] | 1 | 1002 |
| 1076 | Firrelli | Jeff | VP Marketing | x9273 | [email protected] | 1 | 1002 |
+----------------+----------+-----------+---------------+-----------+----------------------+------------+-----------+
2 rows in set

Because vps is a simple view, it is updateable, so we insert a row of employee data information through the vps view:

INSERT INTO vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reportsTo)
values(1703,'Lily','Bush','IT Manager','x9111','[email protected]',1,1002);

We should note that the newly created employee is not visible through the vps view because her position is IT Manager, not VP. Use the following SELECT statement to verify it:

SELECT * FROM employees WHERE employeeNumber=1703;

Execute the above statement and get the following results:

+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
| employeeNumber | lastName | firstName | extension | email | officeCode | reportsTo | jobTitle |
+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
| 1703 | Bush | Lily | x9111 | [email protected] | 1 | 1002 | IT Manager |
| 1702 | Gerard | Martin | x2312 | [email protected] | 4 | 1102 | Sales Rep |
| 1625 | Kato | Yoshimi | x102 | [email protected] | 5 | 1621 | Sales Rep |
| 1621 | Nishi | Mami | x101 | [email protected] | 5 | 1056 | Sales Rep |

But this may not be what we want, because the vps view exposes VP employees, not other employees. Therefore, to ensure the consistency of the view, users can only display or update data visible through the view. Use WITH CHECK OPTION when creating or modifying the view:

CREATE OR REPLACE VIEW vps AS
  SELECT 
    employeeNumber,
    lastname,
    firstname,
    jobtitle,
    extension,
    email,
    officeCode,
    reportsTo
  FROM
    employees
  WHERE
    jobTitle LIKE '%VP%' 
WITH CHECK OPTION;

We must pay attention to add the WITH CHECK OPTION clause at the end of the CREATE OR REPLACE statement. After that, insert a row into the employees table through the vps view again, as shown below:

INSERT INTO vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reportsTo)
VALUES(1704,'John','Minsu','IT Staff','x9112','[email protected]',1,1703);

At this point MySQL will reject the insert and issue the following error message:

Error Code: 1369 - CHECK OPTION failed 'luyaran.vps'

We can insert an employee with the position of SVP Marketing into the employees table through the vps view to see if MySQL allows this:

INSERT INTO vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reportsTo)
VALUES(1704,'John','Minsu','SVP Marketing','x9112','[email protected]',1,1076);

MySQL issues 1 row affected (Query OK, 1 row affected). We can verify the insert operation again by querying the data according to the vps view:

SELECT * FROM vps;

As the query above shows, it works as expected:

mysql> SELECT * FROM vps;
+----------------+----------+-----------+---------------+-----------+--------------------------------+------------+-----------+
| employeeNumber | lastname | firstname | jobtitle | extension | email | officeCode | reportsTo |
+----------------+----------+-----------+---------------+-----------+--------------------------------+------------+-----------+
| 1056 | Hill | Mary | VP Sales | x4611 | [email protected] | 1 | 1002 |
| 1076 | Firrelli | Jeff | VP Marketing | x9273 | [email protected] | 1 | 1002 |
| 1704 | Minsu | John | SVP Marketing | x9112 | [email protected] | 1 | 1076 |
+----------------+----------+-----------+---------------+-----------+--------------------------------+------------+-----------+
3 rows in set

Okay, that’s all for this record.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:
  • MySql8 WITH RECURSIVE recursive query parent-child collection method
  • How to solve the error "A Windows service with the name MySQL already exists." when installing MySQL
  • TIMESTAMP with implicit DEFAULT value is deprecated error in MySQL 5.6
  • Solution to MySQL error TIMESTAMP column with CURRENT_TIMESTAMP
  • MySQL Tips: Solution to the problem of server quit without updating PID file
  • PHP SQL Injection with MySQL
  • MySQL 8.0 WITH query details

<<:  Using Nginx to implement grayscale release

>>:  JavaScript navigator.userAgent obtains browser information case explanation

Recommend

How to clear floating example code in css

Overview The framework diagram of this article is...

The normal method of MySQL deadlock check processing

Normally, when a deadlock occurs, the connection ...

How to connect XShell and network configuration in CentOS7

1. Linux network configuration Before configuring...

Introduction to Nginx regular expression related parameters and rules

Preface Recently, I have been helping clients con...

Simple method to install mysql under linux

When searching online for methods to install MySQ...

My CSS framework - base.css (reset browser default style)

Copy code The code is as follows: @charset "...

Detailed tutorial on installing MySQL 5.7.19 decompressed version on Windows Server 2016

The installation tutorial of MySQL 5.7.19 winx64 ...

Understanding innerHTML

<br />Related articles: innerHTML HTML DOM i...

Html comments Symbols for marking text comments in Html

HTML comments, we often need to make some HTML co...

Detailed examples of variable and function promotion in JavaScript

js execution Lexical analysis phase: includes thr...

Interpretation of Vue component registration method

Table of contents Overview 1. Global Registration...

HTML basics HTML structure

What is an HTML file? HTML stands for Hyper Text M...

How to use uni-app to display buttons and search boxes in the top navigation bar

Recently, the company is preparing to develop an ...

How to install WSL2 Ubuntu20.04 on Windows 10 and set up the docker environment

Enable WSL Make sure the system is Windows 10 200...

Steps to install MySQL 5.7 in binary mode and optimize the system under Linux

This article mainly introduces the installation/st...