SQL views are part of the external database scheme. A view is defined by means of a SQL query and its content is generated upon invocation of the view by an application or other query. Hence it is a virtual table without physical tuples. Views offer a number of advantages. Indeed, they facilitate the ease of use by hiding complex queries such as join queries or correlated queries from their users.
Besides, they can also provide data protection by hiding columns or tuples from unauthorized users. Views are also a key component in a “three-layer” database architecture and allow for logical data independence. Views can be defined using the CREATE VIEW operator.
The database will modify queries that previews into queries on the underlying base tables.
Some views can be updated. In this case, the view serves as a window through which updates are propagated to the underlying base table(s). Updatable views require that INSERT, UPDATAE and DELETE instructions can be unambiguously mapped to INSERTs, UPDATEs and DELETEs on the underlying base table(s). If this property does not hold, the views I read only.
NB: A view update is feasible when only one possible update on the base tables can accomplish the desired update effect on the view.
Various requirements can be listed for views to be updatable. They depend upon the vendor of the arguments.
- No DISTINCT option in the SELECT-component
- No aggregate functions in the SELECT-component
- Only one table name in the FROM-component
- No correlated sub-query in the WHERE-component
- No GROUP BY in the WHERE-component
- No UNION, INTERSECT or EXCEPT in the WHERE-component
Another issue may arise in case an update on a view can be successfully performed. More specifically in case rows are inserted or updated through an updatable view, there is the chance that such rows no longer satisfy the view definition afterwards. In other words, the rows cannot be retrieved to the view anymore. The WITH CHECK option allows to avoid such “undesired” effects by checking the update and insert statements for conformity with the view definition.