ALTER TABLE

Alter an existing table.

Table of contents

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 [ ... ] ] } [, ... ]
    | { DROP [ COLUMN ] [ IF EXISTS ] column_name } [, ... ]
    | OPEN
    | CLOSE
    | RENAME TO table_ident
    | REROUTE reroute_option
    | DROP CONSTRAINT constraint_name
  }

where column_constraint is:

{ PRIMARY KEY |
  NOT NULL |
  INDEX { OFF | USING { PLAIN |
                        FULLTEXT [ WITH ( analyzer = analyzer_name ) ]  } |
  [ CONSTRAINT constraint_name ] CHECK (boolean_expression)
}

Description

ALTER TABLE can be used to modify an existing table definition. It provides options to add columns, modify constraints, enabling or disabling table parameters and allows to execute a shard reroute allocation.

Use the BLOB keyword in order to alter a blob table (see Blobs). 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.

See CREATE TABLE WITH for a list of available parameters.

table_ident

The name (optionally schema-qualified) of the table to alter.

Clauses

PARTITION

If the table is partitioned, the optional PARTITION clause can be used to alter one partition exclusively.

[ PARTITION ( partition_column = value [ , ... ] ) ]

partition_column

One of the column names used for table partitioning.

value

The respective column value.

All partition columns (specified by the PARTITIONED BY clause) must be listed inside the parentheses along with their respective values using the partition_column = value syntax (separated by commas).

Because each partition corresponds to a unique set of partition column row values, this clause uniquely identifies a single partition to alter.

Tip

The SHOW CREATE TABLE statement will show you the complete list of partition columns specified by the PARTITIONED BY clause.

Note

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

See also

Partitioned tables: Alter

Arguments

SET/RESET

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

parameter

The name of the parameter that is set to a new value or its default.

The supported parameters are listed in the CREATE TABLE WITH CLAUSE documentation. In addition to those, for dynamically changing the number of allocated shards, the parameter number_of_shards can be used. For more info on that, see Changing the number of shards.

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. In addition, adding a base column with Default clause is not supported. It is possible to define a CHECK constraint with the restriction that only the column being added may be used in the boolean expression.

data_type

Data type of the column which should be added.

column_name

Name of the column which should be added. This can be a sub-column on an existing OBJECT.

It’s possible to add multiple columns at once.

DROP COLUMN

Can be used to drop a column from a table.

column_name

Name of the column which should be dropped. This can be a sub-column of an OBJECT.

It’s possible to drop multiple columns at once.

Note

It’s not allowed to drop a column:

• which is a system column

• which is part of a PRIMARY KEY

• used in CLUSTERED BY column

• used in PARTITIONED BY

• is a named index column

• used in an named index

• is referenced in a generated column

• is referenced in a table level constraint with other columns

Note

It’s not allowed to drop all columns of a table.

Note

Dropping columns of a table created before version 5.5 is not supported.

OPEN/CLOSE

Can be used to open or close the table.

Closing a table means that all operations, except ALTER TABLE ..., will fail. Operations that fail will not return an error, but they will have no effect. Operations on tables containing closed partitions won’t fail, but those operations will exclude all closed partitions.

RENAME TO

Can be used to rename a table or view, while maintaining its schema and data. If renaming a table, the shards of it become temporarily unavailable.

REROUTE

The REROUTE command provides various options to manually control the allocation of shards. It allows the enforcement of explicit allocations, cancellations and the moving of shards between nodes in a cluster. See Reroute shards to get the convenient use-cases.

The row count defines if the reroute or allocation process of a shard was acknowledged or rejected.

Note

Partitioned tables require a PARTITION clause in order to specify a unique shard_id.

[ REROUTE reroute_option]

where reroute_option is:

{ MOVE SHARD shard_id FROM node TO node
  | ALLOCATE REPLICA SHARD shard_id ON node
  | PROMOTE REPLICA SHARD shard_id ON node [ WITH (accept_data_loss = { TRUE | FALSE }) ]
  | CANCEL SHARD shard_id ON node [ WITH (allow_primary = {TRUE|FALSE}) ]
}

shard_id

The shard ID. Ranges from 0 up to the specified number of Shards shards of a table.

node

The ID or name of a node within the cluster.

See Nodes how to gain the unique ID.

REROUTE supports the following options to start/stop shard allocation:

MOVE

A started shard gets moved from one node to another. It requests a table_ident and a shard_id to identify the shard that receives the new allocation. Specify FROM node for the node to move the shard from and TO node to move the shard to.

ALLOCATE REPLICA

Allows to force allocation of an unassigned replica shard on a specific node.

PROMOTE REPLICA Force promote a stale replica shard to a primary. In case

a node holding a primary copy of a shard had a failure and the replica shards are out of sync, the system won’t promote the replica to primary automatically, as it would result in a silent data loss.

Ideally the node holding the primary copy of the shard would be brought back into the cluster, but if that is not possible due to a permanent system failure, it is possible to accept the potential data loss and force promote a stale replica using this command.

The parameter accept_data_loss needs to be set to true in order for this command to work. If it is not provided or set to false, the command will error out.

CANCEL

This cancels the allocation or recovery of a shard_id of a table_ident on a given node. The allow_primary flag indicates if it is allowed to cancel the allocation of a primary shard.

DROP CONSTRAINT

Removes a CHECK constraint from a table.

ALTER TABLE table_ident DROP CONSTRAINT check_name

table_ident

The name (optionally schema-qualified) of the table.

check_name

The name of the check constraint to be removed.

Warning

A removed CHECK constraints cannot be re-added to a table once dropped.