Column operations

In Oracle mode of OceanBase Database, you can add a column to the end of a table, drop a column, rename a column, change the data type of a column, manage the default value of a column, manage constraints, and set the value of an auto-increment column.

Add a column to the end of a table

The syntax for adding a column to the end of a table is as follows:

ALTER TABLE table_name ADD column_name column_definition;

The following table describes the parameters:

• table_name: the name of the table to which you want to add a column.

• column_name: the name of the column to be added.

• column_definition: the data type and constraints of the column to be added.

For more information, see ALTER TABLE.

Assume that a table named tbl1 exists in the database. The table structure is as follows:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
| C3    | NUMBER(3)    | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

This example adds the C4 column to the end of the tbl1 table.

obclient> ALTER TABLE tbl1 ADD C4 INT;

Execute the DESCRIBE tbl1; command again to view the table structure. The output shows that the C4 column has been added to the tbl1 table.

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
| C3    | NUMBER(3)    | YES  | NULL | NULL    | NULL  |
| C4    | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

Drop a column

The DROP COLUMN statement is an online DDL operation. It marks the column for deletion without triggering data reorganization. This means the disk space occupied by the column is not reclaimed, and the column still exists in the schema, but it is marked as obsolete. To completely remove the obsolete column and its data and information from the schema, execute the ALTER TABLE TABLE_NAME FORCE; statement.

Considerations

When dropping a column, consider the following:

• To maintain internal referential integrity and avoid potential name conflicts, OceanBase Database renames the obsolete column to SYS_C[COLUMN_ID]_TIME$. Therefore, the name of a new column cannot be the same as the system-generated name of an obsolete column. Avoid using names in the SYS_C[COLUMN_ID]_TIME$ format to ensure uniqueness and prevent potential conflicts. For more information about column naming, see ALL_TAB_COLS.

• OceanBase Database limits the maximum number of obsolete columns allowed in a table. When the number of obsolete columns exceeds 128, you cannot add or drop columns. To resolve this, execute the ALTER TABLE TABLE_NAME FORCE; statement to remove the obsolete columns.

• When a DROP COLUMN statement is combined with other DDL operations, it becomes an offline DDL operation. The database will perform a physical deletion, ensuring the column is completely removed from the table structure and storage.

• In OceanBase Database, the maximum length of a single data row is 1.5 MB. If a table reaches this length limit and some columns at the end of the table are marked as obsolete, even if they are no longer used, they still occupy physical storage space. If you plan to add new columns at the end of the table, you must first execute the ALTER TABLE TABLE_NAME FORCE; statement to remove the obsolete columns and reclaim the space before adding new columns.

• The following offline DDL operations, when executed, will trigger data reorganization and remove obsolete columns and their data and information from the schema, similar to executing the ALTER TABLE TABLE_NAME FORCE; statement:

  • Modify the primary key
  • Perform mixed column operations
  • Modify partitioning rules
  • Add an auto-increment column

• When a table contains obsolete columns, you cannot perform partition exchange operations, partition-level INSERT OVERWRITE operations, or full direct load operations at the partition level.

• When a table contains obsolete columns (marked for deletion), the TRUNCATE statement will not remove the marked columns.

For more information about online and offline DDL operations, see Online DDL and offline DDL operations.

Drop a single column

The syntax for dropping a single column is as follows:

ALTER TABLE table_name DROP COLUMN column_name;

In this syntax, table_name specifies the name of the table from which to drop the column, and column_name specifies the name of the column to drop.

Assume that the database contains a table named tbl1 with the following schema:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
| C3    | NUMBER(3)    | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

Here is an example of dropping the C3 column from the tbl1 table.

obclient> ALTER TABLE tbl1 DROP COLUMN C3;

Execute the DESCRIBE tbl1; statement again to view the schema of the tbl1 table. The output shows that the C3 column no longer exists in the table.

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

Drop multiple columns

The syntax for dropping multiple columns is as follows:

• Method 1:

  ALTER TABLE table_name DROP (column_name1, column_name2, ...);
  

• Method 2:

  ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2, ...;
  

In both methods, table_name specifies the name of the table from which to drop the columns, and column_name specifies the name of each column to drop.

Assume that the database contains a table named tbl1 with the following schema:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(30)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
| C3    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
| C4    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
| C5    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

Here is an example of dropping the C4 and C5 columns from the tbl1 table using the ALTER TABLE table_name DROP (column_name1, column_name2, ...) statement.

obclient> ALTER TABLE tbl1 DROP COLUMN (C4, C5);

Execute the DESCRIBE tbl1; statement again to view the schema of the tbl1 table. The output shows that the C4 and C5 columns no longer exist in the table.

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(30)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
| C3    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

Here is an example of dropping the C2 and C3 columns from the tbl1 table using the ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2, ... statement.

obclient> ALTER TABLE tbl1 DROP COLUMN C2, DROP COLUMN C3;

Execute the DESCRIBE tbl1; statement again to view the schema of the tbl1 table. The output shows that the C2 and C3 columns no longer exist in the table.

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(30)   | NO   | PRI  | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

Clear obsolete columns

The syntax for clearing obsolete columns is as follows:

ALTER TABLE TABLE_NAME FORCE;

Here is an example of clearing the C2, C3, C4, and C5 columns from the tbl1 table.

obclient> ALTER TABLE tbl1 FORCE;

Rename a column

The syntax for renaming a column is as follows:

ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;

The parameters are described as follows:

• table_name: specifies the name of the table containing the column to be renamed.

• old_col_name: specifies the name of the column to be renamed.

• new_col_name: specifies the new name of the column.

Assume that the database contains a table named tbl1 with the following schema:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
| C3    | NUMBER(3)    | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

Here is an example of renaming the C3 column to C4 in the tbl1 table.

obclient> ALTER TABLE tbl1 RENAME COLUMN C3 TO C4;

Execute the DESCRIBE tbl1; statement again to view the schema of the tbl1 table. The output shows that the C3 column has been renamed to C4.

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
| C4    | NUMBER(3)    | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

Change the type of a column

The following table describes the column type conversion supported by OceanBase Database:

• Conversion between CHAR and VARCHAR2 columns.

• Change of precision for NUMBER columns. Precision cannot be reduced.

• Change of precision for CHAR, VARCHAR2, NVARCHAR2, and NCHAR columns. Precision cannot be reduced.

For more information about column type conversion in Oracle mode, see Column type conversion rules.

Syntax:

ALTER TABLE table_name MODIFY column_name data_type;

Parameters:

• table_name: the name of the table that contains the column to be modified.

• column_name: the name of the column to be modified.

• data_type: the new data type.

Examples

Convert a character data type column

The following example creates a table named test01:

obclient> CREATE TABLE test01 (C1 INT PRIMARY KEY, C2 CHAR(10), C3 VARCHAR2(32));

The following examples show how to modify the data type and length of a character data type column in the test01 table.

• Change the length of the C2 column in the test01 table to 20 characters.

  obclient> ALTER TABLE test01 MODIFY C2 CHAR(20);
  

• Change the data type of the C2 column in the test01 table to VARCHAR and specify the maximum length as 20 characters.

  obclient> ALTER TABLE test01 MODIFY C2 VARCHAR(20);
  

• Change the maximum length of the C3 column in the test01 table to 64 characters.

  obclient> ALTER TABLE test01 MODIFY C3 VARCHAR(64);
  

• Change the maximum length of the C3 column in the test01 table to 16 characters.

  obclient> ALTER TABLE test01 MODIFY C3 VARCHAR(16);
  

• Change the data type of the C3 column in the test01 table to CHAR and specify the length as 256 characters.

  obclient> ALTER TABLE test01 MODIFY C3 CHAR(256);
  

Change the precision of a numeric data type column

The following example creates a table named test02:

obclient> CREATE TABLE test02(C1 NUMBER(10,2));

The following example shows how to modify the precision of a numeric data type column in the test02 table.

obclient> ALTER TABLE test02 MODIFY C1 NUMBER(11,3);

Manage the default value of a column

If no default value is specified, the default value of a column is NULL. Syntax:

ALTER TABLE table_name MODIFY column_name data_type DEFAULT const_value;

Parameters:

• table_name: the name of the table that contains the column to be modified.

• column_name: the name of the column to be modified.

• data_type: the data type of the column to be modified. You can specify the current data type or change the data type of the column to another type. For more information about the supported data types, see Change the type of a column.

• const_value: the new default value of the column.

Assume that a table named tbl1 exists in the database. The table structure is as follows:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
| C3    | NUMBER(3)    | YES  | NULL | 333     | NULL  |
+-------+--------------+------+------+---------+-------+

• You can execute the following command to set the default value of the C1 column to 111.

  obclient> ALTER TABLE tbl1 MODIFY C1 NUMBER(10) DEFAULT 111;
  

• You can execute the following command to remove the default value of the C3 column.

  obclient> ALTER TABLE tbl1 MODIFY C3 NUMBER(3) DEFAULT NULL;
  

Execute the DESCRIBE tbl1; command again to view the table structure. The output is as follows: the default value of the C1 column is 111, and the default value of the C3 column is NULL.

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(10)   | NO   | PRI  | 111     | NULL  |
| C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
| C3    | NUMBER(3)    | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

Manage constraints

In Oracle mode, you can add constraints to a table, such as modifying an existing column to an auto-increment column, specifying whether a column can be NULL, and specifying the uniqueness of a column. This section describes how to perform these operations.

Syntax:

ALTER TABLE table_name
    MODIFY column_name data_type
    [NULL | NOT NULL]
    [PRIMARY KEY]
    [UNIQUE];

Parameters:

• table_name: the name of the table that contains the column to be modified.

• column_name: the name of the column to be modified.

• data_type: the data type of the column to be modified. You can specify the current data type or change the data type of the column to another type. For more information about the supported data types, see Change the type of a column.

• NULL | NOT NULL: specifies whether the selected column can be NULL (NULL) or cannot be NULL (NOT NULL).

• PRIMARY KEY: specifies the selected column as the primary key.

• UNIQUE: specifies the uniqueness of the selected column.

Assume that a table named tbl1 exists in the database. The table structure is as follows:

+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(10)   | YES  | NULL | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
| C3    | NUMBER(3)    | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+

1. Set the C1 column as the primary key.

   obclient> ALTER TABLE tbl1 MODIFY C1 NUMBER(10) PRIMARY KEY;
   

2. Set the C2 column to NOT NULL.

   obclient> ALTER TABLE tbl1 MODIFY C2 VARCHAR(50) NOT NULL;
   

3. Set the C3 column to be unique.

   obclient> ALTER TABLE tbl1 MODIFY C3 NUMBER(3) UNIQUE;
   

4. Execute the DESCRIBE tbl1; command again to view the table structure.

   +-------+--------------+------+-----+---------+-------+
   | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
   +-------+--------------+------+-----+---------+-------+
   | C1    | NUMBER(10)   | NO   | PRI | NULL    | NULL  |
   | C2    | VARCHAR2(50) | NO   | NULL | NULL    | NULL  |
   | C3    | NUMBER(3)    | YES  | UNI | NULL    | NULL  |
   +-------+--------------+------+-----+---------+-------+
   

Set the value of an auto-increment column

To set the value of an auto-increment column, you must first use the CREATE SEQUENCE statement to create an auto-increment field, and then use the nextval function to retrieve the next value from the sequence.

Here is an example of managing the values of an auto-increment column:

1. Create an auto-increment sequence named seq1 with a starting value of 1, an increment of 1, and a cache size of 10.

   obclient> CREATE SEQUENCE seq1 MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;
   

2. Insert data into the tbl1 table.

   obclient> INSERT INTO tbl1(C1, C2, C3) VALUES (seq1.nextval, 'zhangsan', 20), (seq1.nextval, 'lisi', 21), (seq1.nextval, 'wangwu', 22);
   

3. Execute the following command to view the data in the tbl1 table.

   obclient> SELECT * FROM tbl1;
   

   The output is as follows, where the values in the C1 column start from 1 and increment by 1.

   +------+----------+------+
   | C1   | C2       | C3   |
   +------+----------+------+
   |    1 | zhangsan |   20 |
   |    2 | lisi     |   21 |
   |    3 | wangwu   |   22 |
   +------+----------+------+
   

4. Manually trigger the auto-increment of the seq1 sequence to modify the value of the auto-increment column.

   obclient> SELECT seq1.nextval FROM sys.dual;
   

   The output is as follows:

   +---------+
   | NEXTVAL |
   +---------+
   |      4 |
   +---------+
   

5. Insert data into the tbl1 table again.

   obclient> INSERT INTO tbl1(C1, C2, C3) VALUES (seq1.nextval, 'oceanbase', 12);
   

6. Execute the following command to view the data in the tbl1 table again.

   obclient> SELECT * FROM tbl1;
   

   The output is as follows, where the values in the C1 column do not include 4, and the next value is 5.

   +------+-----------+------+
   | C1   | C2        | C3   |
   +------+-----------+------+
   |    1 | zhangsan  |   20 |
   |    2 | lisi      |   21 |
   |    3 | wangwu    |   22 |
   |    5 | oceanbase |   12 |
   +------+-----------+------+
   

References

ALTER TABLE