Modify a table

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 table uses the character set and collation of the database by default. For more information about the collation and character set of a database, see Database-level character set and collation.

After you create a table, you can change its collation and character set. Execute the following statement:

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.

Here are some examples:

1. Create a table named tbl1.

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

2. 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 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 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 schema.

+-------+-------------+------+-----+---------+-------+
| 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 schema. 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 schema. 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 schema. 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 the column allows NULL values.

  • Key: the column is a primary key column if it is marked with PRI.

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 must manually update the definitions of the view or stored procedure.

• Dropping a column and renaming it at the same time are 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, the execution result of the DESCRIBE test statement, which is used to query the table structure, is as follows.

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

Take note of the following scenarios in which renaming a column will fail:

• The target column name already exists in the current table.

  A special scenario is that if a cyclic column renaming occurs, the renaming can be successful. For example, in this example, the original c1 column is renamed to c2, and the original c2 column is renamed to c1. The execution of the ALTER TABLE test RENAME COLUMN c1 TO c2, rename column c2 TO c1; statement can be successful.

• 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, the execution result of the DESCRIBE test statement, which is used to query the table structure, is as follows.

+-------+-------------+------+-----+---------+-------+
| 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, the execution result of the DESCRIBE test statement, which is used to query the table structure, is as follows.

+-------+-------------+------+-----+---------+-------+
| 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 modifying the default value to 2:

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

After the modification, the execution result of the DESCRIBE test statement, which is used to query the table structure, 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 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 Column skip index attribute.

Here is an example:

1. Create a table named test_skidx by using the following SQL statement:

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

2. Modify the skip index attribute of the col2 column in the test_skidx table to SUM skip index type.

   ALTER TABLE test_skidx MODIFY COLUMN col2 FLOAT SKIP_INDEX(SUM);
   

3. Add the skip index attribute to a newly added column after the table is created. Add the MIN_MAX skip index type to the col4 column in the test_skidx table.

   ALTER TABLE test_skidx MODIFY COLUMN col4 CHAR(10) SKIP_INDEX(MIN_MAX);
   

4. Drop the skip index attribute from a column after the table is created. Drop 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 character set and collation of the table by default. 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 the collation and character set of a column in OceanBase Database, see Column character set and collation.

Delete columns

You can delete both regular 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 regular column from the 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;
  

  After the operation, execute the DESCRIBE test statement to query the table structure. The execution 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 the 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;
  

  After the operation, execute the DESCRIBE test statement to query the table structure. The execution 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 is created later. If you set a primary key when you create a table, OceanBase Database will create a unique index for 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 indexes using the INDEX or KEY keyword. Use commas to separate multiple indexes to be dropped. 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. After a table is created, you can use the ALTER TABLE statement to convert it between a row-store table and a column-store table.

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

Here is an example:

1. Create a row-store table named tbl1.

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

2. 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:

1. Create a row-store table named tbl2.

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

2. 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:

1. 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);
   

2. 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:

1. 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);
   

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

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

   or

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

References

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