The ALTER TABLE statement adds, drops, or alters a column from an existing table; adds unique, referential, or check constraints; and adds or drops a primary, unique, or foreign key.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
The privileges held by the authorization ID of the statement must include at least one of the following:
The authorization ID of the statement has the ALTER privilege on the table when one of the following is true:
To define a foreign key, the privileges held by the authorization ID of the statement must include at least one of the following on the parent table:
The authorization ID of the statement has the REFERENCES privilege on a table when one of the following is true:
If a user-defined type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:
The authorization ID of the statement has the USAGE privilege on a user-defined type when one of the following is true:
If a user-defined cast function is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:
The authorization ID of the statement has the EXECUTE privilege on a user-defined function when one of the following is true:
>>-ALTER TABLE--table-name-------------------------------------->
.-------------------------------------------------------------------------------------------.
V .-COLUMN-. |
>----------+-ADD--+--------+----column-definition--------------------------------------------+----+>
| .-------------------------------------. |
| .-COLUMN-. V (1) | |
+-ALTER-+--------+--column-name--+-SET------+-DATA TYPE--data-type--+---------+-+-+
| | +-default-clause--------+ | |
| | '-NOT NULL--------------' | |
| | .------------------------. | |
| | V (2) | | |
| '-DROP------+-DEFAULT--+---------+-------------' |
| '-NOT NULL-' |
| .-COLUMN-. .-CASCADE--. |
+-DROP----+--------+--column-name--+----------+-----------------------------------+
| '-RESTRICT-' |
| (3) |
+-ADD-------+-unique-constraint------+--------------------------------------------+
| +-referential-constraint-+ |
| '-check-constraint-------' |
| .-CASCADE--. |
'-DROP--+-PRIMARY KEY-------------------------+---+----------+--------------------'
'--+-FOREIGN KEY-+---constraint-name--' '-RESTRICT-'
+-UNIQUE------+
+-CHECK-------+
'-CONSTRAINT--'
>--------------------------------------------------------------><
column-definition
|---column-name----+-------------------------------------+------>
| .-COLUMN-. |
'-FOR-+--------+--system-column-name--'
>----data-type-------------------------------------------------->
.----------------------------------------------------------------------------.
V |
>--------+----------------------------------------------------------------------+--+>
+-NOT NULL-------------------------------------------------------------+
+-default-clause-------------------------------------------------------+
| (4) |
+-datalink-options-----------------------------------------------------+
'-+------------------------------+---+-+-UNIQUE------+---------------+-'
'-CONSTRAINT--constraint-name--' | '-PRIMARY KEY-' |
+-references-clause-------------+
'-CHECK--(--check-condition--)--'
>---------------------------------------------------------------|
Notes:
|
data-type
|--+---BIGINT-------------------------------------------------------------------------------------------------------+->
+-+-INTEGER-+----------------------------------------------------------------------------------------------------+
| '-INT-----' |
+---SMALLINT-----------------------------------------------------------------------------------------------------+
+--+-DECIMAL-+---+-------------------------------+---------------------------------------------------------------+
| +-DEC-----+ '-(--integer--+-----------+--)--' |
| '-NUMERIC-' '-, integer-' |
+-+-FLOAT--+----------------+-+----------------------------------------------------------------------------------+
| | '-(--integer--)--' | |
| +-REAL----------------------+ |
| | .-PRECISION-. | |
| '-DOUBLE-+-----------+------' |
+----+-BLOB----------------+-----+------------------------+---+------------------+-------------------------------+
| '-BINARY LARGE OBJECT-' '-(--integer--+---+---)--' '-allocate-clause--' |
| +-K-+ |
| '-M-' |
+--+--+-CHARACTER-+---+----------------+-------------------------------------------------+---+-----------------+-+
| | '-CHAR------' '-(--integer--)--' | +-FOR BIT DATA----+ |
| +--+--+-VARCHAR-------------------+---(--integer--)--+---+------------------+---------+ +-FOR SBCS DATA---+ |
| | | '--+-CHARACTER-+---VARYING--' | '-allocate-clause--' | +-FOR MIXED DATA--+ |
| | | '-CHAR------' | | '-CCSID--integer--' |
| | '-LONG VARCHAR------------------------------------' | |
| '----+-CLOB-------------------+-----+------------------------+---+------------------+-' |
| +-CHAR LARGE OBJECT------+ '-(--integer--+---+---)--' '-allocate-clause--' |
| '-CHARACTER LARGE OBJECT-' +-K-+ |
| '-M-' |
+--+----GRAPHIC-----+----------------+---------------------------------+---+-----------------+-------------------+
| | '-(--integer--)--' | '-CCSID--integer--' |
| +--+--+-VARGRAPHIC------+---(--integer--)--+---+------------------+-+ |
| | | '-GRAPHIC VARYING-' | '-allocate-clause--' | |
| | '-LONG VARGRAPHIC-----------------------' | |
| '----DBCLOB------+------------------------+---+------------------+--' |
| '-(--integer--+---+---)--' '-allocate-clause--' |
| +-K-+ |
| '-M-' |
+---DATE---------------------------------------------------------------------------------------------------------+
+---TIME---------------------------------------------------------------------------------------------------------+
+---TIMESTAMP----------------------------------------------------------------------------------------------------+
+---DATALINK--+----------------+---+------------------+---+-----------------+------------------------------------+
| '-(--integer--)--' '-allocate-clause--' '-CCSID--integer--' |
'---distinct-type------------------------------------------------------------------------------------------------'
>---------------------------------------------------------------|
allocate-clause
|---ALLOCATE--(integer)-----------------------------------------|
default-clause
.-WITH-.
|--+------+--DEFAULT--+---------------------------------------------------+->
+-constant------------------------------------------+
+-NULL----------------------------------------------+
+-USER----------------------------------------------+
+-CURRENT_DATE--------------------------------------+
+-CURRENT_TIME--------------------------------------+
+-CURRENT_TIMESTAMP---------------------------------+
'-cast-function-name--(--+-constant----------+---)--'
+-USER--------------+
+-CURRENT_DATE------+
+-CURRENT_TIME------+
'-CURRENT_TIMESTAMP-'
>---------------------------------------------------------------|
datalink-options
.-NO LINK CONTROL--------------------------.
|---LINKTYPE URL--+------------------------------------------+--|
'-FILE LINK CONTROL--+-file-link-options-+-'
'-MODE DB2OPTIONS---'
file-link-options
.-----------------------------------.
V (1) |
|-----+------------------------------+--+-----------------------|
+-INTEGRITY ALL----------------+
+-+-READ PERMISSION FS-+-------+
| '-READ PERMISSION DB-' |
+-+-WRITE PERMISSION FS------+-+
| '-WRITE PERMISSION BLOCKED-' |
+-RECOVERY NO------------------+
'-+-ON UNLINK RESTORE-+--------'
'-ON UNLINK DELETE--'
Notes:
|
unique-constraint
|--+------------------------------+---+-UNIQUE------+--(-------->
'-CONSTRAINT--constraint-name--' '-PRIMARY KEY-'
.-,--------------.
V |
>--------column-name---+--)-------------------------------------|
referential-constraint
(1)
|---+------------------------------+--FOREIGN KEY--------------->
'-CONSTRAINT--constraint-name--'
.-,--------------.
V |
>----(-----column-name---+---)---references-clause--------------|
references-clause
|---REFERENCES--table-name----+----------------------------+---->
| .-,--------------. |
| V | |
'-(-----column-name---+---)--'
(2)
.-ON DELETE NO ACTION--------. .-ON UPDATE NO ACTION--.
>-----+----------------------------+---+----------------------+-|
'-ON DELETE--+-RESTRICT----+-' '-ON UPDATE RESTRICT---'
+-CASCADE-----+
+-SET NULL----+
'-SET DEFAULT-'
check-constraint
|---+------------------------------+---------------------------->
'-CONSTRAINT--constraint-name--'
>----CHECK--(--check-condition--)-------------------------------|
Notes:
|
Adding a new column must not make the total byte count of all columns exceed 32766 or, if a VARCHAR or VARGRAPHIC column is specified, 32740.
If the system-column-name is not specified, and the column-name is not a valid system-column-name, a system column name is generated. For more information on how system column names are generated, see "Rules for Column Name Generation".
A DataLink column with FILE LINK CONTROL cannot be added to a table that is a dependent in a referential constraint with a delete rule of CASCADE.
Omission of NOT NULL and DEFAULT from a column-definition is an implicit specification of DEFAULT NULL.
| Data Type | Cast Function Name | ||
|---|---|---|---|
| Distinct type N based on a BLOB, CLOB, or DBCLOB | BLOB, CLOB, or DBCLOB * | ||
| Distinct type N based on a DATE, TIME, or TIMESTAMP | N (the user-defined cast function that was generated when N was created)
**
or DATE, TIME, or TIMESTAMP * | ||
| Distinct type N based on other data types | N (the user-defined cast function that was generated when N was created) ** | ||
| BLOB, CLOB, or DBCLOB | BLOB, CLOB, or DBCLOB * | ||
| DATE, TIME, or TIMESTAMP | DATE, TIME, or TIMESTAMP * | ||
| |||
If the clause is not specified, a unique constraint name is generated by the database manager.
This clause must not be specified in more than one column-definition and must not be specified at all if the UNIQUE clause is specified in the column definition. When a primary key is added, a CHECK constraint is implicitly added to enforce the rule that the NULL value is not allowed in the column that makes up the primary key.
This clause cannot be specified more than once in a column definition and must not be specified if PRIMARY KEY is specified in the column-definition.
Alters the definition of an existing column. Only the attributes specified will be altered. Others will remain unchanged.
The specified length, precision, and scale may be larger, smaller, or the same as the existing length, precision, and scale. However, if the new length, precision, or scale is smaller, truncation or numeric conversion errors may occur.
If the specified column has a default value and a new default value is not specified, the existing default value must represent a value that could be assigned to the column in accordance with the rules for assignment as described in "Assignments and Comparisons".
If the column is specified in a unique, primary, or foreign key, the new sum of the lengths of the columns of the keys must not exceed 2000-n, where n is the number of columns specified that allow nulls.
Changing the attributes will cause any existing values in the column to be converted to the new column attributes according to the rules for assignment to a column, except that string values will be truncated.
Drops the identified column from the table.
If all the columns referenced in a constraint are dropped in the same ALTER TABLE statement, RESTRICT does not prevent the drop.
If not specified, a unique constraint name is generated by the database manager.
If a unique index already exists on the identified columns, that index is designated as a unique index. Otherwise, a unique index is created to support the uniqueness of the unique key. The unique index is created as part of the AS/400 physical file, not as a separate AS/400 logical file.
If a unique index already exists on the identified columns, that index is designated as a primary index. Otherwise, a primary index is created to support the uniqueness of the primary key. The unique index is created as part of the AS/400 physical file, not a separate AS/400 logical file.
If not specified, a unique constraint name is generated by the database manager.
Let T1 denote the table being altered.
A referential constraint is a duplicate if its foreign key, parent key, and parent table are the same as the foreign key, parent key, and parent table of an existing referential constraint. Duplicate referential constraints are allowed, but not recommended.
Let T2 denote the identified parent table.
The list of column names must be identical to the list of column names in the primary key of T2 or a UNIQUE constraint that exists on T2. The names may be specified in any order. For example, if (A,B) is specified, a unique constraint defined as UNIQUE(B,A) would satisfy the requirement. If a column name list is not specified then T2 must have a primary key. Omission of the column name list is an implicit specification of the columns of that primary key.
The specified foreign key must have the same number of columns as the parent key of T2. The description of the nth column of the foreign key and the nth column of the parent key must have identical data types and lengths.
Unless the table is empty, the values of the foreign key must be validated before the table can be used. Values of the foreign key are validated during the execution of the ALTER TABLE statement. Therefore, every nonnull value of the foreign key must match some value of the parent key of T2.
The referential constraint specified by the FOREIGN KEY clause defines a relationship in which T2 is the parent and T1 is the dependent.
SET NULL must not be specified unless some column of the foreign key allows null values.
CASCADE must not be specified if T1 has a delete trigger. SET NULL and SET DEFAULT must not be specified if T1 has an update trigger.
CASCADE must not be specified if T1 contains a DataLink column with FILE LINK CONTROL.
The delete rule applies when a row of T2 is the object of a DELETE or propagated delete operation and that row has dependents in T1. Let p denote such a row of T2.
The update rule applies when a row of T2 is the object of an UPDATE or propagated update operation and that row has dependents in T1. Let p denote such a row of T2.
If not specified, a unique constraint name is generated by the database manager.
The check-condition is a search-condition, except:
For more information on search-condition, see Search Conditions.
A column can only be referenced once in an ADD, ALTER, or DROP COLUMN clause in a single ALTER TABLE statement. However, that same column can be referenced multiple times for adding or dropping constraints in the same ALTER TABLE statement.
The order of operations within an ALTER TABLE statement is:
Within each of these stages, the order in which the user specifies the clauses is the order in which they are performed, with one exception. If any columns are being dropped, that operation is logically done before any column definitions are added or altered, in case record length is increased as a result of the ALTER TABLE statement.
Any views or logical files in another job's QTEMP that are dependent on the table being altered will be dropped as a result of an ALTER TABLE statement.
Authority checking is performed only on the table being altered. Other objects may be accessed by the ALTER TABLE statement, but no authority to those objects is required. For example, no authority is required on views that exist on the table being altered, nor on dependent tables that reference the table being altered through a referential constraint.
It is strongly recommended that a current backup of the table and dependent views and logical files exist prior to altering a table.
The following performance considerations apply to an ALTER TABLE statement when adding, altering, or dropping columns from a table:
Adding and dropping columns require the data to be copied.
Altering a column usually requires the data to be copied. The data does not need to be copied, however, if the alter only includes the following changes:
An index does not need to be rebuilt when columns are added to a table or when columns are dropped or altered and those columns are not referenced in the index key.
Altering a column that is used in the key of an index or constraint usually requires the index to be rebuilt. The index does not need to be rebuilt, however, in the following cases:
Adding a column has no cascaded effects to SQL views or most logical files.31 For example, adding a column to a table does not cause the column to be added to any dependent views, even if those views were created with a SELECT * clause.
Dropping or altering a column may cause several cascaded effects. Table 21 lists the cascaded effects of dropping a column.
Table 21. Cascaded effects of dropping a column
| Operation | RESTRICT Effect | CASCADE Effect |
|---|---|---|
| Drop of a column referenced by a view | The drop of the column is not allowed. | The view and all views dependent on that view are dropped. |
| Drop of a column referenced by a non-view logical file | The drop is allowed, and the column is dropped from the logical file
if:
| The drop is allowed, and the column is dropped from the logical file
if:
|
| Drop of a column referenced in the key of an index | The drop of the index is not allowed. | The index is dropped. |
| Drop of a column referenced in a unique constraint | If all the columns referenced in the unique constraint are dropped in the
same ALTER COLUMN statement and the unique constraint is not referenced by a
referential constraint, the columns and the constraint are dropped.
(Hence, the index used to satisfy the constraint is also dropped.) For
example, if column A is dropped, and a unique constraint of UNIQUE(A) or
PRIMARY KEY(A) exists and no referential constraints reference the unique
constraint, the operation is allowed.
Otherwise, the drop of the column is not allowed. | The unique constraint is dropped as are any referential constraints that refer to that unique constraint. (Hence, any indexes used by those constraints are also dropped). |
| Drop of a column referenced in a referential constraint | If all the columns referenced in the referential constraint are dropped
at the same time, the columns and the constraint are dropped. (Hence,
the index used by the foreign key is also dropped). For example, if
column B is dropped and a referential constraint of FOREIGN KEY (A) exists,
the operation is allowed.
Otherwise, the drop of the column is not allowed. | The referential constraint is dropped. (Hence, the index used by the foreign key is also dropped). |
Table 22 lists the cascaded effects of altering a column.
(Alter of a column in the following chart means altering a data type,
precision, scale, length, or nullability characteristic.)
Table 22. Cascaded effects of altering a column
| Operation | Effect |
|---|---|
| Alter of a column referenced by a view | The alter is allowed.
The views that are dependent on the table will be recreated. The new column attributes will be used when recreating the views. |
| Alter of a column referenced by a non-view logical file | The alter is allowed.
The non-view logical files that are dependent on the table will be recreated. If the logical file shares a format with the file being altered, and that format is not used again in the logical file with another based-on file, the new column attributes will be used when recreating the logical file. Otherwise, the new column attributes will not be used when recreating the logical file. Instead, the current logical file attributes are used. |
| Alter of a column referenced in the key of an index. | The alter is allowed. (Hence, the index will usually be rebuilt.) |
| Alter of a column referenced in a unique constraint | The alter is allowed. (Hence, the index will usually be
rebuilt.)
If the unique constraint is referenced by a referential constraint, the attributes of the foreign keys no longer match the attributes of the unique constraint. The constraint will be placed in a defined and check-pending state. |
| Alter of a column referenced in a referential constraint | The alter is allowed.
|
Assume a new table EQUIPMENT has been created with the following
columns:
| Column Name | Data Type |
|---|---|
| EQUIP_NO | INT |
| EQUIP_DESC | VARCHAR(50) |
| LOCATION | VARCHAR(50) |
| EQUIP_OWNER | CHAR(3) |
Add a referential constraint to the EQUIPMENT table so that the owner (EQUIP_OWNER) must be a department number (DEPTNO) that is present in the DEPARTMENT table. If a department is removed from the DEPARTMENT table, the owner (EQUIP_OWNER) values for all equipment owned by that department should become unassigned (or set to null). Give the constraint the name, DEPTQUIP. Assume the DEPARTMENT table has a primary key defined as (DEPTNO).
ALTER TABLE EQUIPMENT
ADD CONSTRAINT DEPTQUIP
FOREIGN KEY (EQUIP_OWNER)
REFERENCES DEPARTMENT
ON DELETE SET NULL
Assume the same table EQUIPMENT exists as in the first example.
ALTER TABLE EQUIPMENT
ADD COLUMN QUANTITY INT
ALTER TABLE EQUIPMENT
ALTER COLUMN EQUIP_OWNER
SET DEFAULT 'ABC'
ALTER TABLE EQUIPMENT
DROP COLUMN LOCATION CASCADE
ALTER TABLE EQUIPMENT
ADD COLUMN SUPPLIER INT
DROP COLUMN LOCATION
ADD UNIQUE SUPPLIER
ADD PRIMARY KEY EQUIP_NO
ALTER TABLE EQUIPMENT
ALTER COLUMN EQUIP_DESC
SET DATA TYPE VARCHAR(60)