ALTER TABLE

Description

This statement is used to modify the structure of an existing table. For example, you can use this statement to modify the table name and table attributes, add columns, modify column names and column attributes, and delete columns.

Syntax

alter_table_stmt:
    ALTER TABLE [TEMPORARY] table_name alter_table_action_list;

alter_table_action_list:
    alter_table_action [, alter_table_action ...]

alter_table_action:
      ADD [COLUMN] column_definition
          [ opt_position_column ]
    | ADD [COLUMN] (column_definition_list)
    | ADD [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY}
          [index_name] index_desc
    | ADD [CONSTRAINT [constraint_name]] FOREIGN KEY
          [index_name] index_desc
          REFERENCES reference_definition
          [match_action][opt_reference_option_list]
    | ADD PRIMARY KEY (column_name)
    | ADD CONSTRAINT [constraint_name] CHECK (expression)[[NOT] ENFORCED]
    | ADD PARTITION (range_partition_list)
    | ADD {INDEX | KEY} [index_name] [index_type] (key_part,...) [index_option_list][index_column_group_option] [algorithm = algorithm_value] [lock = lock_value]
    | ADD FULLTEXT [INDEX | KEY] [index_name](column_name, [column_name ...]) [WITH PARSER tokenizer_option] [PARSER_PROPERTIES[=](parser_properties_list)] [LOCAL]
    | ADD COLUMN GROUP([all columns, ]each column) [DELAYED]
    | ALTER [COLUMN] column_name {
          SET DEFAULT const_value
        | DROP DEFAULT
      }
    | ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
    | ALTER INDEX index_name
          [VISIBLE | INVISIBLE]
    | CHANGE [COLUMN] column_name column_definition
    | DROP [COLUMN] column_name [, DROP COLUMN column_name ...]
    | DROP {PARTITION | SUBPARTITION} partition_name_list
    | DROP TABLEGROUP
    | DROP FOREIGN KEY fk_name
    | DROP PRIMARY KEY [, ADD PRIMARY KEY (column)]
    | DROP CHECK constraint_name
    | DROP COLUMN GROUP([all columns, ]each column)
    | MODIFY [COLUMN] column_definition
    | RENAME COLUMN old_col_name [TO|AS] new_col_name
    | RENAME [TO|AS] table_name
    | RENAME {INDEX | KEY} old_index_name [TO|AS] new_index_name
    | [SET] table_option_list
    | TRUNCATE {PARTITION | SUBPARTITION} partition_name_list
    | EXCHANGE PARTITION partition_name WITH TABLE origin_table_name WITHOUT VALIDATION
    | REORGANIZE PARTITION partition_name INTO (split_range_partition_list)
    | PARTITION BY {RANGE [COLUMNS]([column_name_list]) [SIZE('size_value')] [range_partition_list]
                   | partition_option}
    | MODIFY PARTITION partition_name ADD SUBPARTITION (add_subpartition_option)
    | FORCE

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 string_value] [SKIP_INDEX(skip_index_option_list)] [ opt_position_column ]

skip_index_option_list:
    skip_index_option [,skip_index_option ...]

skip_index_option:
    MIN_MAX
    | SUM

opt_position_column:
    FIRST | BEFORE | AFTER column_name

index_desc:
   (column_desc_list) [index_type] [index_option_list]

match_action:
   MATCH {SIMPLE | FULL | PARTIAL}

opt_reference_option_list:
   reference_option [,reference_option ...]

reference_option:
   ON {DELETE | UPDATE} {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}

column_desc_list:
    column_desc [, column_desc ...]

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

key_part:
    {index_col_name [(length)] | (expr)} [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 string_value

index_column_group_option:
    WITH COLUMN GROUP([all columns, ]each column)

table_option_list:
    table_option [ table_option ...]

table_option:
      TABLEGROUP = tablegroup_name
    | block_size
    | lob_inrow_threshold [=] num
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | COMMENT string_value
    | parallel_clause
    | TABLE_MODE [=] 'table_mode_value'
    | AUTO_INCREMENT_CACHE_SIZE [=] INT_VALUE
    | READ {ONLY | WRITE}
    | DUPLICATE_SCOPE= 'none | cluster'
    | enable_macro_block_bloom_filter [=] {True | False}
    | DYNAMIC_PARTITION_POLICY [=] (dynamic_partition_policy_list)
    | SEMISTRUCT_ENCODING_TYPE [=] 'encoding'

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

table_mode_value:
    NORMAL
    | QUEUING
    | MODERATE
    | SUPER
    | EXTREME

dynamic_partition_policy_list:
    dynamic_partition_policy_option [, dynamic_partition_policy_option ...]

dynamic_partition_policy_option:
    ENABLE = {true | false}
    | PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
    | EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}

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)

split_range_partition_list:
    PARTITION split_partition_name VALUES LESS THAN (value)
    [, PARTITION split_partition_name VALUES LESS THAN (value) ...]
     , PARTITION split_partition_name VALUES LESS THAN (source_value)

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

add_subpartition_option:
    {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr
        [, SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr] ...}
    | {SUBPARTITION subpartition_name VALUES IN list_partition_expr
        [, SUBPARTITION subpartition_name VALUES IN list_partition_expr] ...}

Parameter description

ADD INDEX KEY

• ADD {INDEX | KEY} [index_name] [index_type] (key_part,...) [index_option_list][index_column_group_option] [algorithm] [lock]
  • INDEX | KEY : Specifies the key or index for the created table. These two keywords are equivalent.
  • index_name: Optional. Specifies the index name. If no index name is specified, the first column referenced by the index is used as the index name. If the index name is duplicated, the index name is specified as underscore (_) plus the sequence number.
  • index_type: Optional. Specifies the index type.
  • key_part: Specifies the column name or expression to be included in the index.
  • index_option: Optional. Specifies the index options.
  • algorithm: Specifies the underlying algorithm type used to execute DDL operations (such as adding an index).<main id="notice"type='notice'>

    Notice

    In the current version, the algorithm parameter is for compatibility with MySQL syntax and has no practical effect.

  • lock: Specifies the table lock level applied during DDL operations, controlling the granularity of concurrent access.<main id="notice"type='notice'>

    Notice

    In the current version, the lock parameter is for compatibility with MySQL syntax and has no practical effect.

table_mode_value

• NORMAL: the default value, indicating normal. In this mode, the probability of triggering a major compaction after a minor compaction is very low.
• QUEUING: in this mode, the probability of triggering a major compaction after a minor compaction is low.
• MODERATE: indicating moderate. In this mode, the probability of triggering a major compaction after a minor compaction is moderate.
• SUPER: indicating super. In this mode, the probability of triggering a major compaction after a minor compaction is high.
• EXTREME: indicating extreme. In this mode, the probability of triggering a major compaction after a minor compaction is high.

For more information about compaction, see Adaptive compaction.

tokenizer_option

• SPACE: the default value, which specifies to split text by spaces. You can specify the following parameters:

  Property	Value range
  min_token_size	[1, 16]
  max_token_size	[10, 84]

• NGRAM: specifies to split text by using the N-Gram (Chinese) tokenizer. You can specify the following parameters:

  Property	Value range
  ngram_token_size	[1, 10]

• NGRAM2: specifies to split text into continuous characters whose lengths range from min_ngram_size to max_ngram_size. You can specify the following parameters:

  Property	Value range
  min_ngram_size	[1, 16]
  max_ngram_size	[1, 16]

  Note

  For OceanBase Database V4.3.5, the NGRAM2 tokenizer is supported since V4.3.5 BP2.

• BENG: specifies to split text by using the basic English tokenizer. You can specify the following parameters:

  Parameter	Value range
  min_token_size	[1, 16]
  max_token_size	[10, 84]

• IK: specifies to split text by using the Chinese IK tokenizer. Only the utf-8 character set is supported. You can specify the following parameters:

  Property	Value range
  ik_mode	smart max_word

  Note

  For OceanBase Database V4.3.5, the IK tokenizer is supported since V4.3.5 BP1.

You can call the TOKENIZE function to view the tokenization results of text in JSON format by using the specified tokenizer.

parser_properties

• min_token_size: the minimum token length. The default value is 3, and the value range is 1 to 16.

• max_token_size: the maximum token length. The default value is 84, and the value range is 10 to 84.

• ngram_token_size: the token length of the NGRAM tokenizer. This parameter is valid only for the NGRAM tokenizer. The default value is 2, and the value range is 1 to 10.

• ik_mode: the mode of the IK tokenizer. Valid values: smart and max_word.

  • smart: the default value. This mode specifies to use the words in the dictionary to improve the accuracy of tokenization and prioritize the boundaries of the words in the dictionary, which may reduce unnecessary expansion.
  • max_word: this mode specifies to identify the words defined in the dictionary but does not affect the maximum expansion of tokenization. Even if the dictionary defines a word, the max_word mode will still try to split the text into more words.

• min_ngram_size: the minimum token length. The value range is [1,16].

• max_ngram_size: the maximum token length. The value range is [1,16].

Here is an example:

1. Create a table named tbl1.

   CREATE TABLE tbl1 (col1 VARCHAR(200), col2 TEXT);
   

2. Add a full-text index named fidx to the tbl1 table.

   ALTER TABLE tbl1 ADD FULLTEXT INDEX fidx(col2) WITH PARSER ik PARSER_PROPERTIES=(ik_mode='max_word');
   

dynamic_partition_policy_option

• ENABLE = {true | false}: specifies whether to enable dynamic partition management. Valid values are as follows:

  • true: specifies to enable dynamic partition management. This value is the default.
  • false: specifies to disable dynamic partition management.

• PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: specifies the precreate time. A dynamic partition management job is scheduled, and partitions are precreated such that the maximum partition upper bound > now() + precreate_time. Valid values are as follows:

  • -1: specifies not to precreate any partitions. This value is the default.
  • 0: specifies to precreate only the current partition.
  • n {hour | day | week | month | year}: specifies to precreate partitions within the specified time span. For example, 3 hour specifies to precreate partitions within the last 3 hours.

  Note

  • When multiple partitions are to be precreated, the interval between partition boundaries is TIME_UNIT.
  • The first precreated partition boundary is the ceiling of the maximum existing partition boundary by TIME_UNIT.

• EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: specifies the partition expiration time. A dynamic partition management job is scheduled, and all partitions whose upper bound is earlier than now() - expire_time are deleted. Valid values are as follows:

  • -1: specifies that partitions never expire. This value is the default.
  • 0: specifies that all partitions except the current one expire.
  • n {hour | day | week | month | year}: specifies the partition expiration time. For example, 1 day specifies that partitions expire after 1 day.

For more information about how to modify a dynamic partition table, see Modify a dynamic partition table.

Here is an example:

ALTER TABLE tbl2 SET DYNAMIC_PARTITION_POLICY(
    ENABLE = true,
    PRECREATE_TIME = '1 day',
    EXPIRE_TIME = '-1'
);

Example

Add and drop columns

Create a sample table named tbl1 and execute the DESCRIBE command to view the table information.

• NGRAM: this option specifies to tokenize the text based on N-Gram (for Chinese). You can set the following attribute for this tokenizer:

  Attribute	Value range
  ngram_token_size	[1, 10]

• BENG: this option specifies to tokenize the text based on the foundational English language (Beng). You can set the following attributes for this tokenizer:

  Attribute	Value range
  min_token_size	[1, 16]
  max_token_size	[10, 84]

• IK: this option specifies to tokenize the text based on the IK engine (for Chinese). The current version supports only the utf-8 character set. You can set the following attributes for this tokenizer:

  Attribute	Value range
  ik_mode	smart max_word

  Note

  For OceanBase Database V4.3.5, the IK tokenizer is supported since V4.3.5 BP1.

You can use the TOKENIZE function to view the tokenization results of a text by specifying the tokenizer and passing in a JSON-formatted parameter.

parser_properties

• min_token_size: the minimum word length. Default value: 3. Value range: 1 to 16.

• max_token_size: the maximum word length. Default value: 84. Value range: 10 to 84.

• ngram_token_size: Represents the token length of NGRAM, only valid for the NGRAM tokenizer. The default value is 2, and the range is from 1 to 10.

• ik_mode: the word splitting mode of the IK tokenizer. Valid values:

  • smart: the default value. In this mode, words in the dictionary are used to improve word splitting accuracy, and word boundaries in the dictionary are given priority. This may reduce unnecessary expansion.
  • max_word: in this mode, words in the dictionary are identified, but the maximum expansion of words is not affected. In max_word mode, the text is still divided into more words even if they are defined in the dictionary.

Here are some examples:

1. Create a table named tbl1.

   CREATE TABLE tbl1 (col1 VARCHAR(200), col2 TEXT);
   

2. Add a full-text index named fidx to the tbl1 table.

   ALTER TABLE tbl1 ADD FULLTEXT INDEX fidx(col2) WITH PARSER ik PARSER_PROPERTIES=(ik_mode='max_word');
   

dynamic_partition_policy_option

• ENABLE = {true | false}: specifies whether to enable dynamic partition management. Valid values:

  • true: the default value. Specifies to enable dynamic partition management.
  • false: specifies to disable dynamic partition management.

• PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: specifies the precreation time. Dynamic partition management is scheduled to precreate partitions so that upper boundary of maximum partition > now() + precreate_time. Valid values:

  • -1: the default value. Specifies not to precreate partitions.
  • 0: specifies to precreate only the current partition.
  • n {hour | day | week | month | year}: specifies to precreate partitions for the specified period. For example, 3 hour specifies to precreate partitions for 3 hours.

  Note

  • When you need to precreate multiple partitions, the intervals between partition boundaries are TIME_UNIT.
  • The boundary of the first precreated partition is the upper boundary of the maximum existing partition, rounded up based on TIME_UNIT.

• EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: specifies the partition expiration time. Dynamic partition management is scheduled to delete all expired partitions whose upper boundary < now() - expire_time. The value of this parameter can be modified. Valid values:

  • -1: the default value. Specifies that partitions never expire.
  • 0: specifies that partitions other than the current partition expire.
  • n {hour | day | week | month | year}: specifies the partition expiration time. For example, 1 day specifies that partitions expire one day after they are created.

For more information about how to modify a table with dynamic partitions, see Modify a table with dynamic partitions.

Here are some examples:

ALTER TABLE tbl2 SET DYNAMIC_PARTITION_POLICY(
    ENABLE = true,
    PRECREATE_TIME = '1 day',
    EXPIRE_TIME = '-1'
);

Example

Operations to add and drop columns

Execute the following statements to create a table named tbl1 and return the table information by using the DESCRIBE statement.

obclient> CREATE TABLE tbl1 (c1 INT(11) PRIMARY KEY,c2 VARCHAR(50));
Query OK, 0 rows affected

obclient> DESCRIBE tbl1;
+-------+------------+----------+--------+---------+-------+
| Field | Type       | Null     | Key    | Default | Extra |
+-------+------------+----------+--------+---------+-------+
| c1    | int(11)    | NO       | PRI    | NULL    |       |
| c2    | varchar(50)| YES      |        | NULL    |       |
+-------+------------+----------+--------+---------+-------+

• Add the c3 column and execute the DESCRIBE command to view the table information.

  obclient> ALTER TABLE tbl1 ADD c3 INT;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c2    | varchar(50)| YES      |        | NULL    |       |
  | c3    | int(11)    | YES      |        | NULL    |       |
  +-------+------------+----------+--------+---------+-------+
  3 row affected
  

• Drop the c3 column and execute the DESCRIBE command to view the table information.

  obclient> ALTER TABLE tbl1 DROP c3;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c2    | varchar(50)| YES      |        | NULL    |       |
  +-------+------------+----------+--------+---------+-------+
  2 row affected
  

• Rename the c2 column in the tbl1 table to c3 and execute the DESCRIBE command to view the table information.

  obclient> ALTER TABLE tbl1 CHANGE COLUMN c2 c3 VARCHAR(50);
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c3    | varchar(50)| YES      |        | NULL    |       |
  +-------+------------+----------+--------+---------+-------+
  2 rows affected
  

• Add the c4 column to the tbl1 table and set it as the first column of the table, and execute the DESCRIBE command to view the table information.

  obclient> ALTER TABLE tbl1 ADD COLUMN c4 INTEGER FIRST;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c4    | int(11)    | YES      |        | NULL    |       |
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c3    | varchar(50)| YES      |        | NULL    |       |
  +-------+------------+----------+--------+---------+-------+
  3 row affected
  

• Add the c2 column after the c1 column and execute the DESCRIBE command to view the table information.

  obclient> ALTER TABLE tbl1 ADD COLUMN c2 INTEGER AFTER c1;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c4    | int(11)    | YES      |        | NULL    |       |
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c2    | int(11)    | YES      |        | NULL    |       |
  | c3    | varchar(50)| YES      |        | NULL    |       |
  +-------+------------+----------+--------+---------+-------+
  4 row affected
  

• Add the c5 column before the c4 column and execute the DESCRIBE command to view the table information.

  obclient> ALTER TABLE tbl1 ADD COLUMN c5 INT BEFORE c4;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c5    | int(11)    | YES      |        | NULL    |       |
  | c4    | int(11)    | YES      |        | NULL    |       |
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c2    | int(11)    | YES      |        | NULL    |       |
  | c3    | varchar(50)| YES      |        | NULL    |       |
  +-------+------------+----------+--------+---------+-------+
  5 row affected
  

• Add the foreign key constraint fk1 to the tbl2 table. When the UPDATE operation affects the parent table's key values that match the rows in the child table, execute the SET NULL operation.

  obclient> CREATE TABLE tbl2(c1 INT PRIMARY KEY,c2 INT);
  Query OK, 0 row affected
  obclient> CREATE TABLE tbl3(c1 INT PRIMARY KEY,c2 INT);
  Query OK, 0 row affected
  obclient> ALTER TABLE tbl2 ADD CONSTRAINT fk1 FOREIGN KEY (c2) REFERENCES tbl3(c1) ON UPDATE SET NULL;
  Query OK, 0 row affected
  

• Drop the foreign key constraint fk1 from the tbl3 table.

  obclient> ALTER TABLE tbl2 DROP FOREIGN KEY fk1;
  Query OK, 0 row affected
  

• Drop the primary key from the tbl2 table.

  obclient> ALTER TABLE tbl2 DROP PRIMARY KEY;
  Query OK, 0 row affected
  

Index operations

• Rename the index ind1 in the tbl2 table to ind2.

  obclient> CREATE TABLE tbl2(c1 INT(11) PRIMARY KEY,c2 INT(50));
  Query OK, 0 row affected
  
  obclient> CREATE INDEX ind1 ON tbl2 (c2 ASC);
  Query OK, 0 row affected
  obclient> ALTER TABLE tbl2 RENAME INDEX ind1 TO ind2;
  Query OK, 0 row affected
  

• Create an index named ind3 on the tbl2 table, which references the c1 and c2 columns.

  obclient> ALTER TABLE tbl2 ADD INDEX ind3 (c1,c2);
  Query OK, 0 row affected
  

  You can run the SHOW INDEX statement to view the created index.

  obclient> SHOW INDEX FROM tbl2;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | tbl2  |          0 | PRIMARY  |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
  | tbl2  |          1 | ind2     |            1 | c2          | A         |        NULL | NULL     | NULL   |  YES | BTREE      | available |               | YES     |
  | tbl2  |          1 | ind3     |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
  | tbl2  |          1 | ind3     |            2 | c2          | A         |        NULL | NULL     | NULL   |  YES | BTREE      | available |               | YES     |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  4 rows in set
  

• Drop the index ind2 from the tbl2 table.

  obclient> ALTER TABLE tbl2 DROP INDEX ind2;
  Query OK, 0 row affected
  

  Note

  In actual operations, you can use the above methods to implement atomic index changes.

• Add three function indexes to the t1_func table, one of which is named i2, and the other two are automatically generated by the system, with names in the format of functional_index followed by a number.

  obclient> ALTER TABLE t1_func ADD INDEX ((CONCAT(c1,'a')));
  Query OK, 0 rows affected
  obclient> ALTER TABLE t1_func ADD INDEX ((c1+1));
  Query OK, 0 rows affected
  obclient> ALTER TABLE t1_func ADD INDEX i2 ((CONCAT(c1,'a')));
  Query OK, 0 rows affected
  obclient> SHOW CREATE TABLE t1_func;
  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | t1_func | CREATE TABLE `t1_func` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `i1` (((`c1` + `c2`) < 1)) BLOCK_SIZE 16384 LOCAL,
  KEY `functional_index` (concat(`c1`,'a')) BLOCK_SIZE 16384 LOCAL,
  KEY `functional_index_2` ((`c1` + 1)) BLOCK_SIZE 16384 LOCAL,
  KEY `i2` (concat(`c1`,'a')) BLOCK_SIZE 16384 LOCAL
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  

• Create a column store index for the table.

  1. Run the following SQL statement to create a table named tbl3.

     CREATE TABLE tbl3 (col1 INT, col2 VARCHAR(50));
     

  2. Create a column store index named idx1_tbl3 on the tbl3 table, which references the col1 column.

     ALTER TABLE tbl3 ADD INDEX idx1_tbl3 (col1) WITH COLUMN GROUP(each column);
     

• Make the index invisible.

  Note

  After an index is made invisible, you can see the /*!80000 INVISIBLE */ tag in the table schema.

  1. Create a tbl4 table.

     CREATE TABLE tbl4 (col1 INT PRIMARY KEY, col2 INT, col3 INT);
     

  2. Create an index named idx1_tbl4.

     CREATE INDEX idx1_tbl4 ON tbl4(col2);
     

  3. Make the idx1_tbl4 index invisible.

     ALTER TABLE tbl4 ALTER INDEX idx1_tbl4 INVISIBLE;
     

  4. View the structure of the tbl4 table again.

     SHOW CREATE TABLE tbl4;
     

     The returned result is as follows:

     +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                              |
     +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | tbl4  | CREATE TABLE `tbl4` (
       `col1` int(11) NOT NULL,
       `col2` int(11) DEFAULT NULL,
       `col3` int(11) DEFAULT NULL,
       PRIMARY KEY (`col1`),
       KEY `idx1_tbl4` (`col2`) BLOCK_SIZE 16384 LOCAL /*!80000 INVISIBLE */
     ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column) |
     +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     1 row in set
     

Partitioning operations

• Clear all data in the M202001 and M202002 partitions of the partitioned table t_log_part_by_range.

  obclient> CREATE TABLE t_log_part_by_range (
      log_id      bigint NOT NULL
      , log_value varchar(50)
      , log_date  timestamp NOT NULL
  ) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
  (
      PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
      , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
      , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
      , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
      , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
  );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE t_log_part_by_range TRUNCATE PARTITION M202001, M202002;
  Query OK, 0 rows affected
  

• Add the M202006 partition to the partitioned table t_log_part_by_range.

  obclient> CREATE TABLE t_log_part_by_range (
      log_id      bigint NOT NULL
      , log_value varchar(50)
      , log_date  timestamp NOT NULL
  ) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
  (
      PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
      , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
      , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
      , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
      , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
  );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE t_log_part_by_range ADD PARTITION
           (PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
           );
  

Change the degree of parallelism

• Change the degree of parallelism of tbl3 to 2.

  obclient> ALTER TABLE tbl3 PARALLEL 2;
  

  You can change the degree of parallelism of a table by using either ALTER TABLE table_name PARALLEL int; or the PARALLEL hint. The syntax for using the PARALLEL hint is alter /*+ parallel(int) */ table.

Operations on column types

• Modify the col1 column in the tbl4 table to an auto-increment column.

  obclient> CREATE TABLE tbl4 (col1 BIGINT(10) NOT NULL,col2 INT);
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl4 MODIFY col1 BIGINT(10) AUTO_INCREMENT;
  Query OK, 0 rows affected
  

• Add the col1 column as the primary key of the tbl4 table and change the primary key to col2.

  obclient> ALTER TABLE tbl4 ADD PRIMARY KEY (col1);
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl4 DROP PRIMARY KEY,ADD PRIMARY KEY (`col2`);
  Query OK, 0 rows affected
  

• Add a CHECK constraint.

  obclient> CREATE TABLE tbl7(col1 VARCHAR(10),col2 VARCHAR(10));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl7 ADD CONSTRAINT my_check CHECK (col1> col2) ;
  Query OK, 0 rows affected
  

• Delete the CHECK constraint from the tbl7 table.

  obclient> ALTER TABLE tbl7 DROP CHECK my_check;
  Query OK, 0 rows affected
  

• Modify the column type to NOT NULL.

  obclient> CREATE TABLE dept(
      deptno           NUMBER(2,0),
      dname            VARCHAR(14),
      location         VARCHAR(13),
      CONSTRAINT pk_dept PRIMARY KEY(deptno)
  );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE emp(
      empno         NUMBER(4,0) NOT NULL,
      empname       VARCHAR(10) NOT NULL,
      job           VARCHAR(9) NOT NULL,
      mgr           NUMBER(4,0) NOT NULL,
      hiredate      DATE NOT NULL,
      sal           NUMBER(7,2) DEFAULT NULL,
      comm          NUMBER(7,2) DEFAULT NULL,
      deptno        NUMBER(2,0) DEFAULT NULL,
      CONSTRAINT PK_emp PRIMARY KEY (empno),
      CONSTRAINT FK_deptno  FOREIGN KEY (deptno)  REFERENCES dept (deptno)
  );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE emp MODIFY deptno NUMBER(2,0) DEFAULT '12' NOT NULL;
  Query OK, 0 rows affected
  

Change the column name and add a NOT NULL column constraint

obclient> ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10) NOT NULL;
Query OK, 0 rows affected (2.421 sec)

Add the NOT NULL constraint to two columns

Run the following command to add the NOT NULL constraint to the C1 and c2 columns:

obclient> ALTER TABLE test_alter4 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
Query OK, 0 rows affected (2.605 sec)

Change the type of a column with a prefix index

1. Run the following command to create a table named test_alter5 that contains the c1 column. The data type of the c1 column is CHAR(20), and a prefix index named idx_c1 is created on the c1 column:

   obclient> CREATE TABLE test_alter5 (c1 char(20), KEY idx_c1(c1(10)));
   

2. Execute the following command to change the data type of the c1 column to varchar(20):

   obclient> ALTER TABLE test_alter5 MODIFY COLUMN c1 varchar(20);
   

Change the foreign key constraint rules

1. Run the following command to create a table named t1 and add a unique constraint to the integer column c1:

   obclient> CREATE TABLE t1(c1 int unique key);
   

2. Run the following command to create a table named t2 that contains an integer column c1 and define a foreign key constraint:

   obclient> CREATE TABLE t2(c1 int, CONSTRAINT fk_cst_test FOREIGN KEY (c1) REFERENCES t1(c1) on update set null on delete no action);
   

3. Run the following command to query the system table INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS and obtain the update and delete rules of the foreign key constraint fk_cst_test:

   obclient> SELECT update_rule, delete_rule FROM information_schema.referential_constraints WHERE CONSTRAINT_NAME = 'fk_cst_test';
   

   The return result is as follows:

   +-------------+-------------+
   | update_rule | delete_rule |
   +-------------+-------------+
   | SET NULL    | NO ACTION   |
   +-------------+-------------+
   

   • UPDATE_RULE: SET NULL (the foreign key is set to NULL during an update).
   • DELETE_RULE: NO ACTION (a constraint is checked during a deletion, which may prevent the operation).

Rename a column

• The RENAME COLUMN statement does not change the column definition but only modifies the column name. If the target name already exists in the table, the RENAME COLUMN statement will fail. However, renaming the column back to its original name will not cause any errors.

  obclient> CREATE TABLE tbl8 (a INT, b INT);
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl8 RENAME COLUMN a TO b;
  ERROR 1060 (42S21): Duplicate column name 'b'
  
  obclient> ALTER TABLE tbl8 RENAME COLUMN a TO a;
  Query OK, 0 rows affected
  

• If an index exists on the column to be renamed, the RENAME COLUMN statement can still be executed, and the index definition will be automatically updated.

  obclient> CREATE TABLE tbl9 (a INT, b INT, index idx_a(a));
  Query OK, 0 rows affected
  
  obclient> SHOW INDEX FROM tbl9;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | tbl9  |          1 | idx_a    |            1 | a           | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  1 row in set
  
  obclient> ALTER TABLE tbl9 RENAME COLUMN a TO c;
  Query OK, 0 rows affected
  
  obclient> SHOW INDEX FROM tbl9;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | tbl9  |          1 | idx_a    |            1 | c           | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  1 row in set
  

• If the column to be renamed is referenced by a prefix index, the RENAME COLUMN statement can still be executed, and the prefix index supports cascading updates.

  DROP TABLE tbl9;
  obclient> CREATE TABLE tbl9 (c1 INT PRIMARY KEY, c2 BLOB, c3 INT, INDEX i1 (c2(10)));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl9 RENAME COLUMN c2 TO c2_, RENAME COLUMN c1 TO c2_1;
  DESC tbl9;
  Query OK, 0 rows affected
  
  obclient> SHOW INDEX FROM tbl9;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | tbl9  |          0 | PRIMARY  |            1 | c2_1        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
  | tbl9  |          1 | i1       |            1 | c2_         | A         |        NULL | 10       | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  2 rows in set
  

• If a foreign key constraint exists on the column to be renamed, the RENAME COLUMN statement can still be executed, and the foreign key constraint will be automatically updated.

  obclient> CREATE TABLE tbl10 (a INT PRIMARY KEY);
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl11(b INT, FOREIGN KEY (b) REFERENCES tbl10(a));
  
  obclient> SHOW CREATE TABLE tbl11;
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table                                                                                                                                                                                                                                                                                                                                                      |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | tbl11 | CREATE TABLE `tbl11` (
  `b` int(11) DEFAULT NULL,
  CONSTRAINT `tbl11_OBFK_1694681944513338` FOREIGN KEY (`b`) REFERENCES `test`.`tbl10`(`a`) ON UPDATE RESTRICT ON DELETE RESTRICT
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE =  16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  
  obclient> ALTER TABLE tbl10 RENAME COLUMN a TO c;
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLE tbl11;
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table                                                                                                                                                                                                                                                                                                                                                      |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | tbl11 | CREATE TABLE `tbl11` (
  `b` int(11) DEFAULT NULL,
  CONSTRAINT `tbl11_OBFK_1694681944513338` FOREIGN KEY (`b`) REFERENCES `test`.`tbl10`(`c`) ON UPDATE RESTRICT ON DELETE RESTRICT
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  

OceanBase Database does not support modifying or automatically updating the column name in the following scenarios:

• The column to be renamed is referenced by a generated list expression. Modifying the column name is not supported, and an error will be returned upon execution.

  obclient> CREATE TABLE tbl12(a INT, b INT AS (a + 1), c INT, d INT, CONSTRAINT d_check CHECK(d > 0)) PARTITION BY HASH(c + 1) PARTITIONS 2;
  
  obclient> ALTER TABLE tbl12 RENAME COLUMN a TO e;
  ERROR 3108 (HY000): Column 'a' has a generated column dependency
  

• The column to be renamed is referenced by a partitioning expression. Modifying the column name is not supported, and an error will be returned upon execution.

  obclient> ALTER TABLE tbl12 RENAME COLUMN c TO e;
  ERROR 3855 (HY000): Column 'c' has a partitioning function dependency and cannot be dropped or renamed.
  

• The column to be renamed is referenced by a CHECK constraint. Modifying the column name is not supported, and an error will be returned upon execution.

  obclient> ALTER TABLE tbl12 RENAME COLUMN d TO e;
  ERROR 3959 (HY000): Check constraint 'd_check' uses column 'd', hence column cannot be dropped or renamed.
  

• The column to be renamed is referenced by a function-based index. Modifying the column name is not supported, and an error will be returned upon execution.

  DROP TABLE IF EXISTS tbl12;
  obclient> CREATE TABLE tbl12(i INT, INDEX ((i+1)));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl12 RENAME COLUMN i TO j;
  ERROR 3837 (HY000): Column 'i' has a functional index dependency and cannot be dropped or renamed.
  

• The column to be renamed is referenced by a view. The RENAME COLUMN statement will execute successfully, but an error will be returned when querying the view. In this case, you must manually modify the view definition.

  obclient> CREATE TABLE tbl13(a INT);
  Query OK, 0 rows affected
  
  obclient> CREATE VIEW v1 AS SELECT a + 1 FROM tbl13;
  Query OK, 0 rows affected
  
  obclient> SELECT * FROM v1;
  Empty set
  
  obclient> ALTER TABLE tbl13 RENAME COLUMN a TO b;
  Query OK, 0 rows affected
  
  obclient> SELECT * FROM v1;
  ERROR 1356 (42S22): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
  

• The column to be renamed is referenced by a stored procedure. The RENAME COLUMN statement will execute successfully, but an error will be returned when you call the stored procedure. In this case, you must manually modify the stored procedure definition.

  obclient> CREATE TABLE tbl14(a INT);
  Query OK, 0 rows affected
  
  obclient> CREATE PROCEDURE proc() SELECT a + 1 FROM tbl14;
  Query OK, 0 rows affected
  
  obclient> CALL proc();
  Empty set
  
  obclient> ALTER TABLE tbl14 RENAME COLUMN a TO b;
  Query OK, 0 rows affected
  
  obclient> CALL proc();
  ERROR 1054 (42S22): Unknown column 'a' in 'field list'
  

Change the column store attribute of a table

1. Run the following statement to create a table named tbl1.

   CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50));
   

2. Convert the tbl1 table to a hybrid row-column store table and then delete the hybrid row-column store attribute.

   ALTER TABLE tbl1 ADD COLUMN GROUP(all columns, each column);
   

   ALTER TABLE tbl1 DROP COLUMN GROUP(all columns, each column);
   

3. Convert the tbl1 table to a column store table and then delete the column store attribute.

   ALTER TABLE tbl1 ADD COLUMN GROUP(each column);
   

   ALTER TABLE tbl1 DROP COLUMN GROUP(each column);
   

Modify the Skip Index attribute of a column

Run the following statement to create a table named test_skidx.

CREATE TABLE test_skidx(
  col1 INT SKIP_INDEX(MIN_MAX, SUM),
  col2 FLOAT SKIP_INDEX(MIN_MAX),
  col3 VARCHAR(1024) SKIP_INDEX(MIN_MAX),
  col4 CHAR(10)
  );

• Modify the Skip Index attribute of the col2 column in the test_skidx table to the SUM Skip Index type.

  ALTER TABLE test_skidx MODIFY COLUMN col2 FLOAT SKIP_INDEX(SUM);
  

• Set the Skip Index attribute of a new column to MIN_MAX Skip Index type after the table is created. Add the MIN_MAX Skip Index attribute to the col4 column in the test_skidx table.

  ALTER TABLE test_skidx MODIFY COLUMN col4 CHAR(10) SKIP_INDEX(MIN_MAX);
  

• Drop the Skip Index attribute of a column after the table is created. Drop the Skip Index attribute of the col1 column in the test_skidx table.

  ALTER TABLE test_skidx MODIFY COLUMN col1 INT SKIP_INDEX();
  

  or

  ALTER TABLE test_skidx MODIFY COLUMN col1 INT;
  

Modify table attributes

Disable the persistent macroblock-level Bloom filter for the table tb.

ALTER TABLE tb SET enable_macro_block_bloom_filter = False;

References

Change a table