Views — creating, updatable views: Question bank

For the deeper machinery behind these, see SQL SELECT and JOINs, Materialized Views, Database Security and GRANT, Triggers, and Query Optimization and View Merging.

The core picture: one-to-one vs many-to-one

The single idea behind every updatability rule is a mapping — an arrow from each view row back to the base row(s) it came from. The figure below shows the two shapes this arrow can take.

Figure — Views — creating, updatable views

Look at the left panel: a plain filtering view. Each green view row has exactly one blue arrow to a single base row — a one-to-one map. You can invert the arrow, so writes translate cleanly. The right panel: an aggregating view. Many blue base rows funnel into one orange summary row — a many-to-one map. There is no single arrow to follow backwards, so the engine cannot know which base row your edit means. That is why aggregation kills updatability.

The next figure shows the same collapse happening for the three other "killers" — DISTINCT, multi-table JOIN, and UNION — so you can see why each breaks the arrow.

Refer back to these two pictures whenever an answer below says "the mapping is undefined."

True or false — justify

A standard view stores its own copy of the query result.

False — it stores only the query text. The rows are recomputed from the base tables on every access, which is why a view uses no data storage and never goes stale.

A view is always in sync with its base tables.

True for a standard view — because it re-runs the SELECT each time, any change to base data is reflected instantly. A materialized view is the exception: it can be stale until refreshed.

Querying a view is always faster than writing the raw query.

False — a standard view re-executes the same underlying query plus a parse/rewrite step, so it is at best equal and can be slightly slower. Speed comes only from a materialized view, at the cost of storage and refresh.

A view can hide columns from a user who only has access to the view.

True — if the view's SELECT never lists salary, a user granted SELECT on the view cannot reference salary at all; the column simply does not exist in the view's schema. This is the security use-case.

Every SELECT you can write can be turned into an updatable view.

False — a view is updatable only when each view row maps back to exactly one base-table row and each changed column is a real base column. Aggregation, GROUP BY, DISTINCT, UNION and multi-table joins break that mapping.

WITH CHECK OPTION makes a view faster.

False — it has nothing to do with speed. It forces every INSERT/UPDATE through the view to keep satisfying the view's WHERE, blocking writes that would land outside the view.

Deleting a row through an updatable view leaves the base table untouched.

False — the view holds no data of its own, so a successful DELETE propagates straight to the base table and removes the real row.

A view with AVG(salary) and GROUP BY dept_id is read-only.

True — you cannot say which employee's salary to change (and to what) so that a group average equals a target; infinitely many base-row changes give the same average, so the mapping is undefined (right panel of the first figure).

All views containing a JOIN are strictly non-updatable in every database.

False — the standard rule warns against joins, but many engines allow updating a join view if you touch columns of only one base table and that table stays key-preserved (unique mapping). It is engine-dependent.

WITH CHECK OPTION prevents anyone from ever seeing a row outside the view.

False — it only guards writes made through the view. Rows inserted directly into the base table can still fall outside the view's WHERE; the option cannot police the base table.

A window function like ROW_NUMBER() OVER (...) in the SELECT list keeps the view updatable.

False — a window function computes each output from many base rows (its window frame), so that column has no single base source; window functions are non-updatable expressions, just like aggregates.

Spot the error

CREATE VIEW v AS SELECT price*1.1 AS marked FROM items; then UPDATE v SET marked = 50; — what breaks?

marked is an expression, not a plain base column, so it is not an updatable column. The engine can't invert price*1.1 = 50 unambiguously in general, so the UPDATE is rejected.

Given CREATE VIEW cheap_items AS SELECT item_id, name, price FROM items WHERE price < 100; (no check option), then INSERT INTO cheap_items VALUES (50,'Mug',150); — what's wrong?

The insert succeeds and writes to the base items table, but the new row has price = 150, violating price < 100, so it instantly vanishes from the view. Nothing errored — that silent disappearance is the trap. Add WITH CHECK OPTION to reject it.

"I granted SELECT on the base table to the intern so they can use my public view." — what did you leak?

Everything — granting on the base table exposes the hidden salary column too. Security via views works only if the intern is granted on the view, not the base table.

CREATE VIEW v AS SELECT DISTINCT dept_id FROM employees; then DELETE FROM v WHERE dept_id=3; — why can't the DB obey?

DISTINCT collapses many base rows into one view row, so "delete this one view row" maps to many base rows (see the second figure). The engine can't tell which employees you meant, so the view is non-updatable.

A UNION view A UNION B; you INSERT a row — where should it go?

Undefined — the engine cannot decide whether the row belongs to base table A, B, or both, so set-operation views are non-updatable. You'd need an INSTEAD OF trigger to specify the target.

Why questions

Why is a view called "virtual"?

Because it stores a formula, not the numbers — like a saved spreadsheet formula rather than a saved spreadsheet. The rows are computed on the fly from base tables each time you read it.

Why does an aggregate view break updatability, in one sentence?

Because aggregation is a many-to-one map (many base rows → one summary row), and no unique inverse exists to send your edit back to specific base rows.

Why grant SELECT on a view instead of on the base table?

The view is an access-control surface: it can expose only chosen rows and columns, so the user literally cannot name a column the view never selected.

Why can changing the schema break apps that read the base table but not apps that read a view?

The view is a stability layer — if the base columns are rearranged, you patch only the view's definition to keep its output identical, so callers of the view see no change.

Why does the optimiser "merge" a view into your query?

Because a standard view has no stored rows to read; the engine substitutes the view's definition into your query (view merging) and executes one combined plan — hence no storage cost but repeated computation.

Why is INSTEAD OF a trigger and not just a flag?

Because the engine cannot infer the base-table mapping for a complex (e.g. join) view, so you must supply the exact write logic in code — that logic runs instead of the impossible automatic translation.

Edge cases

A view whose SELECT has no WHERE at all — is it updatable?

Potentially yes — the absence of a filter does not break the one-to-one mapping. Updatability depends on aggregation/join/distinct, not on whether a WHERE exists.

An updatable view that omits a NOT NULL base column with no default — can you INSERT through it?

No — the base table would receive NULL for a required column and reject the row. The view exposes too few columns to build a valid base row, so inserts fail even though updates may work.

You UPDATE a row through a view with WITH CHECK OPTION so the row would leave the view's WHERE. Result?

Rejected — the check option applies to updates as well as inserts; any write that would push the row outside the view's predicate is blocked.

A view v2 is built on top of a filtering view v1, and only v2 has WITH LOCAL CHECK OPTION. A write through v2 satisfies v2's WHERE but violates v1's. Allowed?

Allowed — LOCAL checks only the predicates of the view where the option is declared (v2), ignoring underlying views. The row can silently fall outside v1.

Same stack, but v2 has WITH CASCADED CHECK OPTION. Same write — allowed?

Rejected — CASCADED checks the predicate of v2 and every underlying view (v1 too). The write must satisfy all layers, so violating v1 blocks it. CASCADED is the SQL default when neither word is given.

A materialized view vs a standard view when base data changes right now — which reflects it?

The standard view reflects it immediately (recomputed on read); the materialized view shows the last refreshed snapshot and stays stale until you refresh it.

Can a single-table view that selects a strict subset of columns still be deleted from?

Yes — column projection alone keeps the row mapping one-to-one, so DELETE and UPDATE (on the exposed columns) translate cleanly to the base table.

Is a view that selects every column of one table via SELECT * updatable?

Yes — full projection of one base table with no aggregation, distinct, join or set operation preserves the exact one-to-one row and column mapping, so all writes propagate.

Recall One-line self-test

If you can point at exactly one base row for each view row, and every column you write is a real base column, the write goes through — otherwise the engine has no unique answer and refuses. ::: This single principle explains all the updatability rules above.