Progress
SQL-89
Guide and Reference


Creating Views

The CREATE VIEW statement creates a view based on one or more tables, views, or both. The CREATE VIEW statement has the following syntax.

SYNTAX
CREATE VIEW view-name [ ( column [ , column ] ... ) ]
  AS SELECT-statement [ WITH CHECK OPTION ] 

The view name you specify must be unique among all existing table names and view names in the database. A view of a non-Progress database created with CREATE VIEW is stored in the schema holder, not in the non-Progress database.

The SELECT in the CREATE VIEW statement determines the tables and other views used to create the view and the columns that appear in the view. The view column names can be the same as or different from the table column names. To use the table column names as the view column names, omit the column-list. To use different column names in the view, enter the column names separated by commas. If you are using different column names, the names cannot contain any characters invalid in SQL. However, you must supply a column-list if there are any expressions or duplicate column names in the select list.

The following example creates a view that includes only customers from New York.

CREATE VIEW ny_view
  AS SELECT * FROM Cust_Table
    WHERE State = ’NY’. 

The keywords WITH CHECK OPTION ensure that all updates to the view (for example, inserting a new row or updating an existing row) satisfy the view-defining condition. The view-defining condition is located in the WHERE clause of the SELECT statement in the view definition. The error message “VIEW INSERT OR UPDATE VIOLATES THE VIEW DEFINITION” occurs at run time if a view update is rejected.

The following example shows how the WITH CHECK OPTION clause selects only those customers from Massachusetts.

CREATE VIEW view_mass
  AS SELECT Name, City, State FROM Cust_Table
    WHERE State = ’MA’ WITH CHECK OPTION. 

Without the WITH CHECK OPTION clause, you can insert a row or update an existing row through this view with a value other than MA for the state column. Those rows would not appear in the view (although they would exist in the database). If you specify the WITH CHECK OPTION option, such inserts or updates are rejected.

The WITH CHECK OPTION option is especially useful when a view serves as a security feature. For example, assume a security administrator creates a view for clerical use. You must enter all new customers through this view.

CREATE VIEW view_num
  AS SELECT * FROM Customer
    WHERE Cust_Num BETWEEN 1000 AND 9999
    WITH CHECK OPTION. 

The WITH CHECK OPTION option enforces that customer numbers must fall in the range 1000-9999. The WITH CHECK OPTION option prohibits you from changing an existing number or inserting a new number if it is outside the range.

NOTE

You can nest views in a view definition; you can define a view in terms of another view. If the top-level view contains the WITH CHECK OPTION option, then the WHERE clauses for all the nested views are enforced for INSERT and UPDATE. If you do not specify a WITH CHECK OPTION option for the top-level view, then the WHERE clauses for any underlying view with the WITH CHECK OPTION option are enforced instead.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095