Column operations

This topic describes the column operations in the Oracle mode of OceanBase Database, including adding a column to the end of a table, dropping a column, renaming a column, changing the data type of a column, managing the default value of a column, managing constraints, and changing 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;

where

• table_name specifies the name of the table where the column is to be added.

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

• column_definition specifies the data type and constraints for the column to be added.

For more information, see ALTER TABLE.

Assuming there is a table named tbl1 in the database with the following 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  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

Here is an example of how to add a C4 column at the end of the tbl1 table:

obclient> ALTER TABLE tbl1 ADD C4 INT;

Execute DESCRIBE tbl1; again to view the table structure of tbl1. The result is as follows, showing that the C4 column has been added to table tbl1:

+-------+--------------+------+------+---------+-------+
| 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 syntax for dropping a column is as follows:

ALTER TABLE table_name DROP COLUMN column_name

Here, table_name specifies the name of the table to which the column belongs, and column_name specifies the name of the column to be dropped.

Assuming there is a table named tbl1 in the database with the following 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  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

The following example shows how to drop the C3 column from the tbl1 table:

obclient> ALTER TABLE tbl1 DROP COLUMN C3;

Execute DESCRIBE tbl1; again to view the table structure of tbl1. The result is as follows, showing that the tbl1 table no longer has the C3 column:

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

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;

where

• table_name specifies the name of the table where the column to be renamed is located.

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

• new_col_name specifies the new name for the column after renaming.

Assuming there is a table named tbl1 in the database with the following 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  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

The following example shows how to rename the C3 column to C4 in the tbl1 table:

obclient> ALTER TABLE tbl1 RENAME COLUMN C3 TO C4;

Execute DESCRIBE tbl1; again to view the table structure of tbl1. The result is as follows, showing that the C3 column in the tbl1 table is renamed as 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 data type of a column

OceanBase Database supports the following data type conversions:

• Data type conversion for character data types CHAR and VARCHAR2.

• Precision change for the numeric data type NUMBER. Note that you can only increase the precision.

• Precision change for character data types CHAR (only precision increase supported), VARCHAR2, NVARCHAR2, and NCHAR.

For more information about the rules for changing the data types of columns in Oracle mode of OceanBase Database, see Column type change rules.

The syntax for changing the data type of a column is as follows:

ALTER TABLE table_name MODIFY column_name data_type;

where

• table_name specifies the name of the table where the column with the type to be modified is located.

• column_name specifies the name of the column whose type is to be modified.

• data_type specifies the new data type after modification.

Examples of column data type changes

Conversions 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 change the data type and length limit of a column of a character data type.

• Change the length limit 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 set the length limit to 20 characters.

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

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

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

• Change the length limit 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 set the length limit to 256 characters.

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

Precision change for a numeric data type

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 change the precision for a column of a numeric data type that has a precision.

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

Manage the default value of a column

If a column is not configured with a default value, the default value is NULL by default. 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;

where

• table_name specifies the name of the table where the column with the default value to be modified is located.

• 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 specify a different data type to modify the column to. The supported data types for modification can be found in the previous sectionModify column type.

• const_value specifies the new default value for the modified column.

Assuming there is a table named tbl1 in the database with the following 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  | NULL | 333     | NULL  |
+-------+--------------+------+------+---------+-------+

• The following example changes the default value of the C1 column to 111:

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

• The following example drops the default value of the C3 column:

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

Execute DESCRIBE tbl1; again to view the table structure of tbl1. The result is as follows, showing 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 the Oracle mode of OceanBase Database, you can add column constraints to tables. For example, you can change an existing column to an auto-increment column, set or cancel a NOT NULL constraint for a column, and set or cancel a UNIQUE constraint for a column.

The syntax for managing constraints is as follows:

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

where

• table_name specifies the name of the table where the column with the constraint to be added is located.

• column_name specifies the name of the column to which the constraint will be added.

• data_type specifies the data type of the column to be modified. You can specify the current data type or specify a different data type to modify the column to. The supported data types for modification can be found in the previous section Modify column type.

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

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

• UNIQUE specifies setting the uniqueness constraint for the selected column.

Assuming there is a table named tbl1 in the database with the following structure:

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

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

2. Set the NOT NULL constraint for the C2 column.

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

3. Set the UNIQUE constraint for the C3 column.

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

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

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

Change the value of an auto-increment column

Before you can change the value of an auto-increment column, you must use CREATE SEQUENCE to create an auto-increment field, and then call the nextval function to retrieve the next value from the sequence.

The following example shows how to manage auto-increment column values:

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. View the data in the tbl1 table.

   obclient> SELECT * FROM tbl1;
   

   The output below shows that the values in the C1 column increment from 1.

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

4. Manually trigger the 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. Query the data in the tbl1 table.

   obclient> SELECT * FROM tbl1;
   

   The output below shows that there is no row with the value 4 in the C1 column. Instead, the value 5 is inserted directly.

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

References

ALTER TABLE