Can you INSERT, UPDATE or DELETE through a view?
Only if the view is simple, based on one table without DISTINCT, GROUP BY, aggregates or joins that make the target row ambiguous. Otherwise the database cannot map the change to base rows. WITH CHECK OPTION blocks rows leaving the view.
Explain it simply
A window lets you reach through to the room behind it only when each pane maps to a clear spot. If the view shows a total of many rows, there is no single row to change.
Example
CREATE VIEW pune_students AS
SELECT id, name, city
FROM students
WHERE city = 'Pune'
WITH CHECK OPTION;
UPDATE pune_students SET name = 'Asha K' WHERE id = 1; -- works, updates students
INSERT INTO pune_students VALUES (9, 'Zoya', 'Delhi'); -- fails, violates CHECK OPTIONWithout WITH CHECK OPTION, the second insert would succeed and the row would vanish from the view because it is not in Pune.
Usually not updatable:
- Views with aggregates, GROUP BY, DISTINCT, UNION or window functions.
- Views that omit NOT NULL columns without defaults (inserts fail).
- Multi-table join views, in many databases (some allow updates of one underlying table).
Some databases let you use an INSTEAD OF trigger to define what happens on a change.
What to say in the interview
"Simple single-table views are updatable. Views with aggregates, DISTINCT or ambiguous joins are not. WITH CHECK OPTION stops changes that would move rows outside the view's filter."
Common mistakes
- Expecting an aggregate view to accept UPDATE.