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 | mary.hill@yiibai.com | 1 | 1002 |
| 1076 | Firrelli | Jeff | VP Marketing | x9273 | jfirrelli@yiibai.com | 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','lilybush@yiiibai.com',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 | lilybush@yiiibai.com | 1 | 1002 | IT Manager |
| 1702 | Gerard | Martin | x2312 | mgerard@gmail.com | 4 | 1102 | Sales Rep |
| 1625 | Kato | Yoshimi | x102 | ykato@gmail.com | 5 | 1621 | Sales Rep |
| 1621 | Nishi | Mami | x101 | mnishi@gmail.com | 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','johnminsu@yiibai.com',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','johnminsu@classicmodelcars.com',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 | mary.hill@yiibai.com | 1 | 1002 |
| 1076 | Firrelli | Jeff | VP Marketing | x9273 | jfirrelli@yiibai.com | 1 | 1002 |
| 1704 | Minsu | John | SVP Marketing | x9112 | johnminsu@classicmodelcars.com | 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

MySQL 5.7.29 + Win64 decompression version installation tutorial with pictures and text

Download the official website Choose the version ...

React Native scaffolding basic usage detailed explanation

Build the project Execute the command line in the...

Nginx reverse proxy configuration to remove prefix case tutorial

When using nginx as a reverse proxy, you can simp...

Best Practices Guide for MySQL Partitioned Tables

Preface: Partitioning is a table design pattern. ...

How to install and configure WSL on Windows

What is WSL Quoting a passage from Baidu Encyclop...

Linux file management command example analysis [display, view, statistics, etc.]

This article describes the Linux file management ...

How many pixels should a web page be designed in?

Many web designers are confused about the width of...

Flex layout allows subitems to maintain their own height

When using Flex layout, you will find that when a...

Detailed explanation of Vue-Jest automated testing basic configuration

Table of contents Install Configuration Common Mi...

MySQL performance optimization tips

MySQL Performance Optimization MySQL is widely us...

Detailed explanation of template tag usage (including summary of usage in Vue)

Table of contents 1. Template tag in HTML5 2. Pro...

Detailed explanation of Vue's commonly used built-in instructions

<body> <div id="root"> <...

Comparison of the advantages of vue3 and vue2

Table of contents Advantage 1: Optimization of di...

After idea publishes web project, Tomcat server cannot find the project and its solution

Overview The project was created successfully and...

Html Select option How to make the default selection

Adding the attribute selected = "selected&quo...