Unlike base tables, VIEWs are either updatable or read-only, but not both. INSERT, UPDATE, and DELETE operations are allowed on updatable VIEWs and base tables, subject to any other constraints.
INSERT, UPDATE, and DELETE are not allowed on read-only VIEWs, but you can change their base tables, as you would expect.
An updatable VIEW is one that can have each of its rows associated with exactly one row in an underlying base table.
When the VIEW is changed, the changes pass unambiguously through the VIEW to that underlying base table.
Updatable VIEWs in Standard SQL are defined only for queries that meet these criteria:
Rules for Updatable View
- Built on only one table
- No GROUP BY clause
- No HAVING clause
- No aggregate functions
- No calculated columns
- No UNION, INTERSECT, or EXCEPT
- No SELECT DISTINCT clause
- Any columns excluded from the VIEW must be NULL-able or have a DEFAULT in the base table, so that a whole row can be constructed for insertion By implication, the VIEW must also contain a key of the table.
In short, we are absolutely sure that each row in the VIEW maps back to one and only one row in the base table.
Some updating is handled by the CASCADE option in the referential integrity constraints on the base tables, not by the VIEW declaration.
The definition of updatability in Standard SQL is actually fairly limited, but very safe.
The database system could look at the information it has in the referential integrity constraints to widen the set of allowed updatable VIEWs.
You will find that some implementations are now doing just that, but it is not common yet.
The SQL Standard definition of an updatable VIEW is actually a subset of the possible updatable VIEWs,and a very small subset at that.
The major advantage of this definition is that it is based on syntax and not semantics.
Top Example of Updatable-view
CREATE VIEW Foo1 -- updatable, has a key! AS SELECT * FROM Foobar WHERE x IN (1, 2);
Top Example of Non-updatable View
CREATE VIEW Foo2 -- not updatable! AS SELECT * FROM Foobar WHERE x = 1 UNION ALL SELECT * FROM Foobar WHERE x = 2;