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

Restrictions:

You cannot alter a column to specify a default value if the table is referenced by a view.
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:

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

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

ALTER TABLE MYEMP ALTER COLUMN JOB SET DEFAULT 'PENDING'

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

ALTER TABLE MYEMP ALTER COLUMN JOB DROP DEFAULT