Which constructs in a view's defining query make the view non-updatable to the database engine, and what is the underlying reason the engine refuses those writes?

middlemust knowfreq 50%

answer

one view row → one base row, invertible columns
aggregate/DISTINCT/UNION = ambiguous target
window fn, LIMIT = positional, not logical
expressions read-only per column
joins need a key-preserved side

basics

~20 s

Aggregates, GROUP BY/HAVING, DISTINCT, set operations like UNION, window functions, LIMIT, and most multi-table joins. All of them break the one-to-one mapping from a view row back to a single base-table row and column, so the engine cannot decide which row to modify.

solid answer

~60 s

The single rule behind every restriction: the engine must be able to map each view row back to exactly one row of one base table, and each writable view column back to exactly one base column.

Things that break it:

Aggregation (SUM, COUNT, GROUP BY, HAVING): one view row summarises many base rows, so there is no target row.
DISTINCT: one view row may stand for several duplicate base rows.
Set operations (UNION, INTERSECT, EXCEPT): the source table of a row is ambiguous.
Window functions: the value depends on other rows, so it cannot be inverted.
LIMIT/OFFSET and, in some engines, ORDER BY with limits: which base rows participate is positional, not logical.
Expressions in the select list: not invertible, so that column is read-only even when the rest of the view is writable.
Joins: allowed only where the engine can prove one side is key-preserved.

When you genuinely need writes through such a view, an INSTEAD OF trigger supplies the mapping the engine cannot infer.

The single rule behind every restriction: the engine must be able to map each view row back to exactly one row of one base table, and each writable view column back to exactly one base column. Things that break it: - **Aggregation** (SUM, COUNT, GROUP BY, HAVING): one view row summarises many base rows, so there is no target row. - **DISTINCT**: one view row may stand for several duplicate base rows. - **Set operations** (UNION, INTERSECT, EXCEPT): the source table of a row is ambiguous. - **Window functions**: the value depends on other rows, so it cannot be inverted. - **LIMIT/OFFSET and, in some engines, ORDER BY with limits**: which base rows participate is positional, not logical. - **Expressions in the select list**: not invertible, so that column is read-only even when the rest of the view is writable. - **Joins**: allowed only where the engine can prove one side is key-preserved. When you genuinely need writes through such a view, an INSTEAD OF trigger supplies the mapping the engine cannot infer.

code

sql · 5 lines

sql

CREATE VIEW distinct_cities AS
SELECT DISTINCT city FROM addresses;

DELETE FROM distinct_cities WHERE city = 'Berlin';
-- rejected: one view row may stand for many base rows

go deeper

for a junior

Recall the headline blockers — aggregates, GROUP BY, DISTINCT, UNION — and that a plain filter view is still writable.

for a middle

Derive the list from the one-row-to-one-row rule and explain per-column read-only behaviour for expressions; know inserts are stricter than updates.

for a senior

Talk about detection and blast radius: catalog updatability flags, a CI write-smoke-test, and the review rule that adding aggregation to a shared view is a breaking change.

for a principal

Argue whether write-through views belong in the design at all, given that updatability is an implicit, engine-dependent contract that any read-side change can silently revoke.

The one rule

Every updatability restriction is a corollary of a single requirement: to execute a write, the engine must resolve a view row to exactly one base-table row, and each written view column to exactly one base-table column. If that resolution is ambiguous or non-invertible, the engine refuses rather than guess. Learning the rule beats memorising the list — you can rederive the list from it.

Constructs that break the mapping

Aggregation — SUM, COUNT, AVG, MIN, MAX, GROUP BY, HAVING. A row of a grouped view stands for a whole set of base rows. Setting a group's total to 500 does not say what each contributing row becomes. There is no defensible rewrite, so writes are rejected.

DISTINCT. Duplicate elimination means one view row may represent several identical base rows. Deleting that view row is ambiguous — one of them, or all of them? The standard treats a DISTINCT view as non-updatable.

Set operations — UNION, UNION ALL, INTERSECT, EXCEPT. A row emerging from a union does not carry the identity of its source branch. An UPDATE cannot tell whether to touch the first table, the second, or both. INTERSECT and EXCEPT are worse: membership itself is derived from comparing sets.

Window functions. A running total or ROW_NUMBER value is computed from the surrounding partition. It is not a property of the row, so it cannot be written back, and its presence usually makes the whole view read-only.

LIMIT / OFFSET / FETCH FIRST. Which base rows survive depends on ordering and position, not on a predicate. A rewrite would have to re-evaluate the ordering during the write, and the row set could differ. Engines refuse.

Expressions and function calls in the select list. A column defined as price * 1.2 or UPPER(name) is not invertible in general. Note this is per-column: many engines keep the view updatable for its plain columns and reject writes only to the derived one. Constants, CASE expressions and concatenations behave the same way.

Joins. Joins are not universally banned, but they need extra reasoning. The engine must establish that the target table is key-preserved — that each row of that table appears at most once in the view result, which follows from joining on the other side's primary or unique key. Some engines support this for updates and deletes; others refuse joined views entirely. Do not assume portability here.

Recursive and set-returning constructs. Recursive common table expressions, table functions, and correlated subqueries in the FROM clause make the row provenance opaque and are non-updatable.

Nesting. A view over another view is updatable only if the whole chain is. One aggregating view three levels down poisons everything built on top of it.

What is explicitly allowed

A WHERE clause filtering rows. Filtering preserves the one-to-one mapping.
Projecting a subset of columns, subject to the INSERT caveat that hidden NOT NULL columns without defaults make inserts impossible.
Renaming columns via aliases — a rename is trivially invertible.
Reading from another updatable view.

Why engines are conservative

A silent wrong guess would corrupt data. Given that any inference could be wrong for some workload, engines pick the safe side and reject anything they cannot prove. That is why the failure surfaces as an error at statement time rather than as a partial or surprising update. It also means the same view definition may be updatable on one engine and rejected on another: the standard defines a conservative core, and vendors extend it by differing amounts. Treat write-through as a capability you verify, not one you assume.

Practical detection and workarounds

Detect early: most catalogs expose an is_updatable-style flag per view, and a smoke test in CI that attempts a write inside a rolled-back transaction is cheap insurance. The moment a view acquires a GROUP BY for a new report, any code writing through it breaks — a change that reviewers routinely miss.

The workarounds, in ascending order of cost:

Restructure the view so it stays simple, and put the aggregation in a separate view for readers.
Write directly to the base table from the application and keep the complex view read-only. Often the right answer.
Add an INSTEAD OF trigger encoding the mapping explicitly — which tables to touch, in what order, with what defaults. Now the mapping is your code's responsibility, and it must handle multi-row statements and constraint failures.

Interview framing

State the rule first, derive two or three examples from it, and note that joins are the nuanced case that depends on key preservation and on the engine. Finish with the escape hatch. Reciting a memorised list without the underlying reason reads as shallow.

## The one rule Every updatability restriction is a corollary of a single requirement: to execute a write, the engine must resolve a view row to exactly one base-table row, and each written view column to exactly one base-table column. If that resolution is ambiguous or non-invertible, the engine refuses rather than guess. Learning the rule beats memorising the list — you can rederive the list from it. ## Constructs that break the mapping **Aggregation — SUM, COUNT, AVG, MIN, MAX, GROUP BY, HAVING.** A row of a grouped view stands for a whole set of base rows. Setting a group's total to 500 does not say what each contributing row becomes. There is no defensible rewrite, so writes are rejected. **DISTINCT.** Duplicate elimination means one view row may represent several identical base rows. Deleting that view row is ambiguous — one of them, or all of them? The standard treats a DISTINCT view as non-updatable. **Set operations — UNION, UNION ALL, INTERSECT, EXCEPT.** A row emerging from a union does not carry the identity of its source branch. An UPDATE cannot tell whether to touch the first table, the second, or both. INTERSECT and EXCEPT are worse: membership itself is derived from comparing sets. **Window functions.** A running total or ROW_NUMBER value is computed from the surrounding partition. It is not a property of the row, so it cannot be written back, and its presence usually makes the whole view read-only. **LIMIT / OFFSET / FETCH FIRST.** Which base rows survive depends on ordering and position, not on a predicate. A rewrite would have to re-evaluate the ordering during the write, and the row set could differ. Engines refuse. **Expressions and function calls in the select list.** A column defined as price * 1.2 or UPPER(name) is not invertible in general. Note this is per-column: many engines keep the view updatable for its plain columns and reject writes only to the derived one. Constants, CASE expressions and concatenations behave the same way. **Joins.** Joins are not universally banned, but they need extra reasoning. The engine must establish that the target table is *key-preserved* — that each row of that table appears at most once in the view result, which follows from joining on the other side's primary or unique key. Some engines support this for updates and deletes; others refuse joined views entirely. Do not assume portability here. **Recursive and set-returning constructs.** Recursive common table expressions, table functions, and correlated subqueries in the FROM clause make the row provenance opaque and are non-updatable. **Nesting.** A view over another view is updatable only if the whole chain is. One aggregating view three levels down poisons everything built on top of it. ## What is explicitly allowed - A WHERE clause filtering rows. Filtering preserves the one-to-one mapping. - Projecting a subset of columns, subject to the INSERT caveat that hidden NOT NULL columns without defaults make inserts impossible. - Renaming columns via aliases — a rename is trivially invertible. - Reading from another updatable view. ## Why engines are conservative A silent wrong guess would corrupt data. Given that any inference could be wrong for some workload, engines pick the safe side and reject anything they cannot prove. That is why the failure surfaces as an error at statement time rather than as a partial or surprising update. It also means the *same* view definition may be updatable on one engine and rejected on another: the standard defines a conservative core, and vendors extend it by differing amounts. Treat write-through as a capability you verify, not one you assume. ## Practical detection and workarounds Detect early: most catalogs expose an is_updatable-style flag per view, and a smoke test in CI that attempts a write inside a rolled-back transaction is cheap insurance. The moment a view acquires a GROUP BY for a new report, any code writing through it breaks — a change that reviewers routinely miss. The workarounds, in ascending order of cost: 1. Restructure the view so it stays simple, and put the aggregation in a separate view for readers. 2. Write directly to the base table from the application and keep the complex view read-only. Often the right answer. 3. Add an INSTEAD OF trigger encoding the mapping explicitly — which tables to touch, in what order, with what defaults. Now the mapping is your code's responsibility, and it must handle multi-row statements and constraint failures. ## Interview framing State the rule first, derive two or three examples from it, and note that joins are the nuanced case that depends on key preservation and on the engine. Finish with the escape hatch. Reciting a memorised list without the underlying reason reads as shallow.

A view is currently updatable and someone adds a GROUP BY to serve a new report. What breaks, and when do you find out?
Every INSERT, UPDATE and DELETE through that view starts failing, because a grouped row no longer maps to one base row. You find out at runtime on the first write after deployment unless something catches it earlier. Guards are a catalog check on the view's updatability flag plus a CI test that attempts a write inside a transaction that is rolled back.
Why does a WHERE clause keep a view updatable while DISTINCT does not?
A WHERE clause only removes rows; each surviving view row is still exactly one base row, so the engine can rewrite the statement by appending the predicate. DISTINCT collapses duplicates, so one view row can correspond to several base rows and the engine cannot tell which to modify. The distinction is whether the row identity survives.

saying these in an interview costs you the question

Claiming any view containing a join is automatically non-updatable
Claiming any join view is updatable without mentioning key preservation
Saying an expression column makes the entire view read-only in every engine
Assuming the updatability rules are identical across engines
Thinking a view over a non-updatable view can still be written to

Which constructs in a view's defining query make the view non-updatable to the database engine, and what is the underlying reason the engine refuses those writes?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The one rule

Constructs that break the mapping

What is explicitly allowed

Why engines are conservative

Practical detection and workarounds

Interview framing

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions6 questions

▾related topics3 topics

The one rule

Constructs that break the mapping

What is explicitly allowed

Why engines are conservative

Practical detection and workarounds

Interview framing

answer

go deeper

context