Another Look At Views

In Chapter 4, "Working with Databases and Database Objects," we saw that views are used to provide a different way of looking at the data stored in one or more base tables. Essentially, a view is a named specification of a result table that is populated whenever the view is referenced in an SQL statement. (Each time a view is referenced, a query is executed and the results are retrieved from the underlying table and returned in a table-like format.) Like base tables, views can be thought of as having columns and rows. And in most cases, data can be retrieved from a view the same way it can be retrieved from a table.

Views can be created by executing the CREATE VIEW SQL statement. The basic syntax for this statement is:

 CREATE VIEW [ViewName] <([ColumnName] ,...)> AS [SELECTStatement] <WITH <LOCAL | CASCADED> CHECK OPTION> 

where:

ViewName	Identifies the name that is to be assigned to the view to be created.
ColumnName	Identifies the names of one or more columns that are to be included in the view to be created. If a list of column names is specified, the number of column names provided must match the number of columns that will be returned by the SELECT statement used to create the view. (If a list of column names is not provided, the columns of the view will inherit the names that are assigned to the columns returned by the SELECT statement used to create the view.)
SELECTStatement	Identifies a SELECT SQL statement that, when executed, will produce data that will populate the view.

Thus, if you wanted to create a view that references specific data values stored in a table named DEPARTMENT and assign it the name DEPT_VIEW, you could do so by executing a CREATE VIEW SQL statement that looks something like this:

 CREATE VIEW dept_view AS SELECT (dept_no, dept_name, dept_size)   FROM department   WHERE dept_size > 25 

The view created by this statement would contain department number, department name, and department size information for each department that has more than 25 people in it.

If the WITH LOCAL CHECK OPTION clause of with the CREATE VIEW SQL statement is specified, insert and update operations performed against the view that is created are validated to ensure that all rows being inserted into or updated in the base table the view refers to conform to the view's definition (otherwise, the insert/update operation will fail). So what exactly does this mean? Suppose a view was created using the following CREATE VIEW statement:

 CREATE VIEW priority_orders AS SELECT * FROM orders WHERE response_time < 4 WITH LOCAL CHECK OPTION 

Now, suppose a user tries to insert a record into this view that has a RESPONSE_TIME value of 6. The insert operation will fail because the record violates the view's definition. Had the view not been created with the WITH LOCAL CHECK OPTION clause, the insert operation would have been successful, even though the new record would not be visible to the view that was used to add it.

If the WITH CASCADED CHECK OPTION clause of the CREATE VIEW SQL statement is specified, the view created will inherit the search conditions of the parent view upon which the view is based and treat those conditions as one or more constraints that are used to validate insert and update operations that are performed against the view. Additionally, every view created that is a child of the view that was created with the WITH CASCADED CHECK OPTION clause specified will inherit those constraints; the search conditions of both parent and child views are ANDed together to form the constraints. To better understand what this means, let's look at an example. Suppose a view was created using the following CREATE VIEW statement:

 CREATE VIEW priority_orders AS SELECT * FROM orders WHERE response_time < 4 

Now, suppose a second view was created using this CREATE VIEW statement:

 CREATE VIEW special_orders AS SELECT * FROM priority_orders WITH CASCADED CHECK OPTION 

If a user tries to insert a record into the SPECIAL_ORDERS view that has a RESPONSE_TIME value of 6, the insert operation will fail because the record violates the search condition of the PRIORITY_ORDERS view's definition (which is a constraint for the SPECIAL_ORDERS view).