ALTER TABLE statement

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

Syntax

^{(explicit id alttab002)
alttab002} ^{(explicit id alttab003)
alttab003} ^{(explicit id bastab002)
bastab002} ^{(explicit id bastab016)
bastab016} ^{(explicit id bastab003)
bastab003} ^{(explicit id bastab017)
bastab017} ^{(explicit id bastab005)
bastab005} ^{(explicit id bastab006)
bastab006} ^{(explicit id bastab007)
bastab007} ^{(explicit id bastab009)
bastab009} ^{(explicit id bastab004)
bastab004} ^{(explicit id bastab014)
bastab014} ^{(explicit id bastab008)
bastab008} ^{(explicit id bastab010)
bastab010} ^{(explicit id bastab012)
bastab012} ^{(explicit id bastab013)
bastab013} ^{(explicit id bastab015)
bastab015} ALTER TABLE { table | synonym } { <Basic Table Options> | <Logging TYPE Options> ^[] | <ADD TYPE Clause> ^[] | <Statistics Options> ^[] }

Basic Table Options

{ { | <ADD Column Clause> ^[] | <ADD AUDIT Clause> ^[] | <ADD CONSTRAINT Clause> ^[] | <Add or drop specialized columns> ^[] | <DROP AUDIT Clause> ^[] | <DROP CONSTRAINT Clause> ^[] | <DROP Column Clause> ^[] | ^[] <LOCK MODE Clause> ^[] | <MODIFY Clause> ^[] | ^[] <MODIFY EXTENT SIZE Clause> ^[] | ^[] <MODIFY NEXT SIZE Clause> ^[] | <PUT Clause> ^[] | <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 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.

¹ See Logging TYPE Options

² See Statistics options of the ALTER TABLE statement

³ See ADD Column Clause

⁴ See ADD AUDIT Clause

⁵ See ADD CONSTRAINT Clause

⁶ 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 ADD TYPE Clause

¹⁵ See PUT Clause

¹⁶ See SECURITY POLICY Clause

¹⁷ See Add or drop specialized columns