All Products
Search
Document Center

ALTER TABLE

Last Updated: Jun 18, 2021

Description

You can execute the ALTER TABLE statement to update the schema of an existing table. For example, you can modify an existing table and table attributes, add columns, modify columns and column attributes, or 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)}
    | CHANGE [COLUMN] column_name column_definition
    | MODIFY [COLUMN] column_definition
    | ALTER [COLUMN] column_name {SET DEFAULT const_value | DROP DEFAULT}
    | DROP [COLUMN] column_name
    | ADD [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} [index_name] index_desc
    | 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 FOREIGN KEY fk_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

Parameters

Parameter

Description

ADD [COLUMN]

Add the column. You cannot add a primary key column.

CHANGE [COLUMN]

Modify the column name and column attributes.

MODIFY [COLUMN]

Modify column attributes.

ALTER [COLUMN]

Modify the default value of the column.

DROP [COLUMN]

Delete the column. You cannot delete a primary key column or column that has indexes.

ADD [UNIQUE INDEX]

Add the unique index.

ADD [INDEX]

Add the common index.

ALTER [INDEX]

Modify index attributes.

ADD [PARTITION]

Add the partition.

DROP [PARTITION]

Delete the partition.

REORGANIZE [PARTITION]

Re-partition the table.

TRUNCATE [PARTITION]

Delete data from the partition.

RENAME [TO] table_name

Rename the table.

DROP [TABLEGROUP]

Delete the table group.

DROP [FOREIGN KEY]

Delete the foreign key.

SET BLOCK_SIZE

The block size for the partitioned table.

SET REPLICA_NUM

The number of replicas for the table.

SET COMPRESSION

The compression method for the table.

SET USE_BLOOM_FILTER

Specifies whether to use a Bloom filter.

SET COMMENT

Add comments.

SET PROGRESSIVE_MERGE_NUM

The number of macro-blocks that are to be merged at a time for progressive compaction. Valid values: 1 to 64.

Examples

  • Change the field name d in the t2 table to c and change the data type of the field.

ALTER TABLE t2 CHANGE COLUMN d c CHAR(10);
  • Add a column, and then delete it.

    • Before you add a column, execute the DESCRIBE test; statement to view the table. The following information appears:image.png

    • Execute the following statement to add the c3 column:

ALTER TABLE test ADD c3 int;
    • After you add the column, execute the DESCRIBE test; statement to view the table. The following information appears:image.png

    • Execute the following statement to delete the c3 column:

ALTER TABLE test DROP c3;
    • After you delete the column, execute the DESCRIBE test; statement to view the table. The following information appears:image.png

  • Configure the number of replicas for the test table, and add the c5 column to the table.

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