Cascaded Effects

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.

Table 46. 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 logical file shares a format with the file being altered, and
  • The dropped column is not used as a key field or in select/omit specifications, and
  • That format is not used again in the logical file with another based-on file.
Otherwise, the drop of the column is not allowed.
The drop is allowed, and the column is dropped from the logical file if:
  • The logical file shares a format with the file being altered, and
  • The dropped column is not used as a key field or in select or omit specifications, and
  • That format is not used again in the logical file with another based-on file.
Otherwise, the logical file is dropped.
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.)

Table 47. Cascaded effects of altering a column
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.
  • If the referential constraint is in the defined but check-pending state, the alter is allowed and an attempt is made to put the constraint in the enabled state. (Hence, the index used to satisfy the unique constraint will usually to be rebuilt.)
  • If the referential constraint is in the enabled state, the constraint is placed in the defined and check-pending state.
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.

58.
A column will also be added to a logical file that shares its physical file's format when a column is added to that physical file (unless that format is used again in the logical file with another based-on file).