Views (Dynamic vs. Materialized) & WITH CHECK OPTION

A named, stored query

A view is a SELECT query stored under a name and used like a table. It is a *virtual* table: the rows aren't stored, only the definition is. Views earn their keep three ways — they simplify complex multi-table queries (write the join once, reference it by name), provide security (expose only certain columns/rows, hiding sensitive ones), and give physical data independence (rebuild the base tables, redefine the view once, and all the programs using it keep working).

Dynamic vs. materialized

- A dynamic view stores only its definition. Each time you reference it, the DBMS re-runs the query against the current base tables — so it is always current, costs almost no storage, but pays the query cost on every reference. (Plain CREATE VIEW in Postgres.) - A materialized view stores the computed result as a physical table that must be refreshed to stay in sync. It trades storage and possible staleness for fast repeated reads — ideal for expensive aggregations in a data warehouse.

Worked example: a view that hides a sensitive column

Suppose front-desk staff may see who the honours students are, but not their exact GPA values beyond the threshold. Define a view exposing only the safe columns:

CREATE VIEW honors_student AS
SELECT student_id, name, gpa
FROM   students
WHERE  gpa >= 3.5;

SELECT name, gpa FROM honors_student ORDER BY gpa DESC;

Grant access to honors_student instead of students, and the user can never reach the rows or columns the view leaves out. Querying the view re-runs its SELECT against the live students table, so it always reflects current GPAs.

When a view is NOT updatable

An UPDATE/INSERT/DELETE through a view must map unambiguously back to exactly one base-table row. It can't when the view:

1. uses DISTINCT,
2. contains an expression, aggregate, or statistical function in the SELECT,
3. references more than one table (a join, subquery, or UNION),
4. is built on another non-updatable view, or
5. has a GROUP BY or HAVING clause.

In each case the DBMS can't tell which base row(s) a view-level change corresponds to (how do you update an average?), so it rejects the update.

WITH CHECK OPTION

WITH CHECK OPTION makes the DBMS reject any insert/update through the view that would make the row vanish from the view. If ab_students shows only state = 'AB' students WITH CHECK OPTION, an update that sets a row's state to 'BC' is blocked — that row would drop out of the view, which the option forbids:

CREATE VIEW ab_students AS
SELECT student_id, name, state
FROM   students
WHERE  state = 'AB'
WITH CHECK OPTION;