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 delete 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 and default value 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 data type of a column

  For more information about the rules for changing the data types of columns in MySQL mode, 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}
  

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

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