ALTER TABLE Statement Changes for Columns

SQL › Alter and Drop Column Support › ALTER TABLE Statement Changes for Columns

ALTER TABLE Statement Changes for Columns

New parameters were added to the ALTER TABLE statement to let you change attributes and drop columns.

For more information about the full ALTER TABLE statement, see the SQL Reference Guide.

Syntax

The following diagram shows the enhanced ALTER TABLE syntax:

►►─ ALTER TABLE ─┬─────────────────┬─ table-identifier ───────────────►
                 └─ schema-name ─.─┘
 ►────┬─ . . . ──────────────────────────────────────────────┬───────►◄
      ├─ ADD ───┬──────────┬─ column-definition ─────────────┤
      │         └─ COLUMN ─┘                                 │
      │                         ┌──────── , ──────────┐     │
      ├─ ADD ───┬──────────┬─ (─▼─ column-definition ┴─)─────┤
      │         └─ COLUMN ─┘                                 │
      ├─ ALTER ─┬──────────┬─ column-alteration ─────────────┤
      │         └─ COLUMN ─┘                                 │
      ├─ DROP ──┬──────────┬─ column-name ─┬───────────┬─────┤
      │         └─ COLUMN ─┘               └─ CASCADE ─┘     │
      └─ RENAME ┬──────────┬ column-name TO new-column-name ─┘
                └─ COLUMN ─┘

Expansion of column-alteration

►►────column-name ───┬─ SET ─┬─ DATA TYPE ─ data-type ────┬───┬──►◄
                     │       ├─┬─ ALLOW ─┬── NULL ────────┤   │
                     │       │ └─ NOT ───┘                │   │
                     │       └─ WITH DEFAULT ─────────────┘   │
                     └─ DROP DEFAULT ─────────────────────────┘

Parameters

This section describes the parameters that support the ALTER TABLE statement:

ALTER COLUMN column-alteration

Specifies the changes to be made to the attributes of a column.

Expanded syntax for column-alteration is shown after the ALTER TABLE syntax. Descriptions of column-alteration parameters follow the description of ALTER TABLE parameters.

DROP COLUMN column-name

Identifies the column to be removed from the table. Column-name must be the name of a column in the table.

Note: You cannot drop columns that are part of a CALC key of a populated table or that are named in a check constraint.

CASCADE

Drops the following entities:

The CALC key if it includes the column.
All referential constraints in which the named column is a referenced or foreign key column.
All linked constraints in which the named column is a sort column.
All indexes in which the named column is an indexed column.
All views in which the column is named.

If CASCADE is not specified, the column must not participate in a referential constraint or index, or be named in a view.

RENAME COLUMN column-name

Identifies the column name to be changed. Column-name must be the name of a column in the table.

Note: You cannot rename a column if the column is named in a check constraint or in a view.

TO new-column-name

Specifies the new name for the identified column.

Limits: new-column-name must be a 1-32 character value that follows the conventions for SQL identifiers.

Note: It must be distinct from the name of any existing column in the table.

Parameters for Expansion of column-alteration

column-name

Identifies the column whose attributes are to be changed. Column-name must be the name of a column in the table.

DATA TYPE data-type

Defines the new data type for the named column. The specified data type must be compatible for assignment with the column’s existing data type.

Note: For expanded data-type syntax, see the section “Expansion of Data-type” in the SQL Reference Guide.

You cannot change the data type of a column that is part of a CALC key of a populated table or that is a referenced or foreign key column in a constraint.

ALLOW NULL

Indicates that the column can contain null values.

You cannot change the null attribute of a column that is part of a CALC key of a populated table or a referenced key.

NOT NULL

Indicates that the column cannot contain null values.

You cannot change the null attribute of a column that is part of a CALC key of a populated table or a referenced key.

WITH DEFAULT

Sets the column’s value to a default if no value for the column is specified when a row is inserted.

DROP DEFAULT

Does not set the column’s value to a default when a row is inserted.

The following parameter describes the Default Index enhancement to the ALTER TABLE statement

ADD DEFAULT INDEX

Creates a default index for the named table.

The table must not already have a default index associated with it.

ALTER TABLE Usage for Columns

The following considerations apply when using the Alter and Drop Column parameters to the ALTER TABLE statement:

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.

Example: Alter Table Statement Parameters

The following examples illustrate the Alter and Drop Column Support enhancement to the ALTER TABLE statement parameters:

Add a new column to an existing table:

alter table demo.empl 
      add column phone character(10);

Change a column’s data type:

alter table demo.empl
      alter column city
      set data type varchar(20);

Drop a column using the CASCADE option:

alter table demo.empl
      drop column status cascade;

Rename a column:

alter table demo.empl
      rename column proj_id to project_id;