Modify a table

After successfully creating a table, you can use the ALTER TABLE statement to modify the table.

Modify table schema

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

• You can add columns to a table, except for a primary key column. Here is an example:

  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 (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
  

• You can add a column after a specified column. Here is an example of adding column c4 after column c1:

  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
  

• You can add a column before a specified column. Here is an example of adding column c6 before column c1:

  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
  

  In these two examples, the following fields are used:

  • Field indicates the column name.

  • Type indicates the data type.

  • Null indicates whether the column allows NULL values. NO indicates that it does not allow NULL values, and YES indicates that it allows NULL values.

  • key: indicates whether the column is a primary key column. PRI indicates that it is a primary key column.

• You can change the name and data type of a column. Here is an example:

  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
  

  Alternatively, you can modify column definitions using the following method. Here is an example:

  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
  

  Note

  If you use the MODIFY keyword to modify column definitions, you can increase the column length only for specific data types, such as VARCHAR, VARBINARY, and CHAR.

• You can change the default value of a column. Here is an example of changing 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
  

  Alternatively, you can change the default value of a column using the following method. Here is an example:

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

• You can drop columns from a table, except for a primary key column or an indexed column. Here is an example:

  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
  

Modify indexes

OceanBase Database allows you to add unique and normal indexes to a table and to modify index attributes.

• Add a unique index.

  After successfully creating a table, you can add a unique index to 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:

  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
  

• Add a normal index.

  OceanBase Database allows you to add multiple normal indexes at a time. You can use the INDEX or KEY keyword. Here is an example:

  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
  

  In this example, myidx(c1,c2) adds an index to both columns c1 and c2. myidx is the index name.

• Drop an index.

  OceanBase Database allows you to drop multiple indexes at a time. When dropping multiple indexes, separate them 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;
  Query OK, 0 rows affected
  

Modify the foreign keys of a table

• Drop a foreign key.

  You can drop a foreign key from a table. Here is an example:

  obclient> ALTER TABLE test DROP FOREIGN KEY fk_name;
  

• Add a foreign key.

  You can add a foreign key to a table. Here is an example:

  obclient> ALTER TABLE test  ADD CONSTRAINT fk_name FOREIGN KEY (gid) REFERENCES test1(id);
  Query OK, 0 rows affected
  

  In this example, the following fields are used:

  • gid indicates the name of the foreign key.

  • test1 indicates the name of the foreign table.

  • id indicates the name of the primary key column.

Change the number of replicas for a table

OceanBase Database allows you to change the number of replicas for a table. Here is an example:

obclient> ALTER TABLE test SET REPLICA_NUM=2;
Query OK, 0 rows affected

Rename a table

OceanBase Database allows you to rename a table. Here is an example:

obclient> ALTER TABLE test RENAME TO t1;

or

obclient> RENAME TABLE test TO t1;

Drop a table group

OceanBase Database allows you to drop the table group to which a table belongs. Here is an example:

obclient> DROP TABLEGROUP grp1;

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