ALTER TABLE

Purpose

This statement is used to modify the structure of an existing table, such as modifying the table and its properties, adding columns, modifying columns and their properties, and deleting columns.

Syntax

ALTER TABLE table_name [alter_option [, alter_option] ...]
    [partition_options]

alter_option: {
      table_options
      ADD [COLUMN] col_name column_definition  
          [FIRST | BEFORE | AFTER column_name]
    | ADD [COLUMN] (col_name column_definition, ...)
    | ADD {INDEX | KEY} [index_name]
          [index_type] (key_part,...) [index_option [ index_option ...]
    | 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][reference_option [,reference_option ...]]
    | ADD PRIMARY KEY (column_name)
    | ADD CONSTRAINT [constraint_name] CHECK (expression)[[NOT] ENFORCED]
    | ADD PARTITION (range_partition [, range_partition ...])
    | 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 [COLUMN] column_name
    | DROP {PARTITION | SUBPARTITION} partition_name [, partition_name ...]
    | DROP TABLEGROUP
    | DROP FOREIGN KEY fk_name
    | DROP PRIMARY KEY [, ADD PRIMARY KEY (column)]
    | DROP INDEX index_name
    | DROP CHECK constraint_name
    | MODIFY [COLUMN] column_definition
    | RENAME COLUMN old_col_name TO new_col_name
    | RENAME [TO] table_name
    | RENAME {INDEX | KEY} old_index_name TO new_index_name
    | [SET] table_option [ table_option ...]
    | TRUNCATE {PARTITION | SUBPARTITION} partition_name [, partition_name ...]
    | MODIFY PARTITION partition_name ADD SUBPARTITION (add_subpartition_option)
    | FORCE

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

index_desc:
   (column_desc [, column_desc ...]) [index_type] [index_option [ index_option ...]]

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

match_action:
   MATCH {SIMPLE | FULL | PARTIAL}

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

key_part:
    {index_col_name [(length)] | (expr)} [ASC | DESC]

index_type:
    USING BTREE

index_option:
     [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name [, column_name ...])
    | comment
    | KEY_BLOCK_SIZE [=] INT_VALUE

table_option:
      TABLEGROUP = tablegroup_name
    | block_size
    |lob_inrow_threshold [=] num
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | comment
    | parallel_clause
    | READ {ONLY \| WRITE}
    | KEY_BLOCK_SIZE [=] INT_VALUE
    | TABLE_MODE [=] 'table_mode_value'
    | auto_increment_cache_size [=] INT_VALUE
    | DUPLICATE_SCOPE= 'none | cluster'

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

table_mode_value:
    NORMAL
    | QUEUING
    | MODERATE
    | SUPER
    | EXTREME

partition_options:
    partition_option [partition_option] ...

partition_option:
      PARTITION BY HASH(expression)
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY KEY([column_name [, column_name ...]])
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY RANGE {(expression) | COLUMNS (column_name [, column_name ...])}
      [subpartition_option] (range_partition [, range_partition ...])

subpartition_option:
      SUBPARTITION BY HASH(expression)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY KEY(column_name [, column_name ...])
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name [, column_name ...])}
      (range_subpartition [, range_subpartition ...])

range_partition:
    PARTITION partition_name
    VALUES LESS THAN {(expression [, expression ...]) | MAXVALUE}

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression [, expression ...]) | MAXVALUE}

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] ...}

Parameters

table_mode_value

• NORMAL: The default value. In this mode, the probability of triggering a compaction after a data dump is extremely low.
• QUEUING: In this mode, the probability of triggering a compaction after a data dump is low.
• MODERATE: In this mode, the probability of triggering a compaction after a data dump is moderate.
• SUPER: In this mode, the probability of triggering a compaction after a data dump is high.
• EXTREME: In this mode, the probability of triggering a compaction after a data dump is very high.

For more information about compaction, see Adaptive compaction.

Examples

Add and drop columns

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

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 to c3 in the tbl1 table 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. 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 in the tbl1 table 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 in the tbl1 table 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 fk1 foreign key constraint to the tbl2 table. When an UPDATE operation affects the key values in the parent table 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 fk1 foreign key constraint 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 ind1 index to ind2 in the tbl2 table.

  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 the ind3 index on the tbl2 table, referencing the c1 and c2 columns.

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

  You can execute 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 ind2 index from the tbl2 table.

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

  Note

  In actual O&M scenarios, you can perform atomic index changes by using the preceding methods.

• Add three function indexes to the t1_func table. One of them is named i2, and the other two are automatically generated by the system with names in the format of functional_index plus 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
  

• Set the index to be invisible.

  Note

  After an index is set to be invisible, you can see the /*!80000 INVISIBLE */ tag in the table structure.

  1. Create the tbl4 table.

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

  2. Create the idx1_tbl4 index.

     CREATE INDEX idx1_tbl4 ON tbl4(col2);
     

  3. Set the idx1_tbl4 index to be 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 |
     +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     1 row in set
     

Partition operations

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

  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 t_log_part_by_range partitioned table.

  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'))
           );
  

Modify the parallelism

• Set the parallelism of the tbl3 table to 2.

  obclient> ALTER TABLE tbl3 PARALLEL 2;
  

Column type-related operations

• Change the col1 column of 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 then 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 the 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
  

• Drop the CHECK constraint from the tbl7 table.

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

• Set 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
  

Rename a column

• The RENAME COLUMN statement does not change the column definition, only the column name. If the target name already exists in the table, the RENAME COLUMN statement will fail, but renaming to the original name will not.

  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 the column to be renamed has an index, the RENAME COLUMN statement can be executed normally, 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 be executed normally, 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 the column to be renamed has a foreign key constraint, the RENAME COLUMN statement can be executed normally, 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 cascading updates in the following scenarios:

• If 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.

  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
  

• If the column to be renamed is referenced by a partitioning expression, modifying the column name is not supported, and an error will be returned.

  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.
  

• If the column to be renamed is referenced by a CHECK constraint, modifying the column name is not supported, and an error will be returned.

  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.
  

• If the column to be renamed is referenced by a function index, modifying the column name is not supported, and an error will be returned.

  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.
  

• If the column to be renamed is referenced by a view, the RENAME COLUMN statement will succeed, but querying the view will return an error. You need to 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
  

• If the column to be renamed is referenced by a stored procedure, the RENAME COLUMN statement will succeed, but calling the procedure will return an error. You need to manually modify the procedure.

  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'
  

References

Change a table