Adding a column has no cascaded effects to SQL views or most logical files.58 For example, adding a column to a table does not cause the column to be added to any dependent views, even if those views were created with a SELECT * clause.
Dropping or altering a column may cause several cascaded effects. Table 46 lists the cascaded effects of dropping a column.
Operation | RESTRICT Effect | CASCADE Effect |
---|---|---|
Drop of a column referenced by a view | The drop of the column is not allowed. | The view and all views dependent on that view are dropped. |
Drop of a column referenced by a non-view logical file | The drop is allowed, and the column is dropped
from the logical file if:
|
The drop is allowed, and the column is dropped
from the logical file if:
|
Drop of a column referenced in the key of an index | The drop of the index is not allowed. | The index is dropped. |
Drop of a column referenced in a unique constraint | If all the columns referenced in the unique
constraint are dropped in the same ALTER COLUMN statement and the unique constraint
is not referenced by a referential constraint, the columns and the constraint
are dropped. (Hence, the index used to satisfy the constraint is also dropped.)
For example, if column A is dropped, and a unique constraint of UNIQUE (A)
or PRIMARY KEY (A) exists and no referential constraints reference the unique
constraint, the operation is allowed.
Otherwise, the drop of the column is not allowed. |
The unique constraint is dropped as are any referential constraints that refer to that unique constraint. (Hence, any indexes used by those constraints are also dropped). |
Drop of a column referenced in a referential constraint | If all the columns referenced in the referential
constraint are dropped at the same time, the columns and the constraint are
dropped. (Hence, the index used by the foreign key is also dropped). For example,
if column B is dropped and a referential constraint of FOREIGN KEY (A) exists,
the operation is allowed.
Otherwise, the drop of the column is not allowed. |
The referential constraint is dropped. (Hence, the index used by the foreign key is also dropped). |
Drop of a column referenced in a trigger | The drop of the column is not allowed. | The trigger is dropped. |
Drop of a column referenced in an MQT | The drop of the column is not allowed. | The MQT is dropped. |
Table 47 lists the cascaded effects of altering a column. (Alter of a column in the following chart means altering a data type, precision, scale, length, or nullability characteristic.)
Operation | Effect |
---|---|
Alter of a column referenced by a view | The alter is allowed.
The views that are dependent on the table will be recreated. The new column attributes will be used when recreating the views. |
Alter of a column referenced by a non-view logical file | The alter is allowed.
The non-view logical files that are dependent on the table will be recreated. If the logical file shares a format with the file being altered, and that format is not used again in the logical file with another based-on file, the new column attributes will be used when recreating the logical file. Otherwise, the new column attributes will not be used when recreating the logical file. Instead, the current logical file attributes are used. |
Alter of a column referenced in the key of an index. | The alter is allowed. (Hence, the index will usually be rebuilt.) |
Alter of a column referenced in a unique constraint | The alter is allowed. (Hence, the index will
usually be rebuilt.)
If the unique constraint is referenced by a referential constraint, the attributes of the foreign keys no longer match the attributes of the unique constraint. The constraint will be placed in a defined and check-pending state. |
Alter of a column referenced in a referential constraint | The alter is allowed.
|
Alter of a column referenced in a trigger | The trigger is preserved. |
Alter of a column referenced in an MQT | The MQT is recreated to include the new attributes. |
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.