Views — creating, updatable views: Visual walkthrough

This is the visual companion to the parent topic. If a word here feels unexplained, it is built below before it is used.

Step 1 — Two tables of paper, stacked

WHAT. Draw the world as two sheets stacked on top of each other. The bottom sheet is the base table — the real place where numbers actually live on disk. The top sheet is the view — a sheet of glass, not paper: it has no ink of its own.

WHY. Before we can ask "can I edit the view?", we must be crystal clear that the view holds no data. A view is a rule for looking, printed on glass. Every mark you seem to see on the glass is really ink on the paper below, shining through.

PICTURE. In the figure, the base sheet (bottom) has real rows. The glass (top) is transparent — the rows you read on it are the base rows shining up. Note the arrow: it only ever points downward, from glass to paper. That downward arrow is the whole topic.

Figure — Views — creating, updatable views

Step 2 — A write is a finger pushing DOWN through the glass

WHAT. Now issue a write: UPDATE v SET price = 90 WHERE item_id = 7. Picture this as a finger landing on one row of the glass and pushing down.

WHY. Every INSERT / UPDATE / DELETE on a view must become a change on the paper — because the glass has nowhere to store anything. The database's job is to translate your finger-push on the glass into a definite change on the paper. The question "is this view updatable?" is exactly: does the finger land on precisely one paper row?

PICTURE. Follow the pale-yellow arrow. It starts on the glass row item_id = 7 and drops straight down onto the one matching paper row. Clean landing. One finger, one target.

Step 3 — The good case: a simple filter view (WHERE only)

WHAT. Our glass is defined by a plain filter:

CREATE VIEW cheap_items AS
SELECT item_id, name, price
FROM items
WHERE price < 100;

This is a SELECT with only a WHERE — no combining, no math, no grouping.

WHY. With only a WHERE, the glass shows a subset of paper rows, each glass row still being exactly one paper row (not a mixture). And every column on the glass (item_id, name, price) is a plain paper column — no disguises. So a finger-push has an obvious, unique target. This is the cleanest possible mapping.

PICTURE. Paper rows with price < 100 shine through to the glass; rows with price ≥ 100 are hidden (greyed out). Each visible glass row has one solid line tying it to one paper row. That line is the "unambiguous map".

Related reading: SQL SELECT and JOINs for how the underlying SELECT builds these rows.

Step 4 — The trap: INSERT that falls outside the glass

WHAT. Insert a row that the base accepts but the view's filter rejects:

INSERT INTO cheap_items VALUES (50, 'Mug', 150);  -- price 150, not < 100

WHY. The WHERE price < 100 only decides what is visible when reading — it does not guard writes. The finger pushes a new row down onto the paper. The paper happily accepts it (150 is a valid price). But when you next look up through the glass, the filter hides it — 150 < 100 is false. The row is real, yet invisible: it "vanished".

PICTURE. The pink arrow drops the new Mug row onto the paper below the visibility line. Look upward: the glass filter blocks it, so the glass shows nothing new. Ink exists on paper; the glass can't show it.

Step 5 — The impossible case: an aggregate view (one glass row over MANY paper rows)

WHAT. Now the glass summarises:

CREATE VIEW dept_stats AS
SELECT dept_id, AVG(salary) AS avg_pay
FROM employees
GROUP BY dept_id;

Try UPDATE dept_stats SET avg_pay = 60000 WHERE dept_id = 3.

WHY. Here one glass row sits over many paper rows (all employees in dept 3). The value avg_pay is not any single paper cell — it is AVG(salary), a computed blend of many. So when the finger pushes down on avg_pay = 60000, the engine asks: which employee's salary do I change, and to what, so the average becomes 60000? There are infinitely many ways to do it (raise one person a lot, or everyone a little...). No unique target → the write is rejected.

PICTURE. ONE glass row fans out to many paper rows — a bundle of lines, not a single line. The finger-push splits and gets lost. That fan-out is exactly what a one-to-one map forbids.

Step 6 — The expression case: a column that isn't a real cell

WHAT. A subtler killer — no aggregate, but a computed column:

CREATE VIEW marked_up AS
SELECT item_id, name, price * 1.1 AS retail
FROM items;

Try UPDATE marked_up SET retail = 110.

WHY. The map is still one glass row ↔ one paper row (no fan-out!). But the column retail is not a paper cell — it's $p r i ce \times 1.1$ . To honour retail = 110, the engine would have to run the formula backwards: solve $p r i ce \times 1.1 = 110 \Rightarrow p r i ce = 100$ . Standard SQL refuses to invert expressions for you, so retail is read-only even though the row map is perfect. The lesson: one-to-one rows is necessary but not sufficient — the written column must also be a raw base column.

PICTURE. One clean line down to one paper row (good!), but the glass column retail is drawn as a little multiplier box $\times 1.1$ sitting between glass and paper. The finger can push a number into the box, but the box won't tell the engine what paper value produced it.

Step 7 — The escape hatch: INSTEAD OF triggers supply the missing map

WHAT. Suppose you must write through a hard view (say a multi-table join). Define an INSTEAD OF trigger: a small program that says, in your own words, "when someone pushes here, instead of trying to guess, do these exact changes on these exact base tables."

WHY. The engine rejected the write only because it couldn't infer the map. An INSTEAD OF trigger lets you hand it the map by hand. You become the translator the engine wasn't allowed to be.

PICTURE. The finger lands on a "join" glass row whose lines fan to two paper tables. A dashed trigger box intercepts the push and redraws it as two definite arrows — one to each table — that you specified.

The one-picture summary

Everything above collapses into one test. Send a finger down through the glass and ask two questions:

Does it land on exactly ONE paper row? (No fan-out from GROUP BY / aggregate / multi-table join / DISTINCT / UNION.)
Is the column you're writing a RAW paper cell? (Not price*1.1, not AVG(...).)

If both are yes → updatable. If either is no → read-only (or reach for INSTEAD OF).

Recall Feynman retelling — the whole walkthrough in plain words

Picture a sheet of glass lying on top of a sheet of paper. The paper holds the real data; the glass just lets you look at a chosen slice — that's a view, and it stores nothing.

When you "edit the view", you're really pushing your finger down through the glass to move ink on the paper. Whether that's allowed depends on where your finger lands:

A plain WHERE view (Step 3): each glass row sits on exactly one paper row, so your finger lands on one clear spot — editable.
But WHERE only filters what you see, not what you write: an insert can land on paper outside the visible strip and vanish (Step 4). Fix that with WITH CHECK OPTION.
An AVG / GROUP BY view (Step 5): one glass row spreads over many paper rows, so your finger fans out and gets lost — the engine can't guess which salary to change to fix an average — not editable.
Even with a clean one-to-one map, a computed column like price*1.1 (Step 6) can't be written, because the engine won't run your formula backwards.
And if you truly must write through a messy view, an INSTEAD OF trigger (Step 7) lets you hand-draw the arrows yourself.

So the whole topic is one question: does my finger land on exactly one real cell? Yes → edit it. No → it's a window you can only look through.

Recall Quick self-check

Why is a plain WHERE view updatable? ::: Each glass (view) row maps to exactly one paper (base) row and every column is a real base column — the write has a unique target. Why is a GROUP BY/AVG view not updatable? ::: One view row summarises many base rows; a write fans out with infinitely many possible base changes, so there is no unique target. Does WHERE price<100 block a bad INSERT? ::: No — WHERE filters reads only; the row lands on the base table and then disappears from the view. Use WITH CHECK OPTION. Why can't you write retail in SELECT price*1.1 AS retail? ::: It is an expression, not a raw base column; the engine won't invert the formula, so the column is read-only. What does an INSTEAD OF trigger provide? ::: The base-table mapping the engine couldn't infer — you specify exactly which tables/rows to modify.

Prerequisites & neighbours: SQL SELECT and JOINs · Database Security and GRANT · Normalization · Materialized Views · Triggers · Query Optimization and View Merging.