Specifying a default value when altering a column

You can use the ALTER TABLE statement to add, change, or remove the default value for a column.

About this task

Begin general-use programming interface information.

The following restrictions apply:

You cannot alter a column to specify a default value if one of the following conditions exists:
- The table is referenced by a view.
- The column was not part of the original table definition, the table exists in a table space with the DEFINE YES attribute, and the table space was not reorganized after the column was added.
If the column is part of a unique constraint or unique index, the new default to a value should not be the same as a value that already exists in the column.
The new default value applies only to new rows.

To alter the default value for a column, use one of the following approaches:

To set the default value, issue the following statement:
```
ALTER TABLE table-name ALTER COLUMN column-name 
SET default-clause
```
You can use this statement to add a default value for a column that does not already have one, or to change the existing default value.
To remove the default value without specifying a new one, issue the following statement:
```
ALTER TABLE table-name ALTER COLUMN column-name 
DROP DEFAULT
```

For example, suppose that table MYEMP is defined as follows:

CREATE TABLE MYEMP LIKE EMP

Example: setting a default

Use the following statement to assign a default value to column JOB:

ALTER TABLE MYEMP ALTER COLUMN JOB SET DEFAULT 'PENDING'

Example: dropping a default

Use the following statement to drop the default value from column JOB:

ALTER TABLE MYEMP ALTER COLUMN JOB DROP DEFAULT

End general-use programming interface information.