Modify a table

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

Modify the collation and character set of a table

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

After you create a table, you can modify the collation and character set of the table. The syntax is as follows:

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

For more information, see Table-level character sets and collations.

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 then modify the collation and character set of the table accordingly. Here is an example:

   obclient> ALTER TABLE tbl1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
   

Modify the schema of a table

OceanBase Database allows you to add columns, modify a column and its attributes, and drop a column.

Add columns

You can add columns other than a primary key column to a table. To add a primary key column, you can add a normal column and then add a primary key to the column. For more information, see Define column constraints.

Assume that the schema of a table named test is as follows:

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

• Add a column named c3.

  obclient> ALTER TABLE test ADD c3 int;
  

  Execute the DESCRIBE test statement to query the table schema. The query 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 a column named c4 after the c1 column.

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

  Execute the DESCRIBE test statement to query the table schema. The query 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 a column named c6 before the c1 column.

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

  Execute the DESCRIBE test statement to query the table schema. The query 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 parameters are described as follows:

  • Field: the column name.

  • Type: the data type of the column.

  • Null: indicates whether the column can contain NULL values. The value NO indicates that the column cannot contain NULL values, whereas the value YES indicates that the column can contain NULL values.

  • Key: indicates whether the column is a primary key column. The value PRI indicates a primary key column.

Modify column attributes

You can rename a column, and modify the data type, default value, and skip index attribute of a column.

Rename a column

When you rename a column by using the RENAME COLUMN keyword, observe the following notes:

• A column that is indexed or constrained by a FOREIGN KEY constraint can be renamed, and the RENAME COLUMN operation is automatically cascaded to the index definition and the FOREIGN KEY constraint.

• A column that is referenced by a view or stored procedure can be renamed, but you need to manually modify the definition of the view or stored procedure.

• You cannot rename and drop columns at the same time.

• You cannot rename columns and modify partitions, such as adding or dropping partitions, at the same time.

Assume that the schema of a table named test is as follows:

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

The following sample code renames the c2 column of the test table to c.

obclient> ALTER TABLE test RENAME COLUMN c2 TO c;

Execute the DESCRIBE test statement to query the table schema. The query result is as follows:

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

Your operation to rename a column fails in the following scenarios:

• The current table already contains a column of the specified new name.

  However, you can address this issue by performing a loop operation. For example, you can rename the c1 column to c2 and the original c2 column to c1 by executing the ALTER TABLE test RENAME COLUMN c1 TO c2, rename column c2 TO c1; statement.

• The column is referenced by an expression of a generated column.

• The column is referenced by a partitioning expression.

• The column is referenced by a CHECK constraint.

Change the type of a column

For more information about the rules for changing the data types of columns in the MySQL mode of OceanBase Database, see Column type change rules.

Assume that the schema of a table named test is as follows:

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

The following sample code changes the data type of the c2 column of the test table to CHAR.

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

Execute the DESCRIBE test statement to query the table schema. The query result 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 a column and change its data type at the same time

Assume that the schema of a table named test is as follows:

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

The following sample code renames the c2 column of the test table to c and changes its data type to CHAR.

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

Execute the DESCRIBE test statement to query the table schema. The query result 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 sample code changes the default value of the c2 column to 2.

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

Execute the DESCRIBE test statement to query the table schema. The query result 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}

Modify the skip index attribute of a column

OceanBase Database allows you to use the ALTER TABLE statement to add, modify, and delete the skip index attribute.

For more information about the skip index attribute, see Column skip index attribute.

Here is an example:

1. Use 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. Change the type of the skip index on 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 for a column after the table is created. That is, add a skip index of the MIN_MAX type for 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 for a column after the table is created. That is, delete the skip index attribute of 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;
   

Modify the collation and character set of a column

If you do not specify the character set or collation of a column when you add the column to a table, the character set and collation of the table are used by default. You can modify the collation and character set of each column based on your business requirements.

Assume that a table named tbl1 is created by using the following statement:

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

The following sample code changes the collation of the c2 column of the tbl1 table.

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

For more information, see Column-level character sets and collations.

Drop a column

OceanBase Database allows you to drop normal columns and indexed columns from a table. However, you cannot directly drop a primary key column.

Assume that the schema of a table named test is as follows:

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

• Drop a normal column from a table.

  Assume that the schema of a table named test is 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 drop the c column:

  obclient> ALTER TABLE test DROP c;
  

  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    |       |
  +-------+---------+------+-----+---------+-------+
  1 row in set
  

• Drop an indexed column from a table.

  Assume that the schema of a table named test is 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 drop the c2 column:

  obclient> ALTER TABLE test DROP c2;
  

  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    |       |
  | c3    | varchar(32) | YES  |     | NULL    |       |
  +-------+-------------+------+-----+---------+-------+
  2 rows in set
  

Modify indexes

You can create unique and normal indexes on a table and modify index attributes.

• Add a unique index

  After you create a table, you can create a unique index on the table. If a primary key is specified when you create the table, OceanBase Database creates a unique index on the primary key column by default.

  Here is an example:

  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 create multiple indexes at a time. You can use either the INDEX or KEY keyword.

  Here is an example:

  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 multiple indexes at a time. You need to separate multiple 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;
  

Modify the PRIMARY KEY, FOREIGN KEY, and CHECK constraints of a table

For more information, see Define column constraints.

Change the number of replicas of a table

You can change the number of replicas of a table. For example, you can change the number to 2. Here is an example:

obclient> ALTER TABLE test SET REPLICA_NUM=2;

Rename a table

You can rename an existing table. OceanBase Database allows you to rename 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;

Convert a rowstore table to a columnstore table

When you create a table in OceanBase Database, the table is a rowstore table by default. You can use the WITH COLUMN GROUP option to explicitly specify to create a columnstore table or a rowstore-columnstore redundant table. You can convert a rowstore table to a columnstore table by using the ALTER TABLE statement.

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

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

Convert a rowstore table into 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 into a rowstore-columnstore redundant table.

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

Convert a rowstore-columnstore redundant table into 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 into a columnstore table.

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

Convert a rowstore-columnstore redundant table into 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 into 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.