Updatable Views and WITH CHECK OPTION

Some views can be changed: INSERT, UPDATE and DELETE on the view change the underlying table. MySQL calls them updatable views.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    category VARCHAR(20) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    cost DECIMAL(8,2) NOT NULL
);
INSERT INTO products (name, category, price, cost) VALUES
    ('Notebook', 'stationery', 80, 40), ('Gel Pen', 'stationery', 15.5, 6), ('Backpack', 'bags', 999, 520);

CREATE VIEW stationery AS
SELECT id, name, price FROM products WHERE category = 'stationery';

UPDATE stationery SET price = 85 WHERE name = 'Notebook';
SELECT id, name, price, category FROM products ORDER BY id;

1
2
3
4
5
6
7
8

+----+----------+--------+------------+
| id | name     | price  | category   |
+----+----------+--------+------------+
|  1 | Notebook |  85.00 | stationery |
|  2 | Gel Pen  |  15.50 | stationery |
|  3 | Backpack | 999.00 | bags       |
+----+----------+--------+------------+
3 rows in set

The update ran through the view and changed the table. The view hides category and cost, so users of the view cannot even see them.

When is a view updatable?

A view is updatable when each row of the view maps to exactly one row of one table. It is not updatable if its query contains:

• GROUP BY, HAVING, aggregate functions (SUM, COUNT…)
• DISTINCT
• UNION
• window functions
• a subquery in the select list
• a LIMIT
• certain joins (a join view can be updated, but only one underlying table per statement, with limits)
• literals and derived expressions can be read, but not written: you cannot update a column that is calculated.

1

SELECT table_name, is_updatable FROM information_schema.views WHERE table_schema = 'learn' ORDER BY table_name;

1
2
3
4
5
6

+------------+--------------+
| TABLE_NAME | IS_UPDATABLE |
+------------+--------------+
| stationery | YES          |
+------------+--------------+
1 row in set

INSERT through a view

An INSERT through the view works only if the columns missing from the view have a default or can be NULL. Our view omits category and cost, which are NOT NULL without default:

1

INSERT INTO stationery (name, price) VALUES ('Ruler', 20);

1

ERROR 1423 (HY000): Field of view 'learn.stationery' underlying table doesn't have a default value

That fails. A view that includes all required columns, or whose hidden columns have defaults, can accept inserts.

The problem: rows that leave the view

1
2

UPDATE products SET category = 'bags' WHERE name = 'Gel Pen';
SELECT name FROM stationery ORDER BY id;

1
2
3
4
5
6

+----------+
| name     |
+----------+
| Notebook |
+----------+
1 row in set

Changing a row so that it no longer matches the view’s WHERE removes it from the view. That is allowed when you update the table directly. But through the view it can be surprising: an update through the view can make the row vanish.

WITH CHECK OPTION

WITH CHECK OPTION makes MySQL refuse any insert or update through the view whose result would not be visible in the view:

1
2
3
4
5
6
7
8
9

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, department VARCHAR(20) NOT NULL, salary INT NOT NULL);
INSERT INTO employees (name, department, salary) VALUES ('Asha', 'engineering', 90000), ('Ben', 'sales', 60000);

CREATE VIEW engineering_staff AS
SELECT id, name, department, salary FROM employees WHERE department = 'engineering'
WITH CHECK OPTION;

UPDATE engineering_staff SET salary = 95000 WHERE name = 'Asha';
UPDATE engineering_staff SET department = 'sales' WHERE name = 'Asha';

1

ERROR 1369 (HY000): CHECK OPTION failed 'learn.engineering_staff'

The first update is fine. The second would move Asha out of the view, so MySQL refuses it with error 1369 (“CHECK OPTION failed”). The same applies to inserts:

1
2
3

INSERT INTO engineering_staff (name, department, salary) VALUES ('Dev', 'sales', 55000);
INSERT INTO engineering_staff (name, department, salary) VALUES ('Dev', 'engineering', 55000);
SELECT name, department FROM employees ORDER BY id;

1
2
3
4
5
6
7
8
9
10

ERROR 1369 (HY000): CHECK OPTION failed 'learn.engineering_staff'

+------+-------------+
| name | department  |
+------+-------------+
| Asha | engineering |
| Ben  | sales       |
| Dev  | engineering |
+------+-------------+
3 rows in set

A security view that cannot be bypassed

A view with WITH CHECK OPTION is a neat way to give a department head the right to edit only their department’s rows, and not to move rows elsewhere. Combine it with privileges: grant SELECT, INSERT, UPDATE, DELETE on the view, and nothing on the table.

CASCADED vs LOCAL

For views built on other views:

• CASCADED (default): the check applies to this view and every underlying view.
• LOCAL: only the conditions of this view are checked (and the underlying views’ conditions only if they also have a check option).

DELETE through a view

1
2

DELETE FROM engineering_staff WHERE name = 'Asha';
SELECT name FROM employees ORDER BY id;

1
2
3
4
5
6
7

+------+
| name |
+------+
| Ben  |
| Dev  |
+------+
2 rows in set

A DELETE removes the underlying row, but only rows visible in the view can be deleted through it.

Rules and limits

• A view over several tables (joins) can be updated only one table per statement, and only if the view is built with MERGE.
• You cannot INSERT into a join view in many cases.
• Triggers on the base tables fire normally.
• Foreign keys and constraints of the base tables still apply.
• INSERT ... ON DUPLICATE KEY UPDATE through a view has restrictions.

When to use updatable views

They are useful for restricted access and for compatibility layers. For general application code, writing to the table directly is simpler and clearer. Many teams use views for reading only, and keep all writes explicit.