Modify a table

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

Add, modify, and drop a column

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    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
  | C2    | VARCHAR2(50) | YES  | NULL | NULL    | 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    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
  | C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL |
  | C3    | NUMBER(38)   | YES  | NULL | NULL    | NULL |
  +-------+--------------+------+-----+---------+-------+
  3 rows in set
  

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

  Note

  When you change the data type of a column, you can increase the column length only for specific character data types, such as VARCHAR, VARBINARY, and CHAR.

  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
  
  obclient> ALTER TABLE test MODIFY (c2 VARCHAR2(70));
  Query OK, 0 rows affected
  
  obclient> DESCRIBE test;
  +-------+--------------+------+-----+---------+-------+
  | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
  +-------+--------------+------+-----+---------+-------+
  | C1    | NUMBER(38)   | YES  | NULL | NULL    | NULL |
  | C2    | VARCHAR2(70) | YES  | NULL | NULL    | NULL |
  +-------+--------------+------+-----+---------+-------+
  2 rows in set
  

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

  obclient> ALTER TABLE test RENAME COLUMN c1 TO c;
  Query OK, 0 rows affected
  
  obclient> DESCRIBE test;
  +-------+--------------+------+-----+---------+-------+
  | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
  +-------+--------------+------+-----+---------+-------+
  | C     | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
  | C2    | VARCHAR2(70) | YES  | NULL | NULL    | NULL  |
  +-------+--------------+------+-----+---------+-------+
  2 rows in set
  

• You can change the default value of a column. Here is an example:

  obclient> ALTER TABLE test MODIFY c DEFAULT 1;
  Query OK, 0 rows affected
  
  obclient> DESCRIBE test;
  +-------+--------------+------+-----+---------+-------+
  | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
  +-------+--------------+------+-----+---------+-------+
  | C     | NUMBER(38)   | YES  | NULL | 1       | NULL  |
  | C2    | VARCHAR2(70) | YES  | NULL | NULL    | NULL  |
  +-------+--------------+------+-----+---------+-------+
  2 rows in set
  

• You can modify the NOT NULL constraint on a column. Here is an example:

  obclient> DESCRIBE test;
  +-------+--------------+------+-----+---------+-------+
  | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
  +-------+--------------+------+-----+---------+-------+
  | C     | NUMBER(38)   | YES  | NULL | 1       | NULL  |
  | C2    | VARCHAR2(70) | YES  | NULL | NULL    | NULL  |
  +-------+--------------+------+-----+---------+-------+
  2 rows in set
  
  obclient> ALTER TABLE test MODIFY (c2 NOT NULL);
  Query OK, 0 rows affected
  
  obclient> DESCRIBE test;
  +-------+--------------+------+-----+---------+-------+
  | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
  +-------+--------------+------+-----+---------+-------+
  | C     | NUMBER(38)   | YES  | NULL | 1       | NULL  |
  | C2    | VARCHAR2(70) | NO   | NULL | NULL    | NULL  |
  +-------+--------------+------+-----+---------+-------+
  2 rows in set
  

• 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    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
  | C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
  +-------+--------------+------+-----+---------+-------+
  2 rows in set
  
  obclient> ALTER TABLE test DROP COLUMN c2;
  Query OK, 0 rows affected
  
  obclient> DESCRIBE test;
  +-------+--------------+------+-----+---------+-------+
  | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
  +-------+--------------+------+-----+---------+-------+
  | C1    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
  +-------+--------------+------+-----+---------+-------+
  1 rows in set
  

Modify indexes

OceanBase Database allows you to add a unique index to a table. 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(c2);

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;

Modify the foreign keys of a table

• 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
  

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

  obclient> ALTER TABLE test DROP CONSTRAINT fk_name;
  

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

Change the table group of a table

For information about how to change the table group of a table, see Statements for table group management.