ALTER TABLE

The ALTER TABLE statement modifies existing tables by:

Adding one or more columns to a table.
Changing the length of one or more VARCHAR columns.

Invocation

This statement can be used in an application using DB2^® CLI functions or issued through the DB2eCLP application.

Syntax


>>-ALTER TABLE--table-name-------------------------------------->

     .----------------------------------------.       
     V                                        |       
>--+---ADD--+--------+--| column-definition |-+---+------------><
   |        '-COLUMN-'                            |   
   | .------------------------------------------. |   
   | V                                          | |   
   '---ALTER--+--------+--| column-alteration |-+-'   
              '-COLUMN-'                              

column-definition:

|--column-name--| data-type |--| column-options |---------------|

column-alteration:

|--column-name--SET DATA TYPE VARCHAR--(--integer--)------------|

column-options:

   .---------------------------------------------.   
   V                                             |   
|----+-----------------------------------------+-+--------------|
     +-NOT NULL--------------------------------+     
     +-+-REFERENCES--table-name-------+--------+     
     | '-CHECK--(--check-condition--)-'        |     
     '-DEFAULT--+-constant-------------------+-'     
                +-datetime-special-register--+       
                '-cast-function-(-constant-)-'

Description

table-name

Specifies the table to alter. The name can be up to 128 bytes long. The name must identify a table in the catalog.

You must use delimited identifiers (with double quotation marks) when a table name contains blanks or special characters.

The table name can include Double Byte Character Set characters.

Restriction: The system-created data files that correspond to tables created and named by user names do not distinguish between upper and lowercase characters. For example, the data file for a table named TB is named DSY_TB. The data file for a table named "tb" is also DSY_TB. Therefore, to ensure data integrity, it is strongly recommended that you do not name a table using a series of characters identical, except for character case, to an existing table name.

column-name

Specifies a column of the table. The name can be up to 128 bytes long. The name cannot be qualified and the same name cannot be used for more than one column of the table.

Column names are converted to uppercase before being stored in the catalog. You can use delimited identifiers (with double quotation marks) to prevent such conversion. You must use delimited identifiers when a column name contains blanks or special characters.

The column name can include DBCS characters.

data-type

Is one of the types supported by the CREATE TABLE statement.

column-options

Defines additional options related to columns of the table.

NOT NULL

Prevents the column from containing null values.

If NOT NULL is not specified, the column can contain null values, and its default value is either the null value or the value provided by the DEFAULT clause.

REFERENCES table-name

See the description of REFERENCES in the next topic.

CHECK (check-condition)

See the description of CHECK in the next topic.

DEFAULT

Provides a default value in the event that a value is not supplied on an INSERT statement.

Omission of DEFAULT from a column-definition results in the use of the null value as the default for the column. If such a column is defined NOT NULL, then the column does not have a valid default.

constant

Specifies a constant as the default value for the column. The specified constant must:

Represent a value that could be assigned to the column
Not have non-zero digits beyond the scale of the column data type if the constant is a decimal constant.
Example: 1.234 cannot be the default for a DECIMAL(5,2) column.

datetime-special-register

Specifies the value of the datetime special register (CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP) at the time of INSERT as the default for the column. The data type of the column must be the data type that corresponds to the special register specified (for example, data type must be DATE when CURRENT DATE is specified).

cast-function

Specifies the cast-function as the default value for the column. This form of a default value can only be used with columns that are defined as a BLOB or datetime (DATE, TIME or TIMESTAMP) data type.

constant: Specifies a constant as the argument. The constant must conform to the rules of a constant for the data type. If the cast-function is BLOB, the constant must be a string constant.

REFERENCES table-name

The table specified in a REFERENCES clause must identify a base table that is described in the catalog, but must not identify a catalog table.

A referential constraint is a duplicate if its foreign key is the same as the foreign key table of a previously specified referential constraint.

In the following discussion, let T2 denote the identified parent table, and let T1 denote the table being created.

The specified foreign key must have the same number of columns as the parent key of T2 and the description of the nth column of the foreign key must be comparable to the description of the nth column of that parent key. Datetime columns are not considered to be comparable to string columns for the purposes of this rule. Foreign keys are not enforced by DB2 Everyplace.

CHECK (check-condition)

Defines a check constraint. A check-condition is a search condition. A column reference must be a column of the table being created. Values being inserted or updated into a table must satisfy any check constraints.

If a check constraint is specified as part of a column definition, then a column reference can be made only to the same column. Check constraints specified as part of a table definition can have column references identifying columns previously defined in the CREATE TABLE statement. Check constraints are not checked for inconsistencies, duplicate conditions, or equivalent conditions. Therefore, contradictory or redundant check constraints can be defined.

The check-condition "IS NOT NULL" can be specified, however it is recommended that nullability be enforced directly using the NOT NULL attribute of a column. For example, CHECK (salary + bonus > 30000) is accepted if salary is set to NULL, because CHECK constraints must be either satisfied or unknown and in this case salary is unknown. However, CHECK (salary IS NOT NULL) would be considered false and a violation of the constraint if salary is set to NULL.

Check constraints are enforced when rows in the table are inserted or updated.

All check constraints defined in a CREATE TABLE statement are combined and stored in the system catalog. DB2 Everyplace^® has a limit of 32767 bytes for this combined check constraint.

Rules

The actual total of byte counts of a row must not be greater than 65,536.
Columns with the BLOB data type cannot have check, default, referential, or foreign key constraints (SQLSTATE 42962).
Columns with the BLOB data type cannot be used in the primary key of a CREATE TABLE statement.

Notes

System tables cannot be altered by the user. Any attempt will result in SQLSTATE 42832.
Adding a primary key column is not supported. Any attempt will result in SQLSTATE 42601. You can however, add columns with default values, check, and referential constraints.
Like the CREATE TABLE statement, an ALTER TABLE statement can be rolled back in a transaction.
When altering the length of a column, the column to be altered must be a VARCHAR type and the specified length must be equal to or greater than the existing column length; otherwise, SQLSTATE 42837.
Combining ADD and ALTER COLUMN clauses in an ALTER TABLE statement will result in a syntax error.
A REORG operation can get invoked after an ALTER TABLE ADD COLUMN statement has been executed successfully. This depends on the table size and the user table reorg threshold level.
Columns should be created using uppercase names. Mixed case and lowercase names might cause errors to occur with some languages.

Byte counts for data: The following list contains the byte counts of columns by data type. This count might change with each release of DB2 Everyplace. Each record also includes information about NULLs. NULL information requires 4 bytes for each group of 32 columns. A NULL value still uses the fixed size column size.

Data type	Column byte count
INTEGER	4
SMALLINT	4
DECIMAL(n, m)	4 – 20
CHAR(n)	n+1
VARCHAR(n)	i+5 where i is the actual length
BLOB	i+4 where i is the actual length
DATE	4
TIME	4
TIMESTAMP	12

Example

The following example shows some of the ways that ALTER TABLE can be used.

CREATE TABLE t1 (c1 INT PRIMARY KEY NOT NULL, c2 VARCHAR(10));

CREATE TABLE t2 (c1 DATE);

CREATE TABLE t3 (c1 TIME, c2 INT PRIMARY KEY NOT NULL); 

ALTER TABLE t2 ADD COLUMN c2 INT REFERENCES t1;

ALTER TABLE t2 ADD c3 INT CHECK (c3 > 1) DEFAULT 10 ADD c4 DECIMAL(5,2) NOT NULL;

ALTER TABLE t2 ADD c5 TIMESTAMP DEFAULT CURRENT TIMESTAMP ADD COLUMN c6 CHAR(20) 
DEFAULT 'xyz' ADD c7 INT REFERENCES t3;

CREATE TABLE t4 (c1 INT, c2 VARCHAR(2), c3 VARCHAR(10));

ALTER TABLE t1 ALTER c2 SET DATA TYPE VARCHAR(20) 
ALTER c3 SET DATA TYPE VARCHAR(100);