When the basis of the view 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 that table or view 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 that table 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 correct 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 surnames 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 column of the same name. Views based on that table continue to work and they retain their original sequence of columns.

You can 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. For example, if a column in a remote object that is included in a view is altered to have a different type, you must drop and re-create the view.