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

Modify a table

Last Updated：2025-12-03 09:13:04 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 default collation and character set of the database are used. 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 new characters added to existing columns is changed, but the data storage method for existing characters in columns 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 change the collation and character set of the existing 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 the table schema

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

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 to the column. For more information about how to add a primary key, 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 modification, 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    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
| c3    | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set

Add c4 after c1.

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

After the modification, 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    |       |
| c4    | int(11)     | YES  |     | NULL    |       |
| c2    | varchar(50) | YES  |     | NULL    |       |
| c3    | int(11)     | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set

Add c6 before c1.

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

After the modification, execute the DESCRIBE test statement to query the table structure. The result 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 describes the displayed fields.

Field: the column name.
Type: the data type of the column.
Null: whether the column allows NULL values. NO indicates that the column does not allow NULL values, and YES indicates that it does.
Key: the field information prefixed with PRI is a primary key column.

Modify column attributes

You can rename a column, change its data type, default value, and Skip Index attribute.

Rename a column

Take note of the following considerations when you use the RENAME COLUMN clause to rename a column:

If the column to be renamed has an index or a foreign key constraint, the renaming succeeds, and the index definitions and foreign key constraints are automatically updated.
If the column to be renamed is referenced by a view or stored procedure, the renaming succeeds, but you need to manually update the definitions of the views or stored procedures.
Dropping a column and renaming the column at the same time is not supported.
Renaming a column and modifying partitions (such as 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

An example of renaming the c2 column of the test table to c is as follows.

obclient> ALTER TABLE test RENAME COLUMN c2 TO c;

After the renaming, executing the DESCRIBE test statement returns the following result.

+-------+-------------+------+-----+---------+-------+
| 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 renaming operation to fail:

The target column name already exists in the current table.

A special scenario is that if the column to be renamed appears in a cyclic reference, the renaming can still be performed. For example, in this example, the c1 column is renamed to c2, and the c2 column is renamed to c1 at the same time. Executing the ALTER TABLE test RENAME COLUMN c1 TO c2, rename column c2 TO c1; statement can still succeed.
The column to be renamed is referenced in a generated list expression.
The column to be renamed is referenced in a partitioning expression.
The column to be renamed is referenced in a CHECK constraint.

Change the column type

For more information about the conversion rules of column types in MySQL mode of OceanBase Database, see Column type change rules.

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

An example of changing the c2 column of the test table to the CHAR type is as follows.

obclient> ALTER TABLE test MODIFY c2 CHAR(60);

After the change, executing the DESCRIBE test statement returns the following result.

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

Rename and modify the column 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

An example of renaming the c2 column of the test table to c and modifying its data type to the CHAR type is as follows.

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

After the renaming and modification, executing the DESCRIBE test statement returns the following result.

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

Modify the default value of a column

Here is an example of setting the default value to 2:

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

After the modification, executing the DESCRIBE test statement returns the following result.

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

You can also modify the default value of a column by using the following statement:

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

Modify the Skip Index attribute of a column

OceanBase Database allows you to use the ALTER TABLE statement to add, modify, and drop the Skip Index attribute.

For more information about Skip Index, see Skip index attribute.

Here is an example:

Execute the following SQL 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 SUM.
```
ALTER TABLE test_skidx MODIFY COLUMN col2 FLOAT SKIP_INDEX(SUM);
```
Add the Skip Index attribute to a newly added column after table creation. 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);
```
Delete the Skip Index attribute from a column after table creation. Delete the Skip Index attribute from 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;
```

Change the collation and character set of a column

If you do not specify the character set and collation for a column, the column uses the collation and character set specified for the table. You can change the collation and character set 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 the collations and character sets supported by OceanBase Database, see Column character set and collation.

Delete columns

You can delete both normal columns and indexed columns, but you cannot delete a primary key column.

Assume that a table named test exists. The table structure is described as follows.

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

Delete a normal column from a table.

Assume that a table named test exists. The table structure is described as follows.

+-------+----------+------+-----+---------+-------+
| 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 delete the c column from the test table.

obclient> ALTER TABLE test DROP c;

Then, 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

Delete an indexed column from a table.

Assume that a table named test exists. The table structure is described as follows.

+-------+-------------+------+-----+---------+-------+
| 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 delete the c2 column from the test table.

obclient> ALTER TABLE test DROP c2;

Then, 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 an index

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. You can use the INDEX or KEY keyword to add an index.

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 an index

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

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

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

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;

Conversion between row-store and column-store tables

By default, when you create a table in OceanBase Database, the table is a row-store table. You can specify the WITH COLUMN GROUP option to create a column-store or hybrid row-store and column-store table. You can use the ALTER TABLE statement to convert a row-store table into a column-store or hybrid row-store and column-store table.

Convert a row-store table to a column-store table

Here is an example:

Create a row-store table named tbl1.

obclient> CREATE TABLE tbl1(col1 INT, col2 VARCHAR(30), col3 DATE);

Convert the row-store table tbl1 to a column-store table.

obclient> ALTER TABLE tbl1 ADD COLUMN GROUP(each column);

Convert a row-store table to a hybrid row-store and column-store table

Here is an example:

Create a row-store table named tbl2.

obclient> CREATE TABLE tbl2(col1 INT, col2 VARCHAR(30), col3 DATE);

Convert the row-store table tbl2 to a hybrid row-store and column-store table.
```
obclient> ALTER TABLE tbl2 ADD COLUMN GROUP(all columns, each column);
```

Convert a hybrid row-store and column-store table to a column-store table

Here is an example:

Create a hybrid row-store and column-store table named tbl3.

obclient> CREATE TABLE tbl3(col1 INT, col2 VARCHAR(30), col3 DATE) WITH COLUMN GROUP(all columns, each column);

Convert the hybrid row-store and column-store table tbl3 to a column-store table.
```
obclient> ALTER TABLE tbl3 DROP COLUMN GROUP(all columns);
```

Convert a hybrid row-store and column-store table to a row-store table

Here is an example:

Create a hybrid row-store and column-store table named tbl4.

obclient> CREATE TABLE tbl4(col1 INT, col2 VARCHAR(30), col3 DATE) WITH COLUMN GROUP(all columns, each column);

Convert the hybrid row-store and column-store table tbl4 to a row-store table.

obclient> ALTER TABLE tbl4 DROP COLUMN GROUP(each column);

obclient> ALTER TABLE tbl4 DROP COLUMN GROUP(all columns, each column);

References

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