In OceanBase Database, you can add a column at the end of a table, add a column not at the end of a table, drop a column, rename a column, rearrange columns, change the data type of a column, set the default value of a column, drop the default value of a column, change the value of an auto-increment column, set a NULL constraint for a column, and set a NOT NULL constraint for a column.
Add a column to the end of a table
Syntax:
ALTER TABLE table_name ADD COLUMN column_name column_definition;
Sample code:
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 | NULL | 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 | NULL | NULL |
| C2 | VARCHAR2(50) | YES | NULL | NULL | NULL |
| C3 | NUMBER(38) | YES | NULL | NULL | NULL |
+-------+--------------+------+-----+---------+-------+
3 rows in set
Drop a column
Syntax:
ALTER TABLE table_name DROP COLUMN column_name
Sample code:
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 | NULL | NULL |
| C2 | VARCHAR2(50) | YES | NULL | NULL | NULL |
+-------+--------------+------+-----+---------+-------+
2 rows in set
Rename a column
Syntax:
ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;
Sample code:
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 | NULL | 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 Oracle mode of OceanBase Database, see Column type change rules. Syntax:
ALTER TABLE table_name MODEFY column_name data_type;
Examples
Conversion between character data types
- Change the data type of a character type column and increase the length of the column.
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 affected
- Decrease 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
Syntax:
ALTER TABLE table_name MODIFY column_name datatype DEFAULT literal;
Sample code:
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
Syntax:
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.
Sample code:
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.
Sample code:
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
Syntax:
ALTER TABLE table_name MODIFY (column_name data_type NOT NULL);
``Sample code:
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 | NULL | 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
Syntax:
ALTER TABLE table_name MODIFY (column_name data_type NULL);
``Sample code:
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 | NULL | NULL |
| C2 | NUMBER(38) | YES | NULL | NULL | NULL |
| C3 | VARCHAR2(50) | YES | NULL | NULL | NULL |
+-------+--------------+------+-----+---------+-------+
3 rows in set