All Products
Search
Document Center

ALTER TABLE

Last Updated: Jun 18, 2021

Description

The ALTER TABLE statement changes the schema of an existing table. For example, you can modify a table and table attributes, add columns, modify columns and column attributes, and delete columns.

Syntax

alter_table_stmt:
      ALTER TABLE table_name
      alter_table_action_list;
    | RENAME TABLE rename_table_action_list;

alter_table_action_list:
    alter_table_action [, alter_table_action ...]

alter_table_action:
      ADD [COLUMN] {column_definition | (column_definition_list)}
    | MODIFY [COLUMN] column_definition
    | DROP [COLUMN] column_name
    | ADD [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} [index_name] index_desc
        | ADD [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name_list) references_clause
    | ADD [CONSTRAINT [constraint_name]] CHECK (expr)
    | ADD {INDEX | KEY} [index_name] index_desc
    | ADD FULLTEXT [INDEX | KEY] [index_name] fulltext_index_desc
    | ALTER INDEX index_name [VISIBLE | INVISIBLE]
    | DROP {INDEX | KEY} index_name
    | ADD PARTITION (range_partition_list)
    | DROP PARTITION (partition_name_list)
    | REORGANIZE PARTITION name_list INTO partition_range_or_list
    | TRUNCATE PARTITION name_list
    | [SET] table_option_list
    | RENAME [TO] table_name
    | DROP TABLEGROUP
    | DROP CONSTRAINT constraint_name

rename_table_action_list:
    rename_table_action [, rename_table_action ...]

rename_table_action:
    table_name TO table_name

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
    column_name data_type
    [DEFAULT const_value] [AUTO_INCREMENT]
    [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment

index_desc:
   (column_desc_list) [index_type] [index_option_list]

fulltext_index_desc:
   (column_desc_list) CTXCAT(column_desc_list) [index_option_list]

column_desc_list:
    column_desc [, column_desc ...]

column_desc:
     column_name [(length)] [ASC | DESC]

index_type:
    USING BTREE

index_option_list:
    index_option [ index_option ...]

index_option:
      [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name_list)
    | comment

table_option_list:
    table_option [ table_option ...]

table_option:
    | primary_zone
    | replica_num
    | table_tablegroup
    | block_size
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | comment
    | DUPLICATE_SCOPE [=] "none|zone|region|cluster"

partition_option:
      PARTITION BY HASH(expression)
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY KEY([column_name_list])
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] (range_partition_list)

subpartition_option:
      SUBPARTITION BY HASH(expression)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY KEY(column_name_list)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      (range_subpartition_list)

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_name_list:
    partition_name [, partition_name ...]

partition_count | subpartition_count:
    INT_VALUE

Parameter description

Parameter

Description

ADD [COLUMN]

Adds a column. You cannot add a primary key column.

MODIFY [COLUMN]

Modifies column attributes.

DROP [COLUMN]

Deletes a column. You are not allowed to delete the primary key column or a column that has indexes.

ADD [UNIQUE INDEX]

Adds a unique index.

ADD [INDEX]

Adds a general index.

ALTER [INDEX]

Modifies index attributes.

ADD [PARTITION]

Adds a partition.

DROP [PARTITION]

Deletes a partition.

REORGANIZE [PARTITION]

Reorganizes a partition.

TRUNCATE [PARTITION]

Deletes partition data.

RENAME [TO] table_name

Renames a table.

DROP [TABLEGROUP]

Deletes a table group.

DROP [CONSTRAINT]

Deletes a constraint.

SET BLOCK_SIZE

Specifies the block size of the partitioned table.

SET REPLICA_NUM

Specifies the number of replicas for the table. The value indicates the total number of replicas.

SET COMPRESSION

Specifies the compression method of the table.

SET USE_BLOOM_FILTER

Specifies whether to use BloomFilter.

SET COMMENT

Specifies comment information.

SET PROGRESSIVE_MERGE_NUM

Specifies the number of progressive merge rounds. Valid values: 1 to 64.

Examples

  • Change the field type of the field d in table t2.

ALTER TABLE t2 MODIFY d CHAR(10);
  • Add and delete a column.

    • Before you add a column, run the DESCRIBE test; command to view table information. The following figure shows the result.image.png

    • Run the following command to add the c3 column:

ALTER TABLE test ADD c3 int;
    • After you add the column, run the DESCRIBE test; command to view the table information. The following figure shows the result.image.png

    • Run the following command to delete the c3 column:

ALTER TABLE test DROP c3;
    • After you delete the column, run the DESCRIBE test; command to view the table information. The following figure shows the result.image.png

  • Set the number of replicas for the test table and add the c5 column.

ALTER TABLE test SET REPLICA_NUM=2, ADD COLUMN c5 INT;