Updatable Views

A regular view is normally used for reading data, but many databases also allow INSERT, UPDATE, and DELETE statements to be run directly against a view. When that happens, the database translates the change into a modification of the underlying base table — the view itself still stores nothing. Not every view supports this, though; whether a view is updatable depends on how it is defined.

A simple updatable view

A straightforward, updatable view

SQL

CREATE VIEW active_employees AS
SELECT employee_id, full_name, department, email
FROM employees
WHERE is_active = TRUE;

-- This UPDATE is run against the view...
UPDATE active_employees
SET department = 'Engineering'
WHERE employee_id = 42;
-- ...but it actually modifies the employees base table.

UPDATE 1

What makes a view updatable

The exact rules differ from one database to another, but most dialects agree on the general shape of the restriction: a view can only be updated if the database can unambiguously map each row of the view back to exactly one row in exactly one base table.

Restriction	Why it blocks updates
Based on a single table	If a view joins two or more tables, an UPDATE or INSERT on one row of the view could need to touch rows in both underlying tables at once, which is ambiguous or unsupported in most dialects.
No aggregate functions	A view using SUM, COUNT, AVG, and similar functions produces computed values with no single corresponding row to write back to.
No GROUP BY or DISTINCT	These collapse multiple base-table rows into fewer view rows, so there is no one-to-one mapping to update.
No complex joins or subqueries in the column list	If a column comes from a correlated subquery or a non-trivial join, the database cannot reliably determine which base-table row and column it should update.

Rules vary by dialect

PostgreSQL, MySQL, and SQL Server each implement slightly different (though overlapping) rules for what counts as an updatable view, and each has its own error message when an update is rejected. Always test against the specific database in use rather than assuming a view is updatable just because it looks simple.

WITH CHECK OPTION

A subtle problem can occur with a view that has a WHERE clause: an UPDATE could change a row so that it no longer matches the view's filter, effectively making the row disappear from the view after the update, even though the update succeeded. Adding WITH CHECK OPTION to the view definition tells the database to reject any INSERT or UPDATE that would produce a row violating the view's WHERE clause.

Preventing rows from silently falling out of a view

SQL

CREATE VIEW active_employees AS
SELECT employee_id, full_name, department, is_active
FROM employees
WHERE is_active = TRUE
WITH CHECK OPTION;

-- Rejected: this would set is_active to FALSE,
-- which would violate the view's WHERE clause.
UPDATE active_employees
SET is_active = FALSE
WHERE employee_id = 42;

ERROR:  new row violates check option for view "active_employees"

Without WITH CHECK OPTION, that same UPDATE would succeed silently, and the row would simply vanish from future queries against active_employees — a subtle correctness bug that WITH CHECK OPTION exists specifically to catch.

Less common in modern application design

In many modern applications, developers prefer to query and modify base tables directly through an ORM or a well-defined data-access layer, so updatable views see relatively little use in new systems. They remain important to understand, though, because they show up frequently in legacy databases, in reporting layers built on top of production schemas, and in scenarios where a view is deliberately used to grant restricted write access to part of a table.

An updatable view lets INSERT/UPDATE/DELETE against the view modify the underlying base table.
A view is typically updatable only if it maps cleanly to a single base table with no aggregation, GROUP BY, DISTINCT, or complex joins.
WITH CHECK OPTION blocks writes that would make a row fall outside the view's own WHERE clause.
Exact updatability rules differ by dialect — verify behavior on the specific database being used.