Modifying Views

Modifying Views

In limited conditions it is possible to carry out an insertion, deletion, or update to a view. In the beginning, this idea makes no sense at all, since the view does not exist the way a base table (stored relation) does. What could it mean, say, to insert a new tuple into a view? Where would the tuple go, and how would the database system remember that it was supposed to be in the view?

For many views, the answer is simply "you can't do that". On the other hand, for sufficiently simple views, called updatable views, it is possible to translate the modification of the view into an equivalent modification on a base table, and the modification can be done to the base table instead. SQL provides a formal definition of when modifications to a view are allowed. The SQL rules are complicated, but roughly, they allow modifications on views that are defined by selecting (using SELECT, not SELECT DISTINCT) some attributes from one relation R (which may itself be an updatable view). Two important technical points:

●  The WHERE clause must not involve R in a subquery.

●  The list in the SELECT clause must contain enough attributes that for every tuple inserted into the view, we can fill the other attributes out with NULL values or the proper default and have a tuple of the base relation that will yield the inserted tuple of the view.

Example 1 :  Assume we try to insert into view ParamountMovie of "View Definitions" Example 1 a tuple like:

INSERT INTO ParamountMovie
VALUES( 'Star Trek', 1979) ;

View ParamountMovie almost meets the SQL updatability conditions, since the view asks only for some components of some tuples of one base table:

Movie(title, year, length, inColor, studioName, producerC#)

The only problem is that since attribute studioName of Movie is not an attribute of the view, the tuple we insert into Movie would have NULL rather than 'Paramount' as its value for studioName. That tuple does not meet the condition that its studio be Paramount.

Thus, to make the view ParamountMovie updatable, we shall add attribute studioName to its SELECT clause, even though it is obvious to us that the studio name will be Paramount. The revised definition of view ParamountMovie is:

CREATE VIEW ParamountMovie AS
SELECT studioName, title, year
FROM Movie
WHERE studioName = 'Paramount';

Then, we write the insertion into updatable view ParamountMovie as:

INSERT INTO ParamountMovie
VALUES('Paramount' , 'Star Trek', 1979);

In order to effect the insertion, we invent a Movie tuple that yields the inserted view tuple when the view definition is applied to Movie. For the specific insertion above, the studioName component is 'Paramount', the title component is 'Star Trek', and the year component is 1979.

The other three attributes that do not appear in the view - length, inColor, and producerC# - certainly exist in the inserted Movie tuple. On the other hand, we cannot infer their values. As a result, the new Movie tuple must have in the components for each of these three attributes the appropriate default value: either NULL or some other default that was declared for an attribute. For instance, if the default value 0 was declared for attribute length, but the other two use NULL for the default, then the resulting inserted Movie tuple would be:

movie tuple

We may also delete from an updatable view. The deletion, like the insertion, is passed through to the underlying relation R and causes the deletion of every tuple of R that gives rise to a deleted tuple of the view.

Example 2 :  Assume we wish to delete from the updatable ParamountMovie view all movies with "Trek" in their titles. We may issue the deletion statement

DELETE FROM ParamountMovie
WHERE title LIKE '%Trek%';

This deletion is translated into an equivalent deletion on the Movie base table: the only difference is that the condition defining the view ParamountMovie is added to the conditions of the WHERE clause.

WHERE title LIKE '%Trek%' AND studioName = 'Paramount';

Why Some Views Are Not Updatable

is the resulting delete statement.

Likewise, an update on an updatable view is passed through to the underlying relation. The view update thus has the effect of updating all tuples of the underlying relation that give rise in the view to updated view tuples.

Example 3 :  The view update

UPDATE ParamountMovie
SET year = 1979
WHERE title = 'Star Trek the Movie';

is turned into the base-table update

SET year = 1979
WHERE title = 'Star Trek the Movie'  AND
studioName = 'Paramount';

A final kind of modification of a view is to delete it altogether. This modification may be done whether or not the view is updatable. A typical DROP statement is

DROP VIEW ParamountMovie;

Note that this statement deletes the definition of the view, so we may no longer make queries or issue modification commands involving this view. Though dropping the view does not affect any tuples of the underlying relation Movie. In contrast,


would not only make the Movie table go away. It would also make the view ParamountMovie unusable, since a query that used it would indirectly refer to the nonexistent relation Movie.