Updatable Views, Dropping Views and Views vs Tables

Concept

Some views are updatable: an INSERT, UPDATE or DELETE against the view is translated into the same operation on the underlying table. It works only when the engine can map each view row back to exactly one table row unambiguously.

When a view is updatable

Example

CREATE OR REPLACE VIEW v_active_employees AS
SELECT id, first_name, last_name, dept_id, salary, status
FROM   employees
WHERE  status = 'active';

-- Both work: the view maps one to one onto employees
UPDATE v_active_employees SET salary = salary * 1.05 WHERE dept_id = 10;
DELETE FROM v_active_employees WHERE id = 7;

WITH CHECK OPTION

-- Without the option: a row can be updated straight out of the view
UPDATE v_active_employees SET status = 'inactive' WHERE id = 3;
-- The row still exists in employees, but has vanished from the view.

CREATE OR REPLACE VIEW v_active_employees_checked AS
SELECT id, first_name, last_name, dept_id, salary, status
FROM   employees
WHERE  status = 'active'
WITH CHECK OPTION;

-- Now rejected: the change would violate the view's own WHERE clause
UPDATE v_active_employees_checked SET status = 'inactive' WHERE id = 3;

WITH CHECK OPTION makes the view's WHERE clause a constraint on writes as well as a filter on reads. It is the mechanism behind row level access control built from views: give a user a view filtered to their own region, add the check option, and they cannot insert or move a row into someone else's region.

Views, tables and materialised views

-- PostgreSQL
CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT order_date, COUNT(*) AS orders, SUM(total) AS revenue
FROM   orders GROUP BY order_date;

REFRESH MATERIALIZED VIEW mv_daily_revenue;

-- The MySQL equivalent: a real table plus a scheduled rebuild
CREATE TABLE daily_revenue (
    order_date DATE PRIMARY KEY,
    orders     INT           NOT NULL,
    revenue    DECIMAL(12,2) NOT NULL
);

REPLACE INTO daily_revenue (order_date, orders, revenue)
SELECT order_date, COUNT(*), SUM(total) FROM orders GROUP BY order_date;

Dropping and changing views

DROP VIEW IF EXISTS v_active_employees;

-- Inspect a view definition
SHOW CREATE VIEW v_order_summary;                                   -- MySQL
SELECT view_definition FROM information_schema.views
WHERE  table_name = 'v_order_summary';                              -- standard

Important rules

• A view is updatable only when each of its rows maps to exactly one base table row.
• Aggregation, DISTINCT and UNION always make a view read only.
• WITH CHECK OPTION prevents writes that would push a row out of the view.
• Inserting through a view fails if the base table has a NOT NULL column the view does not expose.
• MySQL and MariaDB have no materialised views - use a summary table plus a scheduled refresh.
• Dropping a base table does not drop dependent views; they simply break.

Common mistakes

• Trying to update an aggregate view and misreading the error as a permissions problem.
• Omitting WITH CHECK OPTION from a view used for access control, so users can write rows they cannot then see.
• Inserting through a view that hides a mandatory column.
• Expecting CREATE MATERIALIZED VIEW to exist on MySQL.
• Leaving broken views behind after a table is dropped or renamed.

Best practices

• Treat views as read interfaces by default; write to base tables from application code.
• Always add WITH CHECK OPTION to a view that filters rows for security.
• Keep view definitions in migration files so they are version controlled and reviewable.
• Where a heavy view is read constantly, build a refreshed summary table and index it.
• Audit for broken views after any schema change.

Practice

1. Create an updatable view of shipped orders and add WITH CHECK OPTION. Show what it now refuses.
2. Explain why a view containing GROUP BY can never be updatable.
3. Design the MySQL replacement for a materialised view of monthly revenue, including how it is refreshed.