Can an application run INSERT, UPDATE or DELETE statements against a database view, and if so what data actually changes?
answer
- stored query, not stored rows
- one view row → one base row
- no aggregate / DISTINCT / GROUP BY / UNION
- INSERT needs hidden NOT NULL columns
- rows can escape unless WITH CHECK OPTION
basics
~20 sYes, if the view is updatable. A view stores no rows of its own, so the engine rewrites the write into a write on the underlying base table. Simple views that only project columns and filter rows are usually updatable; views with aggregation or DISTINCT are not.
solid answer
~60 sA view is a stored query, not stored data, so a write through it must be translated into a write on the base table. Engines allow this for **simple** views: one base table, no aggregation, no GROUP BY/HAVING, no DISTINCT, no set operations, no window functions, and projected columns mapping one-to-one to base columns. Under those conditions the engine rewrites the statement against the base table, and the change lands there permanently — there is no separate copy to update. Two practical consequences. First, an INSERT must supply values for every base-table column that is NOT NULL without a default; if the view hides such a column, inserts fail. Second, by default a write can push a row *out of* the view's own predicate — you can insert a row the view will never show, or update a visible row so it disappears. That is what WITH CHECK OPTION exists to prevent. When the view is too complex to be auto-updatable, an INSTEAD OF trigger is the escape hatch.
code
sql · 8 linesCREATE VIEW open_orders AS
SELECT order_id, customer_id, status, total
FROM orders
WHERE status = 'OPEN';
UPDATE open_orders SET total = 120.00 WHERE order_id = 42;
-- engine executes: UPDATE orders SET total = 120.00
-- WHERE order_id = 42 AND status = 'OPEN';go deeper
Know that a view stores a query rather than data, that simple single-table views accept writes, and that those writes change the base table.
Be able to list the constructs that block auto-updatability and explain why (no unambiguous mapping back to one base row), plus the hidden-NOT-NULL insert failure.
Frame the updatable view as a real write surface: privilege model, rows escaping the predicate, and when to reach for WITH CHECK OPTION or an INSTEAD OF trigger instead.
Take a position on whether write-through views belong in the architecture at all — they are an implicit contract between schema and callers, and a silently non-updatable view is a deployment-time surprise.
## A view stores a query, not rows Defining a view records a query definition in the catalog. It allocates no storage and copies no data. Whenever a statement references the view, the engine substitutes that definition and works against the real tables underneath. So "writing to a view" is always shorthand for: the engine rewrites your statement into an equivalent statement on the base table(s), and the base table is what changes. Because of that, a view is not a place where data can diverge. If two sessions read the same view they see whatever the base tables currently hold under their transaction's snapshot. Once an update through the view commits, every other reader of the base table sees it too. ## When the engine can do the rewrite The rewrite only works when there is an unambiguous mapping from a row of the view back to exactly one row of one base table, and from each updated view column back to exactly one base column. That is the whole game. Roughly, a view is auto-updatable when it: - selects from exactly one table (or one other updatable view); - projects plain column references, not expressions, aggregates, or window functions, at least for the columns you intend to write; - has no GROUP BY, HAVING, DISTINCT, LIMIT/OFFSET, or set operations (UNION, INTERSECT, EXCEPT); - may have a WHERE clause — filtering rows is fine, because each surviving view row is still exactly one base row. A WHERE clause is the interesting allowed case: a view like "active customers" is a filter over one table, and updating a row through it simply updates that base row. A computed column such as first_name || ' ' || last_name is the interesting disallowed case: the engine cannot invert the concatenation to decide what to store. Many engines allow such a view to be updatable for the *other*, plain columns while rejecting writes to the computed one. ## Inserts are the strictest case UPDATE and DELETE operate on rows that already exist, so the hidden columns keep their values. INSERT has to materialise a whole base row from a partial view row. Any base column that is NOT NULL, has no default, and is not exposed by the view makes inserts through the view impossible — the engine has no value to write. This is a very common surprise when a view was created purely to hide columns for security reasons and someone later tries to write through it. Fixes: expose the column, give it a database default, or take over the insert with an INSTEAD OF trigger. ## Rows that escape the view By default the view predicate is applied when *reading*, not when writing. Through a view defined as "orders where status = 'OPEN'" you can insert a row with status 'CLOSED' — the insert succeeds, the row lands in the base table, and the view immediately cannot see it. Likewise an UPDATE that sets status = 'CLOSED' makes the row vanish from the view. Neither is an error; the engine is doing exactly what the rewrite says. Declaring the view WITH CHECK OPTION makes the engine validate written rows against the view predicate and reject the ones that would fall outside. ## Permissions and the security angle Because the write is executed as part of the view's definition, privileges are typically checked against the view's owner rather than the caller for the base table. That is why granting INSERT/UPDATE on a narrow view, while granting nothing on the base table, is a real access-control pattern: callers can only touch the columns and rows the view exposes. It also means an updatable view is a genuine write surface, and should be reviewed as one. ## When the view is too complex Joins, aggregates and UNIONs break the one-row-to-one-row mapping, so the engine refuses. The standard escape hatch is an INSTEAD OF trigger: you write the procedural code that decides which base tables to modify, and the engine runs your code in place of its own rewrite. That converts "the engine cannot infer the intent" into "the developer stated the intent explicitly". ## What to say in an interview Lead with "a view holds no data, so writes are rewritten onto the base table", then give the shape of the updatability rules (single table, no aggregation/DISTINCT/set ops, plain column projections), then the two gotchas: NOT NULL columns hidden from an INSERT, and rows escaping the view unless WITH CHECK OPTION is declared.
- A view exposes three of a table's ten columns and an INSERT through it fails. What is the most likely cause?One of the seven hidden columns is NOT NULL with no default, so the engine has no value to write for it. UPDATE and DELETE still work because those rows already carry values for the hidden columns. The fixes are to add a database default, expose the column in the view, or intercept the insert with an INSTEAD OF trigger that supplies the value.
- Does a view with a WHERE clause stay updatable, and what happens if an update moves a row outside that predicate?Yes — filtering rows keeps the one-view-row-to-one-base-row mapping, so the view stays updatable. By default an update that violates the predicate still succeeds; the row simply disappears from the view because the predicate is only applied on read. Adding WITH CHECK OPTION makes the engine validate the written row against the predicate and reject it instead.
A view is a window onto a room, not a photograph of it. You can reach through the window and move the furniture, but only if the window shows the room plainly — if it shows a summary or a blend of two rooms, there is no way to reach through it.
saying these in an interview costs you the question
- Saying a view keeps its own copy of the data that has to be refreshed after a write
- Claiming no view can ever be written to
- Assuming a WHERE clause makes a view read-only
- Believing a write through a view is automatically prevented from producing rows the view cannot see
- Thinking an INSERT works whenever an UPDATE does