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 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 you create a table, you can modify the collation and character set of the table. 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 collation and character set.

Here is an example:

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. Modify 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 modify the collation and character set of the existing data and change the collation and character set of the table. 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 table named test 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 field information is 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 rename operation 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 rename operation succeeds, but you must 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 is 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

Here is an example of renaming the c2 column of the test table to c.

obclient> ALTER TABLE test RENAME COLUMN c2 TO c;

After the rename operation, 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 rename operation will fail in the following scenarios:

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

  There is a special scenario where a cyclic renaming of columns is allowed. 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 allows the renaming operation to 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

Here is an example of changing the c2 column to the CHAR type.

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

Here is an example of renaming the c2 column to c and modifying its data type to CHAR.

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

After the operation, 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 Column skip index attribute.

Here is an example:

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

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

3. Add the skip index attribute to a newly added column after the table is created. 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);
   

4. Delete the skip index attribute from a column after the table is created. 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 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 the collation and character set of a column in 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 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 deletion, 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 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 deletion, 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 a unique index to a table that has been created. If you specify the primary key when you create 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 an index

  OceanBase Database allows you to drop one or more indexes by separating the names of the indexes 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;

Conversion between row-based storage and columnar storage

By default, when you create a table in OceanBase Database, the table uses row-based storage. You can specify the WITH COLUMN GROUP option to create a columnstore-redundant table or a rowstore-table that redundantly stores data in columnar format. After a table is created, you can use the ALTER TABLE statement to convert it between row-based storage and columnar storage.

Convert a rowstore table to a columnstore table

Here is an example:

1. Create a rowstore table named tbl1.

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

2. Convert the rowstore table tbl1 to a columnstore table.

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

Convert a rowstore table to a rowstore-columnstore redundant table

Here is an example:

1. Create a rowstore table named tbl2.

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

2. Convert the rowstore table tbl2 to a rowstore-columnstore redundant table.

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

Convert a rowstore-columnstore redundant table to a columnstore table

Here is an example:

1. Create a rowstore-columnstore redundant table named tbl3.

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

2. Convert the rowstore-columnstore redundant table tbl3 to a columnstore table.

   obclient> ALTER TABLE tbl3 DROP COLUMN GROUP(all columns);
   

Convert a rowstore-columnstore redundant table to a rowstore table

Here is an example:

1. Create a rowstore-columnstore redundant table named tbl4.

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

2. Convert the rowstore-columnstore redundant table tbl4 to a rowstore 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.