A view can be used for inserting or updating. However, there are certain limitations.
A view cannot be used for updating if it uses any of the following:
A view cannot be used for inserting if it fails any of the criteria for updating, and must also meet the following conditions:
MariaDB stores an IS_UPDATABLE flag with each view, so it is always possible to see if MariaDB considers a view updatable (although not necessarily insertable) by querying the IS_UPDATABLE column in the INFORMATION_SCHEMA.VIEWS table.
The WITH CHECK OPTION clause is used to prevent updates or inserts to views unless the WHERE clause in the SELECT statement is true.
There are two keywords that can be applied. WITH LOCAL CHECK OPTION restricts the CHECK OPTION to only the view being defined, while WITH CASCADED CHECK OPTION checks all underlying views as well. CASCADED is treated as default if neither keyword is given.
If a row is rejected because of the CHECK OPTION, an error similar to the following is produced:
ERROR 1369 (HY000): CHECK OPTION failed 'db_name.view_name'
A view with a WHERE which is always false (like
WHERE 0) and WITH CHECK OPTION is similar to a BLACKHOLE table: no row is ever inserted and no row is ever returned. An insertable view with a WHERE which is always false but no CHECK OPTION is a view that accepts data but does not show them.
CREATE TABLE table1 (x INT); CREATE VIEW view1 AS SELECT x, 99 AS y FROM table1;
Checking whether the view is updateable:
SELECT TABLE_NAME,IS_UPDATABLE FROM INFORMATION_SCHEMA.VIEWS; +------------+--------------+ | TABLE_NAME | IS_UPDATABLE | +------------+--------------+ | view1 | YES | +------------+--------------+
This query works, as the view is updateable:
UPDATE view1 SET x = 5;
This query fails, since column
y is a literal.
UPDATE view1 SET y = 5; ERROR 1348 (HY000): Column 'y' is not updatable
Here are three views to demonstrate the WITH CHECK OPTION clause.
CREATE VIEW view_check1 AS SELECT * FROM table1 WHERE x < 100 WITH CHECK OPTION; CREATE VIEW view_check2 AS SELECT * FROM view_check1 WHERE x > 10 WITH LOCAL CHECK OPTION; CREATE VIEW view_check3 AS SELECT * FROM view_check1 WHERE x > 10 WITH CASCADED CHECK OPTION;
This insert succeeds, as
view_check2 only checks the insert against
view_check2, and the WHERE clause evaluates to true (
INSERT INTO view_check2 VALUES (150);
This insert fails, as
view_check3 checks the insert against both
view_check3 and the underlying views. The WHERE clause for
view_check1 evaluates as false (
150 is not
<100), so the insert fails.
INSERT INTO view_check3 VALUES (150); ERROR 1369 (HY000): CHECK OPTION failed 'test.view_check3'
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.