Modify a table |V2.2.77|OceanBase Database| docs|Distributed Database

Modify a table

Last Updated：2023-08-18 09:26:34 Updated

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

Add, modify, and delete a column

OceanBase Database allows you to add a column to a table, modify the column and its attributes, and delete a column from a table.

You cannot add a primary key column to a table.

Example for adding a column to a table in MySQL mode:

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

obclient> ALTER TABLE test ADD c3 int;
Query OK, 0 rows affected (0.03 sec)

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 (0.00 sec)

Example for adding a column to a table in Oracle mode:

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

obclient> ALTER TABLE test ADD c3 int;
Query OK, 0 rows affected (0.03 sec)

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
| C3    | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

The column attributes that you can modify include column name and data type.

Example for modifying a column in MySQL mode:

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

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

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

Example for modifying a column in Oracle mode:

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

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

You cannot delete a column that is a primary key or has an index on it.

Example for deleting a column from a table in MySQL mode:

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

obclient> ALTER TABLE test DROP c;
Query OK, 0 rows affected (0.04 sec)

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

Example for deleting a column from a table in Oracle mode:

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

obclient> ALTER TABLE test DROP COLUMN c2;
Query OK, 0 rows affected (0.04 sec)

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
1 rows in set (0.00 sec)

Modify an index

MySQL mode

In MySQL mode, OceanBase Database allows you to create unique and normal indexes on a table and modify index attributes.
- Create a unique index
  
  After you create a table, you can create an 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.
  
  Example for creating a unique column on a table in Oracle mode:
```
obclient> CREATE TABLE test (c1 int PRIMARY KEY, c2 VARCHAR(50));
Query OK, 0 rows affected (0.04 sec)

obclient> ALTER TABLE test ADD UNIQUE INDEX index_name(c2);
Query OK, 0 rows affected (0.53 sec)
```
- Create a normal index
  
  In MySQL mode, OceanBase Database allows you to create multiple indexes at a time. You can use either the INDEX or the KEY clause.
```
obclient> CREATE TABLE test (c1 int PRIMARY KEY, c2 VARCHAR(50));
Query OK, 0 rows affected (0.04 sec)

obclient> ALTER TABLE test ADD INDEX myidx(c1,c2);
Query OK, 0 rows affected (0.55 sec)
```
- Modify index attributes
  
  In MySQL mode, OceanBase Database can show or hide variables. Variables are shown by default. You can hide them as needed.
  
  Syntax:
```
obclient> ALTER TABLE test ALTER INDEX myidx INVISIBLE;
```

Oracle mode

In Oracle mode, OceanBase Database allows you to create a unique index for a non-partitioned table.

Example:

Create a unique index

obclient> CREATE TABLE test (c1 int PRIMARY KEY, c2 VARCHAR(50));
Query OK, 0 rows affected (0.04 sec)

obclient> ALTER TABLE test ADD UNIQUE(c2);

Rename a table

OceanBase allows you to rename an existing table in both MySQL and Oracle modes.

Example:

obclient> ALTER TABLE test RENAME TO t1;

Delete a table group

OceanBase Database allows you to delete a table group in MySQL mode.

Example:

obclient> ALTER TABLE test DROP TABLEGROUP grp1;

For more information about table groups, see manage table groups.

Delete an external key

You can delete an external key in both MySQL and Oracle modes in OceanBase Database.

Example statement in MySQL mode:

obclient> ALTER TABLE test DROP FOREIGN KEY fk_name;

Example statement in Oracle mode:

obclient> ALTER TABLE test DROP CONSTRAINT fk_name;