Use the MODIFY clause to change the data type, length, or default value of a column, to allow or disallow NULL values in a column, or to reset the serial counter of a SERIAL or SERIAL8 column.

MODIFY Clause:
 
                .-,------------------------.
                V                          |
|--MODIFY--+-(----| Modify Column Clause |-+--)-+---------------|
           '-| Modify Column Clause |-----------'
 
Modify Column Clause:
 
                         (1)
|--column--| Data Type |-------+-------------------------+------>
                               |                    (2)  |
                               '-| DEFAULT Clause |------'
 
>--+------------------------------------------+-----------------|
   |                                     (3)  |
   '-| Single-Column Constraint Format |------'
 

1. See page Data Type
2. See page DEFAULT Clause
3. See page Single-Column Constraint Format

In Extended Parallel Server, you cannot use the MODIFY clause if the table has a dependent GK index.

You cannot change the data type of a column to a COLLECTION or a ROW type.

When you modify a column, all attributes previously associated with the column (that is, default value, single-column check constraint, or referential constraint) are dropped. When you want certain attributes of the column to remain, such as PRIMARY KEY, you must re-specify those attributes.

For example, if you are changing the data type of an existing column, quantity, to SMALLINT, but you want to keep the default value (in this case, 1) and the NOT NULL column attribute, you can issue this statement:

ALTER TABLE items MODIFY (quantity SMALLINT DEFAULT 1 NOT NULL)

When you change the data type of a column, the database server does not perform the modification in place. The next example (for Dynamic Server only) changes a VARCHAR(15) column to an LVARCHAR(3072) column:

ALTER TABLE stock MODIFY (description LVARCHAR(3072))

When you modify a column that has column constraints associated with it, the following constraints are dropped:

• All single-column constraints are dropped.
• All referential constraints that reference the column are dropped.
• If the modified column is part of a multiple-column primary-key or unique constraint, all referential constraints that reference the multiple columns also are dropped.

For example, if you modify a column that has a unique constraint, the unique constraint is dropped. If this column was referenced by columns in other tables, those referential constraints are also dropped. In addition, if the column is part of a multiple-column primary-key or unique constraint, the multiple-column constraints are not dropped, but any referential constraints placed on the column by other tables are dropped.

For another example, suppose that a column is part of a multiple-column primary-key constraint. This primary key is referenced by foreign keys in two other tables. When this column is modified, the multiple-column primary-key constraint is not dropped, but the referential constraints placed on it by the two other tables are dropped.

The characteristics of the object you are attempting to modify can affect how you handle your modifications.