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 drop 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.

  The following example adds 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.

  The syntax is as follows:

  ALTER TABLE table_name MODIFY (column_name data_type);
  

  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 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 rename 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 except for the primary key column and indexed columns from a table. 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

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

You can rename an existing 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 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

Modify the skip index attribute of a column

OceanBase Database allows you to use the ALTER TABLE statement to add, modify, and delete the skip index attribute.

For more information about the skip index attribute, see Column skip index attribute.

Here is an example:

1. Use the following SQL statement to create a table named test_skidx.

   CREATE TABLE test_skidx(
     col1 NUMBER SKIP_INDEX(MIN_MAX, SUM),
     col2 FLOAT SKIP_INDEX(MIN_MAX),
     col3 VARCHAR2(1024) SKIP_INDEX(MIN_MAX),
     col4 CHAR(10)
     );
   

2. Change the type of the skip index on the col2 column in the test_skidx table to SUM.

   ALTER TABLE test_skidx MODIFY col2 FLOAT SKIP_INDEX(SUM);
   

3. Add the skip index attribute for a column after the table is created. That is, add a skip index of the MIN_MAX type for the col4 column in the test_skidx table.

   ALTER TABLE test_skidx MODIFY col4 CHAR(10) SKIP_INDEX(MIN_MAX);
   

4. Delete the skip index attribute for a column after the table is created. That is, delete the skip index attribute of the col1 column in the test_skidx table.

   ALTER TABLE test_skidx MODIFY col1 NUMBER SKIP_INDEX();
   

Convert a rowstore table to a columnstore table

When you create a table in OceanBase Database, the table is a rowstore table by default. You can use the WITH COLUMN GROUP option to explicitly specify to create a columnstore table or a rowstore-columnstore redundant table. You can convert a rowstore table to a columnstore table by using the ALTER TABLE statement.

Convert a rowstore table into a columnstore table

Here is an example:

1. Create a rowstore table named tbl1.

   obclient> CREATE TABLE tbl1(col1 NUMBER, col2 VARCHAR2(30));
   

2. Convert the rowstore table tbl1 into a columnstore table.

   obclient> ALTER TABLE tbl1 ADD COLUMN GROUP(each column);
   

Convert a rowstore table into a rowstore-columnstore redundant table

Here is an example:

1. Create a rowstore table named tbl2.

   obclient> CREATE TABLE tbl2(col1 NUMBER, col2 VARCHAR2(30));
   

2. Convert the rowstore table tbl2 into a rowstore-columnstore redundant table.

   obclient> ALTER TABLE tbl2 ADD COLUMN GROUP(all columns, each column);
   

Convert a rowstore-columnstore redundant table into a columnstore table

Here is an example:

1. Create a rowstore-columnstore redundant table named tbl3.

   obclient> CREATE TABLE tbl3(col1 NUMBER, col2 VARCHAR2(30)) WITH COLUMN GROUP(all columns, each column);
   

2. Convert the rowstore-columnstore redundant table tbl3 into a columnstore table.

   obclient> ALTER TABLE tbl3 DROP COLUMN GROUP(all columns);
   

Convert a rowstore-columnstore redundant table into a rowstore table

Here is an example:

1. Create a rowstore-columnstore redundant table named tbl4.

   obclient> CREATE TABLE tbl4(col1 NUMBER, col2 VARCHAR2(30)) WITH COLUMN GROUP(all columns, each column);
   

2. Convert the rowstore-columnstore redundant table tbl4 into a rowstore table.

   obclient> ALTER TABLE tbl4 DROP COLUMN GROUP(each column);
   

   or

   obclient> ALTER TABLE tbl4 DROP COLUMN GROUP(all columns, each column);
   

References

For more information about the ALTER TABLE statement, see ALTER TABLE.