ALTER TABLE statement

Use the ALTER TABLE statement to modify the schema of an existing table.

Syntax


>>-ALTER TABLE--+-table---+--+-| Basic Table Options |------+--><
                '-synonym-'  |                          (1) |   
                             +-| Logging TYPE Options |-----+   
                             |                     (2)      |   
                             +-| ADD TYPE Clause |----------+   
                             |                        (3)   |   
                             '-| Statistics Options |-------'   

Basic Table Options

     .-,---------------------------------------------.     
     V                         (4)                   |     
|------+-| ADD Column Clause |---------------------+-+----------|
       |                      (5)                  |       
       +-| ADD AUDIT Clause |----------------------+       
       |                           (6)             |       
       +-| ADD CONSTRAINT Clause |-----------------+       
       |                                     (7)   |       
       +-| Add or drop specialized columns |-------+       
       |                       (8)                 |       
       +-| DROP AUDIT Clause |---------------------+       
       |                            (9)            |       
       +-| DROP CONSTRAINT Clause |----------------+       
       |                        (10)               |       
       +-| DROP Column Clause |--------------------+       
       |  (11)                       (12)          |       
       +--------| LOCK MODE Clause |---------------+       
       |                   (13)                    |       
       +-| MODIFY Clause |-------------------------+       
       |  (11)                                (14) |       
       +--------| MODIFY EXTENT SIZE Clause |------+       
       |  (11)                              (15)   |       
       +--------| MODIFY NEXT SIZE Clause |--------+       
       |                (16)                       |       
       +-| PUT Clause |----------------------------+       
       |                            (17)           |       
       '-| SECURITY POLICY Clause |----------------'

Notes:

See Logging TYPE Options
See ADD TYPE Clause
See Statistics options of the ALTER TABLE statement
See ADD Column Clause
See ADD AUDIT Clause
See ADD CONSTRAINT Clause
See Add or drop specialized columns
See DROP AUDIT Clause
See DROP CONSTRAINT Clause
See DROP Column Clause
Use this path no more than once
See LOCK MODE Clause
See MODIFY Clause
See MODIFY EXTENT SIZE
See MODIFY NEXT SIZE clause
See PUT Clause
See SECURITY POLICY Clause

Element	Description	Restrictions	Syntax
`synonym`	Synonym for the table to be altered	Synonym and its table must exist; USETABLENAME must not be set	Identifier
`table`	Name of table to be altered	Must exist in the current database	Identifier

Usage

You can use the Basic Table Options segment to modify the schema of a table by adding, modifying, or dropping columns and constraints, or changing the extent size or locking granularity of a table. The database server performs alterations in the order that you specify. If any of the actions fails, the entire operation is canceled. You can associate an existing table with a named ROW type, or specify a new storage space to store large-object data. You can add or drop shadow columns to support secondary-server update operations of the USELASTCOMMITTED feature, or add or drop a rowid column. However, a single ALTER TABLE statement cannot specify these options with most other alterations to the schema of the table.

To use ALTER TABLE, your discretionary access privileges must meet at least one of the following conditions:

You must have DBA privilege on the database that contains the table.
You must own the table.
You must have the Alter privilege on the specified table and the Resource privilege on the database where the table resides.
To add a referential constraint, you must have the DBA or References privilege on either the referenced columns or the referenced table.
To drop a constraint, you must have the DBA privilege or be the owner of the constraint. If you are the owner of the constraint but not the owner of the table, you must have Alter privilege on the specified table. You do not need the References privilege to drop a constraint.

If you run a slow alter on a table that contains a compressed partition, then the corresponding new partition is compressed. The number of compressed rows in the new partition might differ from the number of compressed rows in the original partition. The difference is caused by the timing of operations to rewrite the rows in the partition and to build the compression dictionary. If the resulting new partition has fewer compressed rows, you can recompress the partition, and optionally repack and shrink it.

The ALTER TABLE statement cannot add a fragmentation strategy to a nonfragmented table, nor modify the storage distribution strategy of a fragmented table. To modify the distributed storage strategy of a table, you must use the ALTER FRAGMENT statement, rather than the ALTER TABLE statement. For information on adding, modifying, or dropping the storage distribution strategy of a table, see the ALTER FRAGMENT statement.

Altering a table on which a view depends might invalidate the view.

Warning: The clauses available with this statement have varying performance implications. Before you undertake alter operations, check information in the Altering a table definition section in your IBM® Informix Performance Guide to review effects and strategies.

The table whose name or synonym follows the ALTER TABLE keywords must be a permanent table in the current database. It is subject to the following restrictions:

It cannot be a temporary table.
It cannot be a table in a database that is not the current database.
It cannot be a table object that the CREATE EXTERNAL TABLE statement defined.
It cannot be a violations table or a diagnostics table.
If the USETABLENAME environment variable is set, you cannot specify a synonym for the table in the ALTER TABLE statement.

In addition, you cannot use the ALTER TABLE statement for the following operations:

Add, drop, or modify a column in a table that has an associated violation table or diagnostics table.
Define a referential constraint or a unique constraint on a RAW table.
Define an index on a column or on a set of columns that would conflict with the Restrictions on columns as index keys.