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;