ALTER TABLE

Alter an existing table

Synopsis

ALTER [ BLOB ] TABLE { ONLY table_ident
                       | table_ident [ PARTITION (partition_column = value [ , ... ]) ] }
  { SET ( parameter = value [ , ... ] )
    | RESET ( parameter [ , ... ] )
    | ADD [ COLUMN ] column_name data_type [ column_constraint [ ... ] ]
  }

where column_constraint is:

{ PRIMARY KEY |
  INDEX { OFF | USING { PLAIN |
                        FULLTEXT [ WITH ( analyzer = analyzer_name ) ]  }
}

Description

ALTER TABLE can be used to alter an existing table.

SET can be used to change a table parameter to a different value. Using RESET will reset the parameter to its default value.

ADD COLUMN can be used to add an additional column to a table.

While columns can be added at any time, adding a new generated column is only possible if the table is empty.

Use the BLOB keyword in order to alter a blob table (see Blob Support). Blob tables cannot have custom columns which means that the ADD COLUMN keyword won’t work.

While altering a partitioned table, using ONLY will apply changes for the table only and not for any possible existing partitions. So these changes will only be applied to new partitions. The ONLY keyword cannot be used together with a PARTITION Clause.

Parameters

table_ident:The name (optionally schema-qualified) of the table to alter.
parameter:The name of the parameter that is set to a new value or its default.
column_name:Name of the column which should be added.
data_type:data type of the column which should be added.

See the CREATE TABLE WITH Clause for a list of available parameters.

PRIMARY KEY Constraint

The PRIMARY KEY constraint specifies that a column or columns of a table can contain only unique (non-duplicate), non-null values.

Adding a PRIMARY KEY column is only possible if the table is empty.

Warning

The verification if the table is empty and the schema update isn’t atomic. That means that it could be possible to add a primary key column to a table that isn’t empty. If that is the case queries that contain the primary key columns in the WHERE clause will not behave as expected.

PARTITION Clause

If the table is partitioned this clause can be used to alter only a single partition.

Note

BLOB tables cannot be partitioned and hence this clause cannot be used.

This clause identifies a single partition. It takes one ore more partition columns with a value each to identify the partition to alter.

[ PARTITION ( partition_column = value [ , ... ] ) ]
partition_column:The name of the column by which the table is partitioned. All partition columns that were part of the PARTITIONED BY Clause of the CREATE TABLE statement must be specified.
value:The columns value.