What does it mean to insert a row of data into a view, delete a row from a view, or update a row of a view? For some views, these operations can obviously be translated into equivalent operations against the source table(s) of the view. For example, consider once again the EASTREPS view, defined earlier in this chapter:
Create a view showing Eastern region salespeople.
CREATE VIEW EASTREPS AS
SELECT *
FROM SALESREPS
WHERE REP_OFFICE IN (11, 12, 13)
This is a straightforward horizontal view, derived from a single source table. As shown in Figure 14-5, it makes sense to talk about inserting a row into this view; it means the new row should be inserted into the underlying SALESREPS table from which the view is derived. It also makes sense to delete a row from the EASTREPS view; this would delete the corresponding row from the SALESREPS table. Finally, updating a row of the EASTREPS view makes sense; this would update the corresponding row of the SALESREPS table. In each case, the action can be carried out against the corresponding row of the source table, preserving the integrity of both the source table and the view.
However, consider the ORD_BY_REP grouped view, as it was defined earlier in the section “Grouped Views”:
Define a view that contains summary order data for each salesperson.
CREATE VIEW ORD_BY_REP (WHO, HOW_MANY, TOTAL, LOW, HIGH, AVERAGE) AS
SELECT REP, COUNT(*), SUM(AMOUNT), MIN(AMOUNT), MAX(AMOUNT),
AVG(AMOUNT)
FROM ORDERS
GROUP BY REP
There is no one-to-one correspondence between the rows of this view and the rows of the underlying ORDERS table, so it makes no sense to talk about inserting, deleting, or updating rows of this view. The ORD_BY_REP view is not updateable; it is a read-only view.
The EASTREPS view and the ORD_BY_REP view are two extreme examples in terms of the complexity of their definitions. There are views more complex than EASTREPS where it still makes sense to update the view, and there are views less complex than ORD_BY_REP where updates do not make sense. In fact, which views can be updated and which cannot has been an important relational database research problem over the years.
1. View Updates and the ANSI/ISO Standard
The ANSI/ISO SQL1 standard specifies the views that must be updateable in a database that claims conformance to the standard. Under the standard, a view can be updated if the query that defines the view meets all of these restrictions:
- DISTINCT must not be specified; that is, duplicate rows must not be eliminated from the query results.
- The FROM clause must specify only one updateable table; that is, the view must have a single source table for which the user has the required privileges. If the source table is itself a view, then that view must meet these criteria.
- Each select item must be a simple column reference; the select list cannot contain expressions, calculated columns, or column functions.
- The WHERE clause must not include a subquery; only simple row-by-row search conditions may appear.
- The query must not include a GROUP BY or a HAVING clause.
The basic concept behind the restrictions is easier to remember than the rules themselves.
For a view to be updateable, the DBMS must be able to trace any row of the view back to its source row in the source table. Similarly, the DBMS must be able to trace each individual column to be updated back to its source column in the source table.
If the view meets this test, then it’s possible to define meaningful INSERT, DELETE, and UPDATE operations for the view in terms of the source table(s).
2. View Updates in Commercial SQL Products
The SQL1 standard rules on view updates are very restrictive. Many views can be theoretically updated but do not satisfy all of the restrictions. In addition, some views can support some of the update operations but not others, and some views can support updates on certain columns but not others. Most commercial SQL implementations have view update rules that are considerably more permissive than the SQL1 standard. For example, consider this view:
Create a view showing the sales, quota, and the difference between the two for each salesperson.
CREATE VIEW SALESPERF (EMPL_NUM, SALES, QUOTA, DIFF) AS
SELECT EMPL_NUM, SALES, QUOTA, (SALES – QUOTA)
FROM SALESREPS
The SQL1 standard disallows all updates to this view because its fourth column is a calculated column. However, note that each row in the view can be traced back to a single row in the source table (SALESREPS). For this reason, DB2 (and several other commercial SQL implementations) allows DELETE operations against this view. Further, DB2 allows UPDATE operations on the EMPL_NUM, SALES, and QUOTA columns because they are directly derived from the source table. Only the DIFF column cannot be updated. DB2 does not allow the INSERT statement for the view because inserting a value for the DIFF column would be meaningless.
The specific rules that determine whether a view can be updated vary from one brand of DBMS to another, and they are usually fairly detailed. Some views, such as those based on grouped queries, cannot be updated by any DBMS because the update operations simply do not make sense. Other views may be updateable in one brand of DBMS, partially updateable in another brand, and not updateable in a third brand. The SQL2 standard recognized this and includes a broader definition of updateable views along with considerable latitude for variation among DBMS brands. The best way to find out about updateability of views in your particular DBMS is to consult the user’s guide or experiment with different types of views.
3. Checking View Updates (check option)
If a view is defined by a query that includes a WHERE clause, only rows that meet the search condition are visible in the view. Other rows may be present in the source table(s) from which the view is derived, but they are not visible through the view. For example, the EASTREPS view, described in the “Horizontal Views” section earlier in this chapter, contains only those rows of the SALESREPS table with specific values in the REP_OFFICE column:
Create a view showing Eastern region salespeople.
CREATE VIEW EASTREPS AS
SELECT *
FROM SALESREPS
WHERE REP_OFFICE IN (11, 12, 13)
This is an updateable view for most commercial SQL implementations. You can add a new salesperson with this INSERT statement:
INSERT INTO EASTREPS (EMPL_NUM, NAME, REP_OFFICE, AGE, SALES)
VALUES (113, ‘Jake Kimball’, 11, 43, 0.00)
The DBMS will add the new row to the underlying SALESREPS table, and the row will be visible through the EASTREPS view. But consider what happens when you add a new salesperson with this INSERT statement:
INSERT INTO EASTREPS (EMPL_NUM, NAME, REP_OFFICE, AGE, SALES)
VALUES (114, ‘Fred Roberts’, 21, 47, 0.00)
This is a perfectly legal SQL statement, and the DBMS will insert a new row with the specified column values into the SALESREPS table. However, the newly inserted row doesn’t meet the search condition for the view. Its REP_OFFICE value (21) specifies the Los Angeles office, which is in the Western region. As a result, if you run this query immediately after the INSERT statement:
the newly added row doesn’t show up in the view. The same thing happens if you change the office assignment for one of the salespeople currently in the view. This UPDATE statement:
UPDATE EASTREPS
SET REP_OFFICE = 21
WHERE EMPL_NUM = 104
modifies one of the columns for Bob Smith’s row and immediately causes it to disappear from the view. Of course, both of the vanishing rows show up in a query against the underlying table:
The fact that the rows vanish from the view as a result of an INSERT or UPDATE statement is disconcerting, at best. You probably want the DBMS to detect and prevent this type of INSERT or UPDATE from taking place through the view. SQL allows you to specify this kind of integrity checking for views by creating the view with a check option. The check option is specified in the CREATE VIEW statement, as shown in this redefinition of the EASTREPS view:
CREATE VIEW EASTREPS AS
SELECT *
FROM SALESREPS
WHERE REP_OFFICE IN (11, 12, 13)
WITH CHECK OPTION
When the check option is requested for a view, SQL automatically checks each INSERT and each UPDATE operation for the view to make sure that the resulting row(s) meet the search criteria in the view definition. If an inserted or modified row would not meet the condition, the INSERT or UPDATE statement fails, and the operation is not carried out.
The SQL2 standard specifies one additional refinement to the check option: the choice of CASCADED or LOCAL application of the check option. This choice applies when a view is created, and its definition is based not on an underlying table, but on one or more other views. The definitions of these underlying views might, in turn, be based on still other views, and so on. Each of the underlying views might or might not have the check option specified.
If the new view is created WITH CASCADED CHECK OPTION, any attempt to update the view causes the DBMS to go down through the entire hierarchy of view definitions on which it is based, processing the check option for each view where it is specified. If the new view is created WITH LOCAL CHECK OPTION, then the DBMS checks only that view; the underlying views are not checked. The SQL2 standard specifies CASCADED as the default, if the WITH CHECK OPTION clause is used without specifying LOCAL or CASCADED.
It’s probably clear from the discussion that the check option can add significant overhead to the INSERT and UPDATE operations, especially if you are updating a view that is defined based on several layers of underlying view definitions. However, the check option plays an important role to ensure the integrity of the database. After all, if the update was intended to apply to data not visible through the view or to effectively switch a row of data from one view to another, then logically, the update should be made through an underlying view or base table. When you create an updateable view as part of a security scheme, it’s almost always a good idea to specify the check option. It prevents modifications made through the view from affecting data that isn’t accessible to the user in the first place.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.