A view joins a customers table to an orders table, and an application tries to UPDATE a column through it. What is a key-preserved table, and why do engines use that concept to decide whether the update is allowed?

## Why joins are the hard case Filters and column projections keep row identity intact, so the engine can rewrite writes trivially. A join can multiply rows: a parent row matching N child rows appears N times in the result. Once a base row appears more than once, an UPDATE through the view is ambiguous — the same base row is reachable through several view rows, and one statement could assign it conflicting values. Engines need a decision procedure. That procedure is key preservation. ## The definition A base table is **key-preserved** in a view if every key of that table remains a key of the view result — equivalently, each of its rows can appear at most once in the view. The concept originated in Oracle's terminology but the underlying reasoning is what every engine that supports join-view writes implements, under one name or another. The usual test: join child to parent on the parent's primary or unique key. Because at most one parent row matches each child row, the child rows are not multiplied, and the child is key-preserved. The parent is not: it is duplicated once per matching child. ``` orders o JOIN customers c ON o.customer_id = c.customer_id ``` Here `customers.customer_id` is the primary key, so each order row survives exactly once — `orders` is key-preserved. `customers` is not. Note what this hinges on: the *declared* uniqueness of `customers.customer_id`. The engine reasons from catalog metadata, not from the data that happens to be present. If the join column has no declared primary key or unique constraint, the engine must assume duplicates are possible and neither side is key-preserved, even when today's data is in fact unique. ## What follows for DML On engines that support it: - **UPDATE**: you may set columns belonging to a key-preserved table. Attempting to set a column of a non-key-preserved table raises an error. - **DELETE**: allowed when exactly one table in the view is key-preserved, so the target is unambiguous; if two tables are key-preserved (a one-to-one join on both keys), the engine cannot choose and refuses. - **INSERT**: strictest. Only columns of a single key-preserved table may be supplied, and every mandatory column of that table must be reachable. Inserting into two tables through one view is never inferred. Outer joins add their own wrinkle: on the null-supplying side, rows may not exist at all, so writes to that side make no sense and are refused. ## Portability This is one of the least portable corners of SQL. Oracle implements key preservation explicitly and documents it. MySQL permits updates to multi-table views under related conditions but forbids inserts into joins. PostgreSQL's automatic updatability requires a single base relation in the FROM clause, so join views are simply not auto-updatable there and need a trigger or rule. Never carry an assumption from one engine to another; verify against the target engine, and prefer not to depend on join-view writes at all in a system that might be ported. ## The operational trap Key preservation is inferred from constraints, which makes it fragile in a way schema changes do not advertise. Dropping a unique index during a maintenance window, replacing a primary key, or adding a second matching row-set can silently move a view from updatable to non-updatable. Nothing in the view definition changed; nothing in the application changed; the writes start failing. When a system relies on join-view writes, the uniqueness constraints those views depend on are load-bearing and should be treated as part of the contract — documented, and covered by a test that attempts the write and rolls back. ## The alternative The pragmatic senior answer is usually to stop relying on inference. Either have the application write to the base tables directly — the join view stays a read surface — or attach an INSTEAD OF trigger that states explicitly which table each written column belongs to. The trigger costs code and must handle multi-row statements, but it is portable, explicit, reviewable, and immune to the engine's inference rules changing under you. ## Interview framing Define key preservation in one sentence (each row of that table appears at most once in the view), give the customers/orders example, explain the ambiguity that motivates the rule, then land the two senior points: the property is proved from declared constraints and therefore breaks with schema changes, and engine support varies enough that join-view writes are a portability liability.