Column operations

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

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 of tbl1 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  |
+-------+--------------+------+------+---------+-------+

The following example adds the C4 column to the end of the tbl1 table.

obclient> ALTER TABLE tbl1 ADD C4 INT;

Execute the DESCRIBE tbl1; statement again to view the table structure of tbl1. The output is as follows, indicating that the C4 column has been added to the end of 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

DROP COLUMN is an Online DDL operation. It marks the column for deletion without triggering data reorganization. This means that the disk space occupied by the deleted 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 you drop a column, note the following:

• OceanBase Database changes the name of the obsolete column to SYS_C[COLUMN_ID]_TIME$ to maintain internal references and avoid potential name conflicts. Therefore, the name of a new column cannot be in the SYS_C[COLUMN_ID]_TIME$ format to ensure uniqueness and avoid potential conflicts. For more information about column naming, see ALL_TAB_COLS.

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

• When a DDL operation for dropping a column is combined with other operations, the database will perform a physical deletion to ensure that 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 the length of a table reaches this limit and some columns at the end of the table are marked as obsolete, they will still occupy physical storage space even if they are no longer used. If you plan to add new columns to 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 will trigger data reorganization and remove the obsolete columns and their data and information from the schema, similar to the ALTER TABLE TABLE_NAME FORCE; statement:

  • Adding or dropping a primary key
  • Mixed column operations
  • Modifying partitioning rules
  • Adding an auto-increment column
  • TRUNCATE table

For more information about Online DDL 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 statement, table_name specifies the name of the table from which you want to drop a column, and column_name specifies the name of the column to be dropped.

Assume that a table named tbl1 exists in the database. The table structure of tbl1 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  |
+-------+--------------+------+------+---------+-------+

The following example drops the C3 column from the tbl1 table.

obclient> ALTER TABLE tbl1 DROP COLUMN C3;

Execute the DESCRIBE tbl1; statement again to view the table structure of tbl1. The output is as follows, indicating that the C3 column has been dropped from the tbl1 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_nameE DROP COLUMN column_name1, DROP COLUMN column_name2, ...;

In both methods, table_name specifies the name of the table from which you want to drop columns, and column_name specifies the names of the columns to be dropped. Assume that a table named tbl1 exists in the database. The table structure of tbl1 is as follows:

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

The following example drops 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 table structure of tbl1. The output is as follows, indicating that the C4 and C5 columns have been dropped from the tbl1 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  |
+-------+--------------+------+------+---------+-------+

The following example drops the C2 and C3 columns from the tbl1 table using the ALTER TABLE table_nameE 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 table structure of tbl1. The output is as follows, indicating that the C2 and C3 columns have been dropped from the tbl1 table.

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

Drop obsolete columns

The syntax for dropping obsolete columns is as follows:

ALTER TABLE TABLE_NAME FORCE;

The following example drops 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 following table describes the parameters of the preceding statement.

• table_name: specifies the name of the table that contains 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 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  |
+-------+--------------+------+------+---------+-------+

The following example shows how to rename the C3 column to C4.

obclient> ALTER TABLE tbl1 RENAME COLUMN C3 TO C4;

Execute the DESCRIBE tbl1; statement again to view the table structure. The output is as follows, indicating 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 column type

The following table lists the column type conversion rules supported by OceanBase Database.

• Conversion between character data types, including CHAR and VARCHAR2.

• Change the precision of numeric data types, including NUMBER (precision cannot be reduced).

• Change the precision of character data types, including CHAR (precision cannot be reduced), VARCHAR2, NVARCHAR2, and NCHAR.

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

The syntax for changing the column type is as follows:

ALTER TABLE table_name MODIFY column_name data_type;

The following table describes the parameters of the preceding statement.

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

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

• data_type: specifies the new data type.

Examples

Convert between character data types

The following example shows how to create 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 character data type columns in the test01 table.

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

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

• Modify the data type of the C2 column in the test01 table to VARCHAR and set the maximum length to 20 characters.

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

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

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

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

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

• Modify the data type of the C3 column in the test01 table to CHAR and set the length to 256 characters.

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

Change the precision of numeric data types

The following example shows how to create a table named test02.

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

The following example shows how to modify the precision of numeric data type columns 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. The syntax for managing the default value of a column is as follows:

ALTER TABLE table_name MODIFY column_name data_type DEFAULT const_value;

The following table describes the parameters of the preceding statement.

• table_name: specifies the name of the table that contains the column whose default value is to be modified.

• column_name: specifies the name of the column whose default value is to be modified.

• data_type: specifies 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 information about the data types that can be modified, see Change the column type section above.

• const_value: specifies 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 statement to set the default value of a column. In this example, the default value of the C1 column is set to 111.

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

• You can execute the following statement to remove the default value of a column. In this example, the default value of the C3 column is removed.

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

Execute the DESCRIBE tbl1; statement again to view the table structure. The output is as follows, indicating that 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 of OceanBase Database, you can add constraints to a table. For example, you can modify an existing column to an auto-increment column, set whether a column can be null, or specify the uniqueness of a column. This section describes how to perform these operations.

The syntax for managing constraints is as follows:

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

The following table describes the parameters of the preceding statement.

• table_name: specifies the name of the table that contains the column to which a constraint is to be added.

• column_name: specifies the name of the column to which a constraint is to be added.

• data_type: specifies 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 information about the data types that can be modified, see Change the column type section above.

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

• PRIMARY KEY: specifies whether the selected column is the primary key.

• UNIQUE: specifies whether the selected column is unique.

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 allow null values.

   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; statement 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 an auto-increment column

To set an auto-increment column, you must 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 auto-increment column values:

1. Create an auto-increment sequence 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. The values in the C1 column start at 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 auto-increment column value.

   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. The C1 column does not contain a row with a value of 4. Instead, the value 5 is directly inserted.

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

References

ALTER TABLE