Modifying Schema, View, Table, and Routine Definitions › Maintaining Tables › Changing Columns and Their Attributes

Changing Columns and Their Attributes

ALTER TABLE Statement

With the ALTER TABLE statement, you can make the following column changes:

• Add a column
• Change a column’s data type or null attribute
• Drop or change a column’s default clause
• Rename a column
• Drop a column

For instructions on using the ALTER TABLE statement, see the SQL Reference Guide.

Considerations

Add a Column

The definition of the table is updated to include the new column definition, and the new column becomes the last column in the table. Table rows are not updated as part of the ALTER TABLE processing; instead, the column is added to an existing row only when that row is next updated.

When adding a column, if the table is not empty, you much supply a default value for the added column. You do this in one of the following ways:

• Specify that the column is to have a default value. All existing rows are then considered to have the default value for the new column.
• Allow the column to have a null value. All existing rows are then considered to have a null value for the column.

Note: For more information about choosing a value, see the SQL Reference Guide.

Compressed Rows

If a new column in a compressed table will be used as an index key or as a referencing column, consider placing the column near the front of the table. Otherwise, the compression potential of the table will be greatly reduced.

To do this, the table must be dropped and re-added with a new column order. When you put the rows back into the table, make sure the data is in the new column order.

Effect on Programs and View Definitions

Adding a column to a table does not impact existing programs or view definitions except under the following circumstances:

• If your host language programs include SELECT * from the table, they will receive runtime errors because of the added column.
• If a view definition includes a SELECT * from the affected table, it becomes invalid and must be dropped and recreated.

Add a Default to a Column

Allowing a column to have a default value affects only the table’s definition; existing table rows are not affected.

Remove a Column’s Default

If the table is populated and the column does not allow null values, every existing row must contain a value in the changed column. To ensure this, each row is accessed and updated if it does not contain a value for the column.

Rename a Column

A column that is named in a check constraint or a view cannot be renamed.

The definition of all referential constraints, sort keys, CALC keys and indexes in which the column participates are updated to show the new column name.

Drop a Column

Every row in the table is updated to remove the column value.

If a column is named in a check constraint or is part of the CALC key of a populated table, you cannot drop the column.

If you do not specify CASCADE, the column must not be one of the following types of columns:

• A column in a CALC key
• A referenced or foreign key column in a referential constraint
• An indexed column
• A sort column of a linked constraint
• Named in a view

If you specify CASCADE, how the column is used determines what other items are dropped:

• Dropping a CALC key column also drops the CALC key
• Dropping a referenced or foreign key column in a referential constraint also drops the constraint
• Dropping an indexed column also drops the index
• Dropping a sort column of a linked constraint also drops the constraint
• Dropping a column named in a view also drops the view

Change a Column’s Null Attribute

The following situations apply when you change a column's null attribute:

• When the column is part of a CALC key of a populated table, or is a referenced column in a constraint, the ALTER statement fails.
• When you change a null attribute, every row in the table is updated to add or remove the null attribute byte for that column.
• When the changed column is a sort column, every index and linked indexed constraint is automatically rebuilt.
• When disallowing nulls and the value of the column is null for a row in the table, the ALTER statement fails.

Change a Column’s Data Type

The following situations apply when you change a column's data type:

• When the column is part of a CALC key of a populated table, or is a referenced column in a constraint, the ALTER statement fails.
• When changing a column’s data type, the new data type you enter must be compatible for assignment with the original data type.
• Every row in the table is restructured to convert the column value to the new type. This might involve increasing or decreasing the length of the row.
• The ALTER statement will fail if a loss of data (such as truncation of a non-blank character or numeric overflow) would occur as part of the conversion.
• When you change data type, every index and linked indexed constraint in which the column is a sort column is rebuilt.

Maximum Row Length

Adding a column to a table or changing a column's attributes might increase the length of the table row beyond the maximum allowed.

For compressed tables, the maximum is 32760. If the new column would cause this to be exceeded, the column cannot be added to the table; instead, consider creating a second table to hold the additional information.

For uncompressed tables, the maximum depends on the page size of the area in which the table resides. If the new column would cause the length of the row to be greater than (page size - 40), then do one of the following:

• Use the EXPAND PAGE utility statement to increase the page size of the areas.

Note: For more information about EXPAND PAGE, see the Utilities Guide.

• Compress the table.
• Create a second table to hold the new or expanded information.

Note: The maximum length of an uncompressed row can be as much as (page size - 40); however, it is recommended that row lengths be no more than 30% of the size of the page.

Expanding Space in an Area

If an area is becoming full, consider expanding its space before increasing the length of table rows. The chapter “Modifying Physical Database Definitions” describes methods you can use to expand an area.