Modify a table

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

Considerations

You cannot perform other DDL operations when you modify the primary key or column type of a table. Vice versa, you cannot modify the primary key or column type of a table when you perform other DDL operations.

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.

• You can add columns except for a primary key column to a table. 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.

  Example: Add a normal column.

  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
  
  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 type of a column. OceanBase Database supports the conversion of column types.

  For more information about the rules for changing the data types of columns, see Column type change rules.

  Syntax:

  ALTER TABLE table_name MODIFY (column_name data_type);
  

  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 MODIFY (c2 VARCHAR(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. 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. 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. 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 delete columns except for the primary key column and indexed columns from a table. 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

After you create a table, you can create a unique index on the table. 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

You can rename an existing table.

OceanBase Database allows you to rename a table. Example:

obclient> ALTER TABLE test RENAME TO t1;

or

obclient> RENAME TABLE test TO t1;

Modify the PRIMARY KEY and FOREIGN KEY constraints for a table

After you create a table, you can modify the PRIMARY KEY and FOREIGN KEY constraints for the table. For more information, see Define column constraints.

Change the number of replicas of a table

After you create a table, you can change the number of replicas of the table.

Example: Change the number of replicas of a table to 2.

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