ALTER TABLE Statement (alter_table_statement)

The ALTER TABLE statement (alter_table_statement) changes the properties of a base table.

Structure

Syntax Syntax

<alter_table_statement> ::=
  ALTER TABLE <table_name> <add_definition>
| ALTER TABLE <table_name> <alter_definition>
| ALTER TABLE <table_name> <column_change_definition>
| ALTER TABLE <table_name> <drop_definition>
| ALTER TABLE <table_name> <modify_definition>
| ALTER TABLE <table_name> <referential_constraint_definition>
| ALTER TABLE <table_name> <sample_definition>
| ALTER TABLE <table_name> [NOT] CACHE
| ALTER TABLE <table_name> [NOT] NOCACHE

End of the code.

Examples

SQL Tutorial, Tables, Primary Key, Constraints, Foreign Key Dependencies Between Tables

Explanation

ALTER TABLE Statement
ADD Definition	Defines additional table properties.
ALTER Definition	Changes a CONSTRAINT or key definition.
COLUMN Change Definition	Changes column properties.
DROP Definition	Deletes table properties.
MODIFY Definition	Changes data types and column attributes.
Referential CONSTRAINT Definition	Defines a new referential constraint.
SAMPLE Definition	Defines a number of rows which the database system is to take into account when it determines table statistics (sample size). You can exclude individual tables from an UPDATE STATISTICS run by setting a sample size of 0 for these tables.

If the name of the schema is not specified in the table name, the current schema is used. The table name must be the name of an existing base table. The table must not be a temporary base table. The current database user must have the ALTER privilege for the specified table.

CACHE

The specification of CACHE for a table means that data from the table (row values, LOB values, index data) that has been read into the data cache once remains there, as far as possible.

An area is reserved in the data cache for all tables with the CACHE option. The size of this reserved area is defined by the database parameter DataCachePinAreaSize. Data is only replaced in this reserved data cache area if inserting a new data page into the reserved area would cause the permitted size of this reserved area to be exceeded.

The CACHE option is particularly suited for tables that are accessed very frequently.

If CACHE is specified, you cannot specify NOCACHE. The specification of NOT CACHE for a table means that the CACHE option is revoked.

NOCACHE

The specification of NOCACHE for a table means that data from the table (row values, LOB values, index data) that is read into the data cache is removed from the data cache very quickly.

The NOCACHE option is suitable for tables for which a long retention of the data in the data cache is not useful, either because the table is very seldom accessed or because there is no locality of accesses.

If NOCACHE is specified, you cannot specify CACHE. The specification of NOT NOCACHE for a table means that the NOCACHE option is revoked.

More Information

Privileges: Overview