Restrictions on Views

Because a view is not really a table, it cannot be indexed, and it cannot be the object of such statements as ALTER TABLE and RENAME TABLE. You cannot rename the columns of a view with RENAME COLUMN. To change anything about the definition of a view, you must drop the view and re-create it.

Because it must be merged with the user's query, the SELECT statement on which a view is based cannot contain the following clauses or keywords:

INTO TEMP

The user's query might contain INTO TEMP; if the view also contains it, the data would not know where to go.

ORDER BY

The user's query might contain ORDER BY. If the view also contains it, the choice of columns or sort directions could be in conflict.

A SELECT statement on which you base a view can contain the UNION keyword. In such cases, the database server stores the view in an implicit temporary table where the unions are evaluated as necessary. The user's query uses this temporary table as a base table.

When the Basis Changes

The tables and views on which you base a view can change in several ways. The view automatically reflects most of the changes.

When you drop a table or view, any views in the same database that depend on it are automatically dropped.

The only way to alter the definition of a view is to drop and re-create it. Therefore, if you change the definition of a view on which other views depend, you must also re-create the other views (because they all are dropped).

When you rename a table, any views in the same database that depend on it are modified to use the new name. When you rename a column, views in the same database that depend on that table are updated to select the proper column. However, the names of columns in the views themselves are not changed. For an example, recall the following view on the customer table:

CREATE VIEW name_only AS
   SELECT customer_num, fname, lname FROM customer

Now suppose that you change the customer table in the following way:

RENAME COLUMN customer.lname TO surname

To select last names of customers directly, you must now select the new column name. However, the name of the column as seen through the view is unchanged. The following two queries are equivalent:

SELECT fname, surname FROM customer

SELECT fname, lname FROM name_only

When you drop a column to alter a table, views are not modified. If views are used, error -217 (Column not found in any table in the query) occurs. The reason views are not modified is that you can change the order of columns in a table by dropping a column and then adding a new column of the same name. If you do this, views based on that table continue to work. They retain their original sequence of columns.

The database server permits you to base a view on tables and views in external databases. Changes to tables and views in other databases are not reflected in views. Such changes might not be apparent until someone queries the view and gets an error because an external table changed.