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

Modify a table

Last Updated：2025-12-02 02:51:29 Updated

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

Considerations

Do not perform other DDL operations when you change the primary key or column type of a table. Likewise, do not change the primary key or column type of a table on which other DDL operations are being performed.

Add, modify, and drop columns

OceanBase Database allows you to add, modify, and drop columns.

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

Here is an example of adding a regular column:

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set

obclient> ALTER TABLE test ADD c3 int;
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL |
| C3    | NUMBER(38)   | YES  | NULL | NULL    | NULL |
+-------+--------------+------+-----+---------+-------+
3 rows in set

You can convert a column of one type into another type. OceanBase Database supports conversion between many column types.

For more information, see Rules for changing column types.

The SQL syntax for converting a column type is as follows:

ALTER TABLE table_name MODIFY (column_name data_type);

Here is an example:

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set

obclient> ALTER TABLE test MODIFY (c2 VARCHAR(70));
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(38)   | YES  | NULL | NULL    | NULL |
| C2    | VARCHAR2(70) | YES  | NULL | NULL    | NULL |
+-------+--------------+------+-----+---------+-------+
2 rows in set

You can rename a column. Here is an example:

obclient> ALTER TABLE test RENAME COLUMN c1 TO c;
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C     | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
| C2    | VARCHAR2(70) | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set

You can modify the default value of a column. Here is an example:

obclient> ALTER TABLE test MODIFY c DEFAULT 1;
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C     | NUMBER(38)   | YES  | NULL | 1       | NULL  |
| C2    | VARCHAR2(70) | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set

You can modify the NOT NULL constraint of a column. Here is an example:

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C     | NUMBER(38)   | YES  | NULL | 1       | NULL  |
| C2    | VARCHAR2(70) | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set

obclient> ALTER TABLE test MODIFY (c2 NOT NULL);
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C     | NUMBER(38)   | YES  | NULL | 1       | NULL  |
| C2    | VARCHAR2(70) | NO   | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set

You can drop a column from a table, but not a primary key column or a column that contains an index. Here is an example:

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set

obclient> ALTER TABLE test DROP COLUMN c2;
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
1 rows in set

Add a unique index

OceanBase Database allows you to add a unique index to an existing table. Here is an example:

obclient> CREATE TABLE test (c1 int PRIMARY KEY, c2 VARCHAR(50));
Query OK, 0 rows affected

obclient> ALTER TABLE test ADD UNIQUE(c2);

Rename a table

After a table is created, you can rename it.

OceanBase Database allows you to rename a table. Here is an example:

obclient> ALTER TABLE test RENAME TO t1;

obclient> RENAME test TO t1;

Add or drop a primary key or foreign key

After a table is created, you can add or drop a primary key or foreign key to or from the table. For more information, see Define a constraint type for a column.

Change the number of replicas of a table

After a table is created, you can also change the number of replicas of the table.

Here is an example of setting the number of replicas to 2:

obclient> ALTER TABLE test SET REPLICA_NUM=2;
Query OK, 0 rows affected

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 the Skip Index attribute, see Identify columns with the Skip Index attribute.

Here is an example:

Execute the following SQL statement to create a table named test_skidx.

CREATE TABLE test_skidx(
  col1 NUMBER SKIP_INDEX(MIN_MAX, SUM),
  col2 FLOAT SKIP_INDEX(MIN_MAX),
  col3 VARCHAR2(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 col2 FLOAT SKIP_INDEX(SUM);
```
Add the Skip Index attribute to a column after the column is created. Add the MIN_MAX Skip Index attribute to the col4 column in the test_skidx table.
```
ALTER TABLE test_skidx MODIFY col4 CHAR(10) SKIP_INDEX(MIN_MAX);
```
Drop the Skip Index attribute from a column after the column is created. Drop the Skip Index attribute from the col1 column in the test_skidx table.
```
ALTER TABLE test_skidx MODIFY col1 NUMBER SKIP_INDEX();
```

Convert a rowstore table into a columnstore table

By default, OceanBase Database builds a rowstore table when a table is created. You can use the WITH COLUMN GROUP option to explicitly specify to create a columnstore table or a hybrid rowstore-columnstore table. You can also use the ALTER TABLE statement to convert a rowstore table into a columnstore table after the table is created.

Convert a rowstore table into a columnstore table

Here is an example:

Create a rowstore table named tbl1.

obclient> CREATE TABLE tbl1(col1 NUMBER, col2 VARCHAR2(30));

Convert the tbl1 rowstore table into a tbl1 columnstore table.

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

Convert a rowstore table into a hybrid rowstore-columnstore table

Here is an example:

Create a rowstore table named tbl2.

obclient> CREATE TABLE tbl2(col1 NUMBER, col2 VARCHAR2(30));

Convert the tbl2 rowstore table into a hybrid rowstore-columnstore table.

obclient> ALTER TABLE tbl2 ADD COLUMN GROUP(all columns, each column);

Convert a hybrid rowstore-columnstore table into a columnstore table

Here is an example:

Create a hybrid rowstore-columnstore table named tbl3.

obclient> CREATE TABLE tbl3(col1 NUMBER, col2 VARCHAR2(30)) WITH COLUMN GROUP(all columns, each column);

Convert the tbl3 hybrid rowstore-columnstore table into a columnstore table.
```
obclient> ALTER TABLE tbl3 DROP COLUMN GROUP(all columns);
```

Convert a hybrid rowstore-columnstore table into a rowstore table

Here is an example:

Create a hybrid rowstore-columnstore table named tbl4.

obclient> CREATE TABLE tbl4(col1 NUMBER, col2 VARCHAR2(30)) WITH COLUMN GROUP(all columns, each column);

Convert the tbl4 hybrid rowstore-columnstore table into a rowstore 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.