In OceanBase Database, you can add a column to the end of a table, drop a column, rename a column, reorder columns, change the data type of a column, set a default value for a column, drop the default value of a column, change the value of an auto-increment column, set a NULL constraint on a column, and set a NOT NULL constraint on a 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 column_name column_definition;
Here is an example:
obclient> CREATE TABLE tbl1 (c1 INT PRIMARY KEY,c2 VARCHAR(50));
Query OK, 0 rows affected
obclient> DESCRIBE tbl1;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1 | NUMBER(38) | NO | PRI | 1 | NULL |
| C2 | VARCHAR2(50) | YES | NULL| NULL | NULL |
+-------+--------------+------+-----+---------+-------+
2 rows in set
obclient> ALTER TABLE tbl1 ADD c3 INT;
Query OK, 0 rows affected
obclient> DESCRIBE tbl1;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1 | NUMBER(38) | NO | PRI | 1 | NULL |
| C2 | VARCHAR2(50) | YES | NULL| NULL | NULL |
| C3 | NUMBER(38) | YES | NULL| NULL | NULL |
+-------+--------------+------+-----+---------+-------+
3 rows in set
Drop a column
The syntax for dropping a column is as follows:
ALTER TABLE table_name DROP COLUMN column_name
Here is an example:
obclient> ALTER TABLE tbl1 DROP COLUMN c3;
Query OK, 0 rows affected
obclient> DESCRIBE tbl1;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1 | NUMBER(38) | NO | PRI | 1 | NULL |
| C2 | VARCHAR2(50) | YES | NULL| NULL | NULL |
+-------+--------------+------+-----+---------+-------+
2 rows in set
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;
Here is an example:
obclient> ALTER TABLE tbl1 RENAME COLUMN c2 TO c3;
Query OK, 0 rows affected
obclient> DESCRIBE tbl1;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1 | NUMBER(38) | NO | PRI | 1 | NULL |
| C3 | VARCHAR2(50) | YES | NULL| NULL | NULL |
+-------+--------------+------+-----+---------+-------+
2 rows in set
Change the type of a column
OceanBase Database supports the following data type conversions:
- Data type conversion for character data types
CHARandVARCHAR2. - 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, andNCHAR.
For more information about the rules for column type change in the Oracle mode of OceanBase Database, see Column type change rules. The syntax for changing the type of a column is as follows:
ALTER TABLE table_name MODEFY column_name data_type;
Examples of column type changes
Conversions between character data types
Change the data type of a character column and increase the length.
obclient> CREATE TABLE test01 (c1 INT PRIMARY KEY, c2 CHAR(10), c3 VARCHAR2(32)); Query OK, 0 rows affected obclient> ALTER TABLE test01 MODIFY c2 VARCHAR(20); Query OK, 0 rows affected obclient> ALTER TABLE test01 MODIFY c3 VARCHAR(64); Query OK, 0 rows affected obclient> ALTER TABLE test01 MODIFY c3 CHAR(256); Query OK, 0 rows affectedDecrease the length of a character column.
obclient> CREATE TABLE test02(c1 VARCHAR2(128)); Query OK, 0 rows affected obclient> ALTER TABLE test02 MODIFY c1 VARCHAR2(64); Query OK, 0 rows affected obclient> CREATE TABLE test03(c1 CHAR(10)); Query OK, 0 rows affected obclient> ALTER TABLE test03 MODIFY c1 CHAR(20); Query OK, 0 rows affected
Precision change for a numeric data type
Change the precision for a column of a numeric data type that has a precision.
obclient> CREATE TABLE test04(c1 NUMBER(10,2));
Query OK, 0 rows affected
obclient> ALTER TABLE test04 MODIFY c1 NUMBER(11,3);
Query OK, 0 rows affected
Set a default value for a column
The syntax for setting a default value for a column is as follows:
ALTER TABLE table_name MODIFY column_name datatype DEFAULT literal;
Here is an example:
obclient> ALTER TABLE tbl1 MODIFY c1 INT DEFAULT 111;
Query OK, 0 rows affected
obclient> DESCRIBE tbl1;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1 | NUMBER(38) | NO | PRI | 111 | NULL |
| C3 | VARCHAR2(50) | YES | NULL| NULL | NULL |
+-------+--------------+------+-----+---------+-------+
2 rows in set
Drop the default value of a column
The syntax for dropping the default value of a column is as follows:
ALTER TABLE table_name MODIFY column_name DEFAULT NULL;
Notice
For a column with a default value, you can use the DEFAULT clause to change the default value to NULL. You cannot completely drop the default value.
Here is an example:
obclient> ALTER TABLE tbl1 MODIFY c1 DEFAULT NULL;
Query OK, 0 rows affected
Change the value of an auto-increment column
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:
obclient> CREATE SEQUENCE seq1 MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;
Query OK, 0 rows affected
obclient> INSERT INTO t1 (c1) VALUES (seq1.nextval);
Query OK, 0 rows affected
obclient> SELECT seq1.nextval FROM sys.dual;
+---------+
| NEXTVAL |
+---------+
| 2 |
+---------+
1 row in set
Set a NOT NULL constraint on a column
The syntax for setting a NOT NULL constraint on a column is as follows:
ALTER TABLE table_name MODIFY (column_name data_type NOT NULL);
Here is an example:
obclient> CREATE TABLE tbl2 (c1 INT PRIMARY KEY,c2 INT,c3 VARCHAR(50));
Query OK, 0 rows affected
obclient> ALTER TABLE tbl2 MODIFY (c2 INT NOT NULL);
Query OK, 0 rows affected
obclient> DESCRIBE tbl2;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1 | NUMBER(38) | NO | PRI | 1 | NULL |
| C2 | NUMBER(38) | NO | NULL| NULL | NULL |
| C3 | VARCHAR2(50) | YES | NULL| NULL | NULL |
+-------+--------------+------+-----+---------+-------+
3 rows in set
Set a NULL constraint on a column
The syntax for setting a NULL constraint on a column is as follows:
ALTER TABLE table_name MODIFY (column_name data_type NULL);
Here is an example:
obclient> ALTER TABLE tbl2 MODIFY (c2 INT NULL);
Query OK, 0 rows affected
obclient> DESCRIBE tbl2;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1 | NUMBER(38) | NO | PRI | 1 | NULL |
| C2 | NUMBER(38) | YES | NULL| NULL | NULL |
| C3 | VARCHAR2(50) | YES | NULL| NULL | NULL |
+-------+--------------+------+-----+---------+-------+
3 rows in set