Codd's view-updating rule says every view that is theoretically updatable must also be updatable by the system. Why do real engines fall short of that, and what do they offer instead?
answer
- view = stored query; write must map back to base rows
- safe: one table, key preserved, omitted cols nullable/defaulted
- breaks on aggregate, join, DISTINCT, UNION, computed cols
- INSTEAD OF trigger supplies the missing semantics
- check option stops rows migrating out of the view
basics
~20 sBecause for many views a change to the view has no unique translation back to base rows — joins, aggregates and set operations are ambiguous or lossy. Engines auto-update only simple single-table views and require an explicit handler, such as an INSTEAD OF trigger, for the rest.
solid answer
~60 sRule 6 asks that any view that is *theoretically* updatable be updatable in practice. The gap is that deciding theoretical updatability is hard, and the translation is often not unique. A view is a stored query. To apply a change you must map the requested change on view rows back to changes on base rows. That mapping is unique only when the view preserves the base table's identity and every base column not in the view can be left alone — roughly, a projection and restriction of one table that keeps its key. It fails when the view aggregates (which underlying row does a changed SUM belong to?), joins (which side does the change hit, and what happens to rows on the other side?), applies DISTINCT or set operations (one view row stands for several base rows), or omits a NOT NULL column with no default (an insert cannot produce a legal base row). Engines therefore auto-update only a well-defined simple subset and let developers supply the semantics explicitly for anything else, typically via INSTEAD OF triggers. Some also offer an option that rejects changes producing rows the view would no longer show.
code
sql · 5 linesCREATE VIEW active_customers AS
SELECT customer_id, name, email
FROM customers
WHERE status = 'ACTIVE'
WITH CHECK OPTION;go deeper
Know a view is a stored query, that simple single-table views can be written through, and that aggregates and joins generally cannot.
State the key-preservation condition and name the failure classes with a reason for each.
Cover INSTEAD OF triggers as the explicit translation point, the check option for row migration, and the read-easy/write-hard asymmetry in migrations.
Decide policy: whether writes go through the view layer at all, or whether views stay a read contract while writes target base tables through a service, and justify the choice on evolvability.
## The rule and the problem Codd's sixth rule: *all views that are theoretically updatable are also updatable by the system.* A **view** is a named stored query; users read it as if it were a table. The rule is about writes: if there exists an unambiguous way to translate a change on view rows into changes on the underlying base tables, the engine should do it rather than forcing the user down to the base tables. The difficulty is that 'theoretically updatable' is not a simple test, and for many views the translation either does not exist or is not unique. ## When translation is unambiguous The safe case is a view that is a **restriction and projection of a single base table that retains that table's key**. Selecting a subset of columns and a subset of rows preserves a one-to-one correspondence between view rows and base rows, so an update to a view row means an update to exactly one base row. Even here two conditions matter: the key must survive into the view (otherwise you cannot identify which base row to change), and any base column excluded from the view must be omissible on insert — nullable or defaulted — or inserts through the view cannot construct a legal base row. ## Why the general case fails **Aggregation.** A view exposing one row per customer with a total order amount has no answer to 'set the total to 500'. Adjust which order? Add a new one? Scale them all? Multiple base states produce the same view row, so the inverse is not a function. **Joins.** A view joining orders to customers can, in narrow cases, support an update to columns from one side. But in general: which side owns a changed column? What does deleting a view row mean — delete the order, the customer, or both? An insert may require creating a row on each side, and the engine cannot know whether that is intended. **DISTINCT, UNION, GROUP BY.** One view row can stand for many base rows, or for rows from different tables. A single delete has no single target. **Computed and derived columns.** A column defined as an expression cannot generally be inverted; only special cases such as a simple unit conversion are reversible, and engines do not attempt it. **Row migration and disappearance.** Even in a simple filtered view, an update can change a row so that it no longer satisfies the view's condition — the row vanishes from the caller's perspective, or an insert produces a row the view cannot see. The standard offers a check option that rejects such changes; without it, callers get surprising behaviour that looks like data loss. ## What engines actually do Every mainstream engine defines an **auto-updatable subset**, broadly matching the single-table restriction-projection case, sometimes extended to certain key-preserved joins. Outside that subset, the engine refuses to guess and instead provides a hook: an **INSTEAD OF trigger** (or an equivalent rule mechanism) where the developer writes the intended translation explicitly. That is the pragmatic resolution of the rule: the system does not decide semantics it cannot derive, but it does give a place to state them once, in the database, so all callers get the same behaviour. ## Why this matters architecturally Views are the main tool for insulating applications from schema change, but that insulation is asymmetric: reads are easy to preserve, writes are not. When a view layer is introduced to shield callers from a restructuring, the read side usually works unchanged while every write path needs an explicit translation. Planning a migration that treats a view as a fully transparent stand-in for a table will surface exactly this asymmetry. The strong answer in an interview states the rule, gives the precise condition for unambiguous translation (single table, key preserved, omitted columns nullable or defaulted), names two concrete failure classes with a reason, and finishes with the engineering resolution: auto-updatable subset plus explicit INSTEAD OF handlers, plus a check option so updates cannot silently push rows out of the view.
- Give the precise conditions under which an update through a view maps unambiguously to one base row.The view must select from a single base table, preserve that table's key so each view row identifies exactly one base row, and expose columns without duplicate-collapsing operations such as DISTINCT, GROUP BY or set operations. For inserts, every base column not exposed by the view must be nullable or defaulted; otherwise the insert cannot construct a legal base row.
- An update through a filtered view changes a row so it no longer matches the view's condition. What happens, and how do you control it?By default the update succeeds and the row disappears from the view, which looks like data loss to the caller. The SQL standard's check option makes the engine reject any insert or update that would produce a row the view cannot see, keeping the view a closed world for its writers.
Reading a view is reading a summary of a report; writing to it is editing the summary and expecting the report to update itself — fine if the summary is a page of the report, impossible if it is a total.
saying these in an interview costs you the question
- Claiming views are never updatable — simple key-preserving single-table views are, in every mainstream engine.
- Claiming any view can be made updatable automatically; aggregates and set operations have no unique inverse.
- Forgetting that inserts through a view fail when an omitted base column is NOT NULL without a default.
- Assuming a view layer makes writes as schema-independent as reads — the write side is the hard half.