ALTER TABLE|V4.1.0| docs|Distributed Database

ALTER TABLE

Last Updated：2023-07-28 02:55:42 Updated

Purpose

You can use this statement to modify the structure of an existing table. For example, you can use this statement to modify a table and its attributes, add columns to it, modify its columns and attributes, and delete its columns.

Syntax

alter_table_stmt:
      ALTER TABLE table_name alter_table_action_list;

alter_table_action_list:
    alter_table_action [, alter_table_action ...]

alter_table_action:
      ADD [COLUMN] column_definition  
          [FIRST | BEFORE | AFTER column_name]
    | 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)
    | 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 {PARTITION | SUBPARTITION} partition_name_list
    | DROP TABLEGROUP
    | DROP FOREIGN KEY fk_name
    | DROP PRIMARY KEY [, ADD PRIMARY KEY (column)]
    | DROP CHECK constraint_name
    | MODIFY [COLUMN] column_definition
    | RENAME [TO] table_name
    | RENAME {INDEX | KEY} old_index_name TO new_index_name
    | REORGANIZE PARTITION name_list INTO partition_range_or_list
    | [SET] table_option_list
    | TRUNCATE {PARTITION | SUBPARTITION} partition_name_list

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]

match_action:
   MATCH {SIMPLE | FULL | PARTIAL}

opt_reference_option_list:
   reference_option [,reference_option ...]

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

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:
      table_group
    | block_size
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | comment
    | DUPLICATE_SCOPE [=] "none|zone|region|cluster"
    | parallel_clause

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

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]	Adds a column. You can add a generated column.
[FIRST \| BEFORE \| AFTER column_name]	Specifies the added column as the first column of the table or to be after the `column_name` column. Currently, OceanBase Database allows you to specify the position of a column only in the `ADD COLUMN` syntax.
CHANGE [COLUMN]	When you change the type of a column, you can only increase the column length for specific character data types, such as `VARCHAR`, `VARBINARY`, and `CHAR`.
MODIFY [COLUMN]	Modifies the column attribute.
ALTER [COLUMN] {SET DEFAULT const_value \| DROP DEFAULT}	You can change the default value of a column.
DROP [COLUMN]	Drops a column. You cannot drop a primary key column or columns that contain indexes.
ADD FOREIGN KEY	Adds a foreign key. If you do not specify the name of the foreign key, it will be named in the format of table name + `OBFK`+ time when the foreign key was created. For example, the foreign key created for table `t1` at 00:00:00 on August 1, 2021 is named as `t1_OBFK_1627747200000000`.
ALTER INDEX	Modifies whether an index is visible. When the status of an index is `INVISIBLE`, the SQL optimizer will not select this index.
ADD [PARTITION]	Adds a partition to a partitioned table.
DROP {PARTITION \| SUBPARTITION}	Drops partitions: `PARTITION`: drops the specified RANGE or LIST partitions, as well as all subpartitions that exist under these partitions. The partition definitions and partition data are also deleted, but the indexes defined on the partitioned table are maintained. `SUBPARTITION`: drops the specified `-RANGE` or `-LIST` subpartitions, including the subpartition definitions and subpartition data, and maintains the indexes defined on the partitioned table. Separate multiple partition names with commas (,). Notice Before you drop a partition, ensure that no active transactions or queries exist in this partition. Otherwise, SQL statement errors or exceptions may occur.
REORGANIZE [PARTITION]	Reorganizes a partition. Note: The current version does not support this parameter.
TRUNCATE {PARTITION \| SUBPARTITION}	Truncates partitions: `PARTITION`: deletes all data in the specified RANGE or LIST partitions, as well as data in all subpartitions that exist under these partitions, and maintains the indexes defined on the partitioned table. `SUBPARTITION`: deletes all data in the specified `-RANGE` or `-LIST` subpartitions, and maintains the indexes defined on the partitioned table. Separate multiple partition names with commas (,). Notice Before you delete partition data, ensure that no active transactions or queries exist in this partition. Otherwise, SQL statement errors or exceptions may occur.
RENAME [TO] table_name	Renames a table.
RENAME {INDEX \| KEY}	Renames an index or a key.
DROP [TABLEGROUP]	Drops a table group.
DROP [FOREIGN KEY]	Drops an external key.
DROP [PRIMARY KEY]	Drops a primary key. Note In OceanBase Database, you cannot drop a primary key from a MySQL tenant in the following conditions: The table is a parent table that contains foreign key information. The table is a child table, but the primary key column contains a foreign key reference column.
[SET] table_option	Sets table attributes. The following parameters are supported: `REPLICA_NUM`: sets the number of replicas of the table (not supported). `TABLE_GROUP`: sets the group to which the table belongs. `BLOCK_SIZE`: sets the microblock size of the table. Default Value: `16384`, which is 16 KB. Value range: [1024,1048576]. `COMPRESSION`: sets the compression mode of the table. Default value: `None`, which means that data is not compressed. `AUTO_INCREMENT`: sets the next value of the auto-increment column in the table. `comment`: sets the comments for the table. `DUPLICATE_SCOPE`: sets the replication mode for the table. `PROGRESSIVE_MERGE_NUM`: sets the number of progressive compaction steps. Value range: [1, 64]. `parallel_clause`: specifies the degree of parallelism (DOP) at the table level. `NOPARALLEL`: sets the degree of parallelism to `1`, which is the default value. `PARALLEL integer`: sets the degree of parallelism to an integer greater than or equal to 1.
CHECK	Modifies the `CHECK` constraint. The following operations are supported: Add a new CHECK constraint. Drop the current CHECK constraint named `constraint_name`.
[NOT] ENFORCED	Specifies whether to forcibly apply the CHECK constraint named `constraint_name`. If you do not specify this parameter or set it to `ENFORCED`, a CHECK constraint is created and forcibly applied. The default value is `ENFORCED`. If you set it to `NOT ENFORCED`, a CHECK constraint is created but not forcibly applied.

Examples

Add and drop columns

Create a table named tbl1 and execute the DESCRIBE statement 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 a column named c3 and execute the DESCRIBE statement 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 statement 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 statement 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 a column named c4 to the tbl1 table, set this column as the first column of the table, and execute the DESCRIBE statement 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 a column named c2 behind the c1 column and execute the DESCRIBE statement 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 a column named c5 before the c4 column and execute the DESCRIBE statement 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 a FOREIGN KEY constraint fk1 to the tbl2 table.

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);
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 of the tbl2 table.

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

Index operations

Rename the ind1 index on 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 to reference 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 indexes.

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

You can use the preceding method to implement an atomic change to an index in actual O&M scenarios.

Partition operations

Truncate partitions M202001 and M202002 of the t_log_part_by_range 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 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'))
         );

Change the DOP

Change the DOP of the tbl1 table to 2.
```
obclient> ALTER TABLE tbl3 PARALLEL 2;
```

Change the ordinary column col1 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 primary key column col1 to 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 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

Drop the CHECK constraint from the tbl7 table.

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

Change 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