Alter a table

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

Change a table's collation and character set

When you create a table, if you do not explicitly define the table's collation and character set, the database character set and collation are used by default. For more information about the database collation and character set, see Database-level character set and collation.

After a table is created, you can modify its collation and character set settings. 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 tables in OceanBase Database, see Table-level character set and collation.

Example:

1. Create the tbl1 table.

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

2. Modify the table's collation and character set.

   obclient> ALTER TABLE tbl1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;
   

   If the table already contains data, you can modify the collation and character set of the existing data in the table, and also modify the table's collation and character set to the corresponding settings. The following is an example:

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

Change the table structure

OceanBase Database supports adding columns, modifying columns and their attributes, and deleting columns.

Add columns

You can add columns to a table, but directly adding a primary key column is not supported. If you need 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 operations related to adding primary keys, see Define column constraint types.

Assume that there is a table test whose table structure 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 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
  

• Specify to add the c4 column after the c1 column.

  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
  

• Specify adding the c6 column before the c1 column.

  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 displayed fields are described as follows:

  • Field: The field information is the column name.

  • Type: The data type of the column.

  • Null: Indicates whether the column can be null. NO indicates that the column cannot be null, and YES indicates that the column can be null.

  • key: The field information displayed as PRI indicates that this column is a primary key column.

Modify column attributes

Renaming columns, modifying column types, and modifying default values of columns are supported.

• Rename a column

  When you use the RENAME COLUMN keyword to rename a column, note the following:

  • If the column to be renamed has an index or foreign key constraint, the column name can be modified successfully, and the index definition and foreign key constraint are automatically modified in cascade.

  • If the column to be renamed is referenced by a view or stored procedure, the column name can be modified successfully, but you must manually modify the definition of the view or stored procedure.

  • Renaming a column and dropping a column 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 a table named test has the following table structure.

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

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

  obclient> ALTER TABLE test RENAME COLUMN c2 TO c;
  

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

  Notice that renaming a column fails in the following scenarios:

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

    There is a special scenario: if the columns to be renamed form a cycle, the renaming can succeed. For example, in this sample, the original c1 column is renamed to c2, and the original c2 column is renamed to c1. If you execute the statement ALTER TABLE test RENAME COLUMN c1 TO c2, rename column c2 TO c1;, the renaming can succeed.

  • The column to be renamed is referenced by a generated column expression.

  • The column to be renamed is referenced by a partitioning expression.

  • The column to be renamed is referenced by a CHECK constraint.

• Modify a column type

  OceanBase Database does not support modifying the column type of foreign key columns or columns referenced by foreign keys.

  For the conversion rules and details of column types in MySQL mode of OceanBase Database, see Column type change rules.

  In addition, for primary key columns whose column type is INTEGER, modifying the column type supports Online DDL starting from V4.2.2.

  Assume that a table test exists, whose table structure 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 example modifies the c2 column of the test table to the BIGINT type.

  obclient> ALTER TABLE test MODIFY c1 BIGINT(50);
  

  After the modification, execute the statement DESCRIBE test to query the table structure. The result is as follows.

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

• Modify a column name and column type at the same time

  Assume that a table test exists, whose table structure 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 example 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);
  

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

• Modify the default value of a column.

  The following example modifies the default value of a column to 2:

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

  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  |     | 2       |       |
  +-------+-------------+------+-----+---------+-------+
  2 rows in set
  

  Alternatively, you can modify the default value of the column by using the following statement.

  ALTER TABLE table_name ALTER [COLUMN] column_name {SET DEFAULT const_value | DROP DEFAULT}
  

Modify the collation and character set of a column

If you do not specify the character set and collation of each column in the definition of each column when you add columns, each column uses the character set and collation of the table by default. You can modify the collation and character set settings of each column based on your business needs.

Assume that the table creation statement of table tbl1 is as follows.

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

The following example modifies the collation of the c2 column in table tbl1:

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

For more information about the collation and character set of columns in OceanBase Database, see Column-level character sets and collations.

Drop columns

You can drop regular columns and indexed columns from a table, but you cannot drop primary key columns.

Assume that table test has the following table structure.

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

• Drop a regular column from a table.

  Assume that table test has the following table structure.

  +-------+----------+------+-----+---------+-------+
  | 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 column c.

  obclient> ALTER TABLE test DROP c;
  

  After the column is dropped, 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
  

• Drop an indexed column from a table.

  Assume that there is a table test with the following table structure.

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

  After the column is dropped, execute the statement DESCRIBE test 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 supports adding unique indexes and normal indexes, and also supports modifying index attributes.

• Add a unique index

  OceanBase Database supports adding a unique index to a table after the table is created. If a primary key is also specified when the table is created, OceanBase Database creates a unique index for the primary key column by default.

  The following example shows how to add a unique index to a table:

  1. Create the test table.

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

  The following example shows how to add a normal index to a table:

  1. Create the test table.

     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 at the same time.

     obclient> ALTER TABLE test ADD INDEX myidx(c1,c2);
     

• Drop indexes

  OceanBase Database supports dropping indexes. When dropping multiple indexes, separate them with commas. You can use either INDEX or KEY as the index keyword. Example:

  obclient> ALTER TABLE test DROP KEY index_name, DROP KEY index_name1;
  

Change the primary key, foreign keys, and Check constraints of a table

OceanBase Database supports changing the primary key, foreign keys, and Check constraints of a table. For specific operations and details, see Define column constraint types.

Change the number of replicas for a table

Set the number of replicas for a table to 2. Example:

obclient> ALTER TABLE test SET REPLICA_NUM=2;

Rename a table

After a table is successfully created, you can change its name. OceanBase Database supports renaming tables.

Here is an example:

obclient> ALTER TABLE test RENAME TO t1;

Alternatively, you can use the following statement:

obclient> RENAME TABLE test TO t1;

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