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

VMware pro15 installation macOS10.13 detailed installation diagram (picture and text)

The editor recently wanted to get started with th...

How to use JavaScript to determine several common browsers through userAgent

Preface Usually when making h5 pages, you need to...

Introduction to commonly used MySQL commands in Linux environment

Enter the mysql command: mysql -u+(user name) -p+...

A brief analysis of the problem of Mysql 8.0 version driving getTables to return all database tables

Preface This article mainly introduces the releva...

Tutorial on how to quickly deploy clickhouse using docker-compose

ClickHouse is an open source column-oriented DBMS...

Vue implements the shake function (compatible with ios13.3 and above)

Recently, I made a function similar to shake, usi...

Vue implements adding, displaying and deleting multiple images

This article shares the specific code for Vue to ...

How to implement form validation in Vue

1. Installation and use First, install it in your...

Detailed explanation of Nginx reverse proxy example

1. Reverse proxy example 1 1. Achieve the effect ...

Javascript Virtual DOM Detailed Explanation

Table of contents What is virtual dom? Why do we ...

Sublime / vscode quick implementation of generating HTML code

Table of contents Basic HTML structure Generate s...

Linux lossless expansion method

Overview The cloud platform customer's server...

In-depth understanding of the creation and implementation of servlets in tomcat

1. What is a servlet 1.1. Explain in official wor...

ffmpeg Chinese parameter description and usage examples

1. When ffmpeg pushes video files, the encoding f...

Common errors and solutions for connecting Navicat to virtual machine MySQL

Question 1 solve Start the service: service mysql...