How does mysql ensure the consistency of views 01/29 Update SLTechnology News&Howtos

How does mysql ensure the consistency of views

2025-01-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article mainly shows you "mysql how to ensure the consistency of views", the content is easy to understand, clear, hope to help you solve doubts, the following let the editor lead you to study and learn "mysql how to ensure the consistency of views" this article.

The details are as follows:

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

CREATE OR REPLACE VIEW view_name AS select_statement WITH CHECK OPTION

Note that the semicolon (;) is placed at the end of the WITH CHECK OPTION clause rather than at the end of the select statement to define the view. When we're done, let's try to create a view named vps based on the employees table to show 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 use the following statement to query the number from the vps view:

Because vps is a simple view, it is updatable, 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.com, 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 VP employees are exposed through vps views, not other employees, so to ensure view consistency, users can only display or update data that is visible through the view, and 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 should note that we add the WITH CHECK OPTION clause at the end of the CREATE OR REPLACE statement, and once again insert a row into the employees table through the vps view, as shown below:

INSERT INTO vps (employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reportsTo) VALUES (1704 Staff','x9112','johnminsu@yiibai.com',1,1703)

At this point, mysql refuses to insert and sends the following error message:

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

We can insert an employee whose position is 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 Marketing','x9112','johnminsu@classicmodelcars.com',1,1076)

Mysql issues 1 row affected (Query OK, 1 row affected), and we can verify the insert operation again by querying the data against the vps view:

SELECT * FROM vps

As the query results above show, it does work as expected:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.