Modify a table|V4.1.0| docs|Distributed Database

Modify a table

Last Updated：2023-08-01 06:02:28 Updated

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

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

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

Notice

The modifications affect the data storage methods of character columns that you add after the modifications take effect. The data storage methods of existing character columns are not modified.

Example:

Create a table named tbl1.

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

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. 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 a column to a table, modify a column and its attributes, and delete a column from a table.

Examples:

You can add columns to a table but you cannot directly add primary key columns. 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.

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

obclient> ALTER TABLE test ADD c3 int;
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+-------------+------+-----+---------+-------+
| 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 next to the c1 column.

obclient> ALTER TABLE test ADD COLUMN c4 INT NULL AFTER c1;
Query OK, 0 rows affected

obclient> DESC test;
+-------+-------------+------+-----+---------+-------+
| 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    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set

Add a column named c6 before the c1 column.

obclient> ALTER TABLE test ADD COLUMN c6 INT NULL BEFORE c1;
Query OK, 0 rows affected

obclient> DESC test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c6    | int(11)     | YES  |     | NULL    |       |
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c3    | int(11)     | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set

Parameters:

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.

You can change the name and 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.

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

obclient>ALTER TABLE test CHANGE COLUMN c2 c CHAR(60);
Query OK, 0 rows affected

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

The following sample code shows another supported syntax that you can use to modify the definition of a column:

obclient> ALTER TABLE test MODIFY c2 varchar(60);
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | NO   | PRI | NULL    |       |
| c2    | varchar(60) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set

Set the default value of a column to 2.

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

obclient> ALTER TABLE test CHANGE COLUMN c2 c2 varchar(50)  DEFAULT 2;
Query OK, 0 rows affected

obclient> DESC test;
+-------+-------------+------+-----+---------+-------+
| 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 syntax to change the default value of a column.

Syntax:

obclient> ALTER [COLUMN] {SET DEFAULT const_value | DROP DEFAULT}

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.
```
obclient> CREATE TABLE tbl1 (c1 int, c2 varchar(32), c3 varchar(32), PRIMARY KEY(c1), UNIQUE KEY uk1(c2));

obclient> ALTER TABLE tbl1 MODIFY COLUMN c2 varchar(32) COLLATE utf8mb4_bin;
```
For more information, see Column-level character sets and collations.

You can drop normal columns and indexed columns from a table but cannot drop primary key columns.

Drop a normal column from a table.

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

obclient> ALTER TABLE test DROP c;
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set

Drop an indexed column from a table.

obclient> CREATE TABLE test (c1 int, c2 varchar(32), c3 varchar(32), PRIMARY KEY(c1), INDEX(c2));
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+-------------+------+-----+---------+-------+
| 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

obclient> ALTER TABLE test DROP c2;
Query OK, 0 rows affected

obclient> DESCRIBE test;
+-------+-------------+------+-----+---------+-------+
| 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.

Examples:

Create 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.
```
obclient> CREATE TABLE test (c1 int PRIMARY KEY, c2 VARCHAR(50));
Query OK, 0 rows affected

obclient> ALTER TABLE test ADD UNIQUE INDEX index_name(c2);
Query OK, 0 rows affected
```
Create a normal index.

OceanBase Database allows you to create multiple indexes at a time. You can use the INDEX or KEY keyword.
```
obclient> CREATE TABLE test (c1 int PRIMARY KEY, c2 VARCHAR(50));
Query OK, 0 rows affected

obclient> ALTER TABLE test ADD INDEX myidx(c1,c2);
Query OK, 0 rows affected
```
myidx(c1,c2) indicates that an index is to be added to both the c1 and c2 columns. myidx is the index name.
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.
```
obclient> ALTER TABLE test DROP KEY index_name, DROP KEY index_name1;
Query OK, 0 rows affected
```

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. Example:
```
obclient> ALTER TABLE test SET REPLICA_NUM=2;
Query OK, 0 rows affected
```

Rename a table

You can rename an existing table. OceanBase Database allows you to rename a table.

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.