Database Management Systems (DBMS) Question Bank 03

Why it is not always possible to perform update, delete and insert operation on top of a view? Can we ALWAYS execute insert, delete and update SQL queries on views

Question:

Explain why it is not always possible to perform SQL UPDATE/DELETE/INSERT statements on top of a view.

Answer:

Updates on views must be translated into one or more updates on base relations. For some views, the translation can be done in an unambiguous way. In that case, updates are allowed. For example, if a view selects tuples from a relation that satisfy a simple condition, then updates on the view can be translated into updates on the base table in a straightforward fashion.

In other cases, however, there may not be one unambiguous translation. For example, if a view joins two tables together and a user wants to delete a tuple from the view, that delete operation can be translated into deleting one or more tuples from one or both of the underlying tables. Depending on how we would implement the delete operation, additional tuples might also disappear from the view, which would be an undesirable side-effect. For such a view, the only reasonable solution is thus to disallow updates.

When it is possible to perform updates on a view, the view is called updatable.

*************************