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

Detailed explanation of JavaScript BOM composition and common events

Table of contents 1. BOM 2. Composition of BOM 2....

How to safely shut down MySQL

When shutting down the MySQL server, various prob...

IE8 uses multi-compatibility mode to display web pages normally

IE8 will have multiple compatibility modes . IE pl...

Detailed explanation of nginx front-end distribution method based on $remote_addr

The requirements are as follows: There are multip...

Semantics: Is Html/Xhtml really standards-compliant?

<br />Original text: http://jorux.com/archiv...

Detailed explanation of the idea of ​​setting up login verification interception function in Vue

Table of contents 1. Solution 2. Let the browser ...

Use of TypeScript Generics

Table of contents 1. Easy to use 2. Using generic...

Improve the efficiency of page code modification and master the core knowledge of HTML language

When building a website, HTML language may seem un...

Use Visual Studio Code to connect to the MySql database and query

Visual Studio Code is a powerful text editor prod...

How to change the color of the entire row (tr) when the mouse stops in HTML

Use pure CSS to change the background color of a ...

vue-cropper plug-in realizes the encapsulation of image capture and upload component

The specific code for encapsulating the image cap...

How to deploy FastDFS in Docker

Install fastdfs on Docker Mount directory -v /e/f...

Vue3 encapsulates the side navigation text skeleton effect component

Vue3 project encapsulation side navigation text s...

Detailed example of IOS database upgrade data migration

Detailed example of IOS database upgrade data mig...

How to use SessionStorage and LocalStorage in Javascript

Table of contents Preface Introduction to Session...