Modify a table|V4.2.2| docs|Distributed Database

Modify a table

Last Updated：2025-12-02 02:50:56 Updated

After a table is created, you can use the ALTER TABLE statement to modify the table.

Change the collation and character set of a table

If you do not explicitly specify the collation and character set of a table when you create the table, the character set and collation of the database are used by default. For more information about the collation and character set of a database, see Database-level character set and collation.

After a table is created, you can change its collation and character set. The statement is as follows:

ALTER TABLE table_name [[DEFAULT] CHARACTER SET [=] charset_name] [COLLATE [=] collation_name];

For more information about the collation and character set of a table in OceanBase Database, see Table-level character set and collation.

Notice

After you modify the collation and character set of a table, the data storage method for characters in existing columns of the specified collation and character set is not changed.

Here are some examples:

Create a table named tbl1.

obclient> CREATE TABLE tbl1 (c1 int, c2 varchar(32), c3 varchar(32), PRIMARY KEY(c1), UNIQUE KEY uk1(c2));

Change the collation and character set of the table.
```
obclient> ALTER TABLE tbl1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;
```
If the table contains data, you can also change the collation and character set of the data in the table and modify the table's collation and character set to the new settings. Here is an example:
```
obclient> ALTER TABLE tbl1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
```

Modify table schema

OceanBase Database supports adding, modifying, and dropping columns and their attributes.

Add a column

You can add a column to a table, but you cannot directly add a primary key column. If you want to add a primary key column, you can add the column first and then add a primary key constraint to the column. For more information about how to add a primary key constraint, see Define a constraint type for a column.

Assume that there is a test table with the following structure.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set

Add an arbitrary column c3.

obclient> ALTER TABLE test ADD c3 int;

After the addition, execute the DESCRIBE test statement to query the table structure, which is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
| c3    | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set

Add c4 after column c1.

obclient> ALTER TABLE test ADD COLUMN c4 INT NULL AFTER c1;

After the addition, execute the DESCRIBE test statement to query the table structure, which is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c4    | int(11)     | YES  |     | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
| c3    | int(11)     | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set

Add c6 before column c1.

obclient> ALTER TABLE test ADD COLUMN c6 INT NULL BEFORE c1;

After the addition, execute the DESCRIBE test statement to query the table structure, which is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c6    | int(11)     | YES  |     | NULL    |       |
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c4    | int(11)     | YES  |     | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
| c3    | int(11)     | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set

The following table explains the displayed fields:

Field: the field information column that contains the column names.
Type: the data type column.
Null: the column that indicates whether the values in the column can be NULL. The value NO indicates that the values can be NULL, and the value YES indicates that the values cannot be NULL.
Key: the column that displays PRI for a primary key column.

Modify column attributes

You can rename a column, modify the column type, and change the default value of a column.

Rename a column

Take note of the following considerations when you use the RENAME COLUMN clause to rename a column:
- If the target column has an index or a foreign key constraint, the renaming succeeds and the index definitions and foreign key constraints are automatically updated.
- If the target column is referenced by a view or stored procedure, the renaming succeeds but you need to manually update the view or stored procedure.
- Renaming a column and dropping the column at the same time are not supported.
- Renaming a column and modifying partitions (adding or dropping partitions) at the same time are not supported.
Assume that there is a test table with the following structure.
```
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set
```
The following example shows how to rename the c2 column of the test table to c.
```
obclient> ALTER TABLE test RENAME COLUMN c2 TO c;
```
After the renaming, the execution result of the DESCRIBE test statement is as follows.
```
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c     | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set
```
Note that the following scenarios will cause the column renaming to fail:
- The target column name already exists in the current table.
  
  A special scenario is that if a cyclic renaming occurs, the renaming can be successful. For example, in this example, the c1 column is renamed to c2, and the c2 column is renamed to c1 at the same time. The execution result of the ALTER TABLE test RENAME COLUMN c1 TO c2, rename column c2 TO c1; statement is successful.
- The target column is referenced in a generated list expression.
- The target column is referenced in a partitioning expression.
- The target column is referenced in a CHECK constraint.

Modify the column type

OceanBase Database does not support modifying the type of a foreign key column or a column that is referenced by a foreign key.

For more information about the conversion rules of column types and details, see Column type change rules.

In addition, for a primary key column of the INTEGER type, Online DDL is supported for changing the column type starting from V4.2.2.

Assume that there is a test table with the following structure.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set

The following example shows how to change the c2 column of the test table to the BIGINT type.

obclient> ALTER TABLE test MODIFY c1 BIGINT(50);

After the change, the execution result of the DESCRIBE test statement is as follows.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | bigint(50)  | NO   | PRI | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set

Change the column name and type at the same time

Assume that there is a test table with the following structure.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set

The following example shows how to rename the c2 column of the test table to c and change its data type to CHAR.

obclient> ALTER TABLE test CHANGE COLUMN c2 c CHAR(60);

After the change, the execution result of the DESCRIBE test statement is as follows.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c     | char(60)    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set

Change the default value of a column.

The following example shows how to change the default value to 2:

obclient> ALTER TABLE test CHANGE COLUMN c c2 varchar(50) DEFAULT 2;

After the change, the execution result of the DESCRIBE test statement is as follows.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(50) | YES  |     | 2       |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set

You can also use the following statement to change the default value of a column.

ALTER TABLE table_name ALTER [COLUMN] column_name {SET DEFAULT const_value | DROP DEFAULT}

Change the collation and character set of a column

If you do not specify the character set and collation for a column when you add the column, the column uses the character set and collation specified for the table. You can change the character set and collation of a column based on your business requirements.

Assume that the CREATE TABLE statement for the tbl1 table is as follows:

obclient> CREATE TABLE tbl1 (c1 int, c2 varchar(32), c3 varchar(32), PRIMARY KEY(c1), UNIQUE KEY uk1(c2));

Here is an example of changing the collation of the c2 column in the tbl1 table:

obclient> ALTER TABLE tbl1 MODIFY COLUMN c2 varchar(32) COLLATE utf8mb4_bin;

For more information about columns and collations in OceanBase Database, see Column-level character sets and collations.

Drop a column

You can drop a regular column or an indexed column, but you cannot drop a primary key column.

Assume that the test table has the following structure.

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1    | int(11)  | NO   | PRI | NULL    |       |
| c     | char(60) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set

Drop a regular column from a table.

Assume that the test table has the following structure.

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1    | int(11)  | NO   | PRI | NULL    |       |
| c     | char(60) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set

Execute the following statement to drop the c column from the test table.

obclient> ALTER TABLE test DROP c;

After the c column is dropped, execute the DESCRIBE test statement to query the table structure. The result is as follows:

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set

Drop an indexed column from a table.

Assume that the test table has the following structure.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(32) | YES  | MUL | NULL    |       |
| c3    | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set

Execute the following statement to drop the c2 column from the test table.

obclient> ALTER TABLE test DROP c2;

After the c2 column is dropped, execute the DESCRIBE test statement to query the table structure. The result is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c3    | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set

Modify indexes

OceanBase Database allows you to add unique and normal indexes, and modify the attributes of indexes.

Add a unique index

OceanBase Database allows you to add unique indexes to a table that has been created. If you specified a primary key when you created a table, OceanBase Database creates a unique index on the primary key column by default.

Here is an example of adding a unique index to a table:
1. Create a table named test.
```
obclient> CREATE TABLE test (c1 int PRIMARY KEY, c2 VARCHAR(50));
```
2. Add a unique index to the table.
```
obclient> ALTER TABLE test ADD UNIQUE INDEX index_name(c2);
```
Add a normal index

OceanBase Database allows you to add multiple indexes at a time, and you can use the INDEX or KEY keyword.

Here is an example of adding a normal index to a table:
1. Create a table named test.
```
obclient> CREATE TABLE test (c1 int PRIMARY KEY, c2 VARCHAR(50));
```
2. Add an index named myidx to the c1 and c2 columns of the table.
```
obclient> ALTER TABLE test ADD INDEX myidx(c1,c2);
```
Drop indexes

OceanBase Database allows you to drop one or more indexes by separating the index names with commas. You can use the INDEX or KEY keyword. Here is an example:
```
obclient> ALTER TABLE test DROP KEY index_name, DROP KEY index_name1;
```

Change the primary key, foreign key, or CHECK constraint of a table

OceanBase Database allows you to change the primary key, foreign key, or CHECK constraint of a table. For more information, see Define a constraint on a column.

Change the number of replicas of a table

Set the number of replicas of the table to 2. Example:

obclient> ALTER TABLE test SET REPLICA_NUM=2;

Rename a table

After a table is created, you can change the table name. OceanBase Database supports renaming a table.

Here is an example:

obclient> ALTER TABLE test RENAME TO t1;

You can also use the following statement:

obclient> RENAME TABLE test TO t1;

For more information about the ALTER TABLE statement, see ALTER TABLE.