Column operations include appending a column, adding a non-terminating column, dropping a column, renaming a column, reordering columns, modifying column types, managing column defaults, managing constraints, and modifying auto-increment column values.
Add a column
Adding a column can be done as either an append-only operation or a non-append-only operation, which are described below.
Append-only operation
The syntax for appending a column to the end of a table is as follows:
ALTER TABLE table_name ADD COLUMN column_name data_type;
The related parameters are described as follows:
table_name: Specifies the name of the table to which a column is to be added.column_name: Specifies the name of the column to be added.data_type: Specifies the data type of the column to be added.
Assume that a table named tbl1 exists in the database. This example shows how to execute the statement to add a column c1 to the end of the tbl1 table.
View the schema of the
tbl1table.obclient> DESCRIBE tbl1;The output is as follows, showing that the table contains three columns:
id,name, andage.+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(50) | NO | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+Add a column
c1to the end of the table and specify its data type as INT.obclient> ALTER TABLE tbl1 ADD COLUMN c1 INT;View the schema of the
tbl1table again.obclient> DESCRIBE tbl1;The output is as follows, showing that the
c1column has been added to the end of the table.+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(50) | NO | | NULL | | | age | int(11) | YES | | NULL | | | c1 | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
Non-append-only operation
The syntax for adding a column to a position other than the end of a table is as follows:
ALTER TABLE table_name
ADD COLUMN new_column_name data_type
{FIRST | BEFORE | AFTER} column_name;
The related parameters are described as follows:
table_name: Specifies the name of the table to which a column is to be added.new_column_name: the name of the column to be added.data_type: the data type of the column to be added.FIRST | BEFORE | AFTER: Specifies the position where the column to be added is placed. It can be added at the beginning of the table (FIRST), beforecolumn_name(BEFORE), or aftercolumn_name(AFTER).Notice
When using
FIRSTto add a column to the beginning of a table, you do not need to setcolumn_name; otherwise, an error will occur.column_name: the name of the column corresponding to the position of the column to be added.
Assume a table named tbl1 exists in the database. The structure of the tbl1 table is as follows:
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(50) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
This example shows how to execute the statement for adding columns by adding a c1 column at the beginning of the tbl1 table, a c2 column before the name column, and a c3 column after the name column.
Add a
c1column to the header of table tbl1 and specify its data type as INT.obclient> ALTER TABLE tbl1 ADD COLUMN c1 INT FIRST;Add a
c2column before thenamecolumn in tabletbl1, and specify its data type as VARCHAR.obclient> ALTER TABLE tbl1 ADD COLUMN c2 VARCHAR(50) BEFORE name;Add a
c3column after thenamecolumn in tabletbl1. Specify the data type of this column as VARCHAR and add a NOT NULL constraint.obclient> ALTER TABLE tbl1 ADD COLUMN c3 VARCHAR(25) NOT NULL AFTER name;View the table structure information of tbl1 again
obclient> DESCRIBE tbl1;The output is as follows,
+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | c1 | int(11) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | c2 | varchar(50) | YES | | NULL | | | name | varchar(50) | NO | | NULL | | | c3 | varchar(25) | NO | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
Drop columns
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 containing the column to be dropped; column_name specifies the name of the column to be dropped.
Assume a table named tbl1 exists in the database. The structure of tbl1 is as follows:
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(50) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
This example shows you how to execute a statement for dropping a column by dropping the name column.
obclient> ALTER TABLE tbl1 DROP COLUMN name;
After the column is dropped, execute the DESCRIBE tbl1; command again to view the table structure of tbl1. The output is as follows. The name column no longer exists in table tbl1.
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
Delete multiple columns
Syntax:
ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2, ...;
Similar to dropping a single column, table_name specifies the name of the table containing the column to be dropped, and column_name specifies the name of the column to be dropped. Assume a table named tbl1 exists in the database. The structure of tbl1 is as follows:
+-------+--------------+------+------+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1 | NUMBER(30) | NO | PRI | NULL | NULL |
| C2 | VARCHAR(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 |
+-------+--------------+------+------+---------+-------+
This example shows you how to use the ALTER TABLE table_name DROP COLUMN column_name1, column_name2, ... statement to drop multiple columns from a table by dropping the C4 and C5 columns from the tbl1 table.
obclient> ALTER TABLE tbl1 DROP COLUMN C4, DROP COLUMN C5;
Execute the DESCRIBE tbl1; command again to view the table structure of tbl1. The output is as follows. Columns C4 and C5 no longer exist in table tbl1.
+-------+--------------+------+------+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1 | NUMBER(30) | NO | PRI | NULL | NULL |
| C2 | VARCHAR(50) | YES | NULL | NULL | NULL |
| C3 | NUMBER(30) | YES | NULL | NULL | NULL |
+-------+--------------+------+------+---------+-------+
Drop obsolete columns
The syntax for dropping obsolete columns is as follows:
ALTER TABLE TABLE_NAME FORCE;
This example shows you how to drop unused columns from the tbl1 table by dropping the C2, C3, C4, and C5 columns.
obclient> ALTER TABLE tbl1 FORCE;
Rename a column
You can rename a column by using either of the following syntaxes:
Rename a column and modify its data type at the same time.
ALTER TABLE table_name CHANGE old_col_name new_col_name data_type;The related parameters are described as follows:
table_name: The name of the table that contains the column to be renamed.old_col_name: the name of the column to be renamed.new_col_name: the name of the column after renaming.data_type: Specifies the data type of the column to be renamed. You can specify the current data type or another data type to which the column will be modified. For information about the supported data types, see [Modify column type](#Modify column type).
Rename columns only
ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_nameThe related parameters are described as follows:
table_name: The name of the table that contains the column to be renamed.old_col_name: the name of the column to be renamed.new_col_name: the name of the column after renaming.
Assume a table named tbl1 exists in the database. The structure of table tbl1 is as follows:
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(50) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
This topic uses the following examples to illustrate how to execute a statement for renaming columns: changing the column name name to c2, changing the column name age to c3, and changing the data type of the age column to VARCHAR.
Change the column name
nametoc2.obclient> ALTER TABLE tbl1 RENAME COLUMN name TO c2;Rename the
agecolumn toc3and modify its data type to VARCHAR.obclient> ALTER TABLE tbl1 CHANGE age c3 VARCHAR(50);Execute the
DESCRIBE tbl1;command again to view the table structure of tbl1.The output is as follows. Table tbl1 has only three columns:
id,c2, andc3, and the data type of columnc3is VARCHAR.+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | c2 | varchar(50) | YES | | NULL | | | c3 | varchar(50) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
Rearrange
The syntax for rearrangement is as follows:
ALTER TABLE table_name
MODIFY [COLUMN] column_name data_type
{FIRST | BEFORE | AFTER} column_name;
The related parameters are described as follows:
table_name: The name of the table to be rearranged.column_name: The name of the column to be rearranged.data_type: Specifies the data type of the column to be rearranged. You can specify the current data type or a different data type to which the column will be modified. For information about the supported data types, see [Modify column type](#Modify column type).FIRST | BEFORE | AFTER: Specifies the position where the column to be added is placed. It can be added at the beginning of the table (FIRST), beforecolumn_name(BEFORE), or aftercolumn_name(AFTER).Notice
When using
FIRSTto add a column to the beginning of a table, you do not need to setcolumn_name; otherwise, an error will occur.column_name: the name of the column related to the position to be rearranged.
Assume that a table named tbl1 exists in the database. The table structure of tbl1 is as follows:
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(50) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
This example shows you how to use the rearrange statement by reordering the age column before the name column and changing the data type of the age column to VARCHAR.
obclient> ALTER TABLE tbl1 MODIFY COLUMN age VARCHAR(50) BEFORE name;
Execute the DESCRIBE tbl1; command again to view the table structure of tbl1. The output is as follows: the age column is arranged before the name column, and its data type is VARCHAR.
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| age | varchar(50) | YES | | NULL | |
| name | varchar(50) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
Modify column type
The following table describes the conversions supported for column types in OceanBase AI Database.
Conversions of character data types, including
CHAR,VARCHAR,TINYTEXT,TEXT, andLONGTEXT.Conversions of numeric data types, including
TINYINT,SMALLINT,MEDIUMINT,INT, andBIGINT.Conversions of binary data types, including
BINARY,VARBINARY,BLOB,TINYBLOB,MEDIUMBLOB, andLONGBLOB.Changes in precision for precision data types, including
VARCHAR,FLOAT,DOUBLE, andDECIMAL.Conversions between precision data types, including
FLOAT,DOUBLE, andDECIMAL.Conversions between different data types, including
INT,VARCHAR,DOUBLE,FLOAT, andDECIMAL.
For more information about column type changes in OceanBase AI Database, see Column type change rules.
The syntax for modifying a column type is as follows:
ALTER TABLE table_name MODIFY [COLUMN] column_name data_type;
The parameters are described as follows:
table_name: The name of the table that contains the column whose type you want to modify.column_name: The name of the column whose type you want to modify.data_type: The data type after the modification.
Examples of conversions between character data types
Create the table test01 as shown below:
obclient> CREATE TABLE test01 (c1 INT PRIMARY KEY, c2 CHAR(10), c3 CHAR(10));
Using the test01 table as an example, the following examples demonstrate how to modify the data type and increase the length of a column with a character data type.
Change the data type of column
c2in the test01 table to VARCHAR, specifying a maximum length of 20 characters.obclient> ALTER TABLE test01 MODIFY c2 VARCHAR(20);Change the data type of column
c2in the test01 table to VARCHAR, specifying a maximum length of 40 characters.obclient> ALTER TABLE test01 MODIFY c2 VARCHAR(40);Change the data type of column
c2in the test01 table to TINYTEXT.obclient> ALTER TABLE test01 MODIFY c2 TINYTEXT;Change the data type of column
c2in table test01 to LONGTEXT.obclient> ALTER TABLE test01 MODIFY c2 LONGTEXT;Change the length of column
c3in table test01 to 20 charactersobclient> ALTER TABLE test01 MODIFY c3 CHAR(20);Change the data type of column
c3in tabletest01to VARCHAR and specify a maximum length of 30 characters.obclient> ALTER TABLE test01 MODIFY c3 VARCHAR(30);
Examples of conversions between numeric data types
Integer types
Create a table named test02 as shown below:
obclient> CREATE TABLE test02 (id INT PRIMARY KEY, name VARCHAR(10),age TINYINT, description VARCHAR(65525));
Taking the test02 table as an example, the following examples illustrate how to modify the data type of an integer column and increase its length.
Change the data type of the
agecolumn in the test02 table to SMALLINT.obclient> ALTER TABLE test02 MODIFY age SMALLINT;Change the data type of the
agecolumn in the test02 table to INT.obclient> ALTER TABLE test02 MODIFY age INT;Modify the data type of the
agecolumn in the test02 table to BIGGINT.obclient> ALTER TABLE test02 MODIFY age BIGGINT;
Precision data types
As shown below, create the table test03:
obclient> CREATE TABLE test03(c1 INT, c2 FLOAT(8,2), c3 FLOAT(8,2), UNIQUE(c2, c3));
The following examples show how to modify the data type and length of a column with a precision data type, using the test03 table as an example.
Modified the
c2column in the test03 table to allow 5 digits of numeric dataobclient> ALTER TABLE test03 MODIFY c2 FLOAT(5,2);Change the data type of column
c2in tabletest03to DOUBLE and set it to store a total of 10 digits, with 4 decimal places.obclient> ALTER TABLE test03 MODIFY c2 DOUBLE(10,4);Modify the data type of column
c2in tabletest03to DOUBLE and set the number of digits that can be stored to 5, with 2 decimal places.obclient> ALTER TABLE test03 MODIFY c2 DOUBLE(5,2);Modify the data type of column
c2in table test03 to DECIMAL and set it to store a total of 20 digits, with 4 decimal places.obclient> ALTER TABLE test03 MODIFY c2 DECIMAL(20, 4);
Examples of converting between binary data types
Create a table named test04 as follows:
obclient> CREATE TABLE test04 (c1 TINYBLOB, c2 BINARY(64));
The following examples use the test04 table to demonstrate how to modify the data type and length of a binary data type column.
Change the data type of column
c1in tabletest04to BLOB.obclient> ALTER TABLE test04 MODIFY c1 BLOB;Change the data type of column
c1in tabletest04to BINARY, allowing it to store up to 256 bytes of data.obclient> ALTER TABLE test04 MODIFY c1 BINARY(256);Change the data type of column
c1in tabletest04to VARCHAR and specify a maximum length of 256 characters.obclient> ALTER TABLE test04 MODIFY c1 VARCHAR(256);
Examples of converting integer and character data
Create the table test05 as follows:
obclient> CREATE TABLE test05 (c1 INT);
Execute the following statement to modify the data type of column
c1in tabletest05to VARCHAR with a maximum length of 64 characters.obclient> ALTER TABLE test05 MODIFY c1 VARCHAR(64);Execute the following command to change the data type of column
c1in tabletest05to INT again:obclient> ALTER TABLE test05 MODIFY c1 INT;
Manage column defaults
You can modify or delete the default value of a column in OceanBase AI Database. This section describes how to perform these operations.
Modify the default value of a column
If not set, the default value of a column is NULL. The syntax for modifying a column's default value is as follows:
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT const_value;
The related parameters are described as follows:
table_name: the name of the table that contains the column whose default value you want to modify.column_name: The name of the column whose default value is to be modified.const_value: The default value of the modified column.
Assume a table named tbl1 exists in the database. The table structure of tbl1 is as follows:
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(50) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
This example shows you how to modify the default value of a column by setting it to 18 for the age column.
obclient> ALTER TABLE tbl1 ALTER COLUMN age SET DEFAULT 18;
Execute the DESCRIBE tbl1; command again to view the table structure of tbl1. The output is as follows, with a default value of 18 for the age column.
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(50) | NO | | NULL | |
| age | int(11) | YES | | 18 | |
+-------+-------------+------+-----+---------+-------+
Delete the default value of a column
The syntax for dropping a column's default value is as follows:
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;
Here, table_name specifies the name of the table containing the column whose default value is to be modified; column_name specifies the name of the column whose default value is to be modified.
Assume a table named tbl1 exists in the database. The table structure of tbl1 is as follows:
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(50) | NO | | NULL | |
| age | int(11) | YES | | 18 | |
+-------+-------------+------+-----+---------+-------+
This example shows you how to drop a default value of a column by dropping the age column.
obclient> ALTER TABLE tbl1 ALTER COLUMN age DROP DEFAULT;
Execute the DESCRIBE tbl1; command again to view the table structure of tbl1. The output is as follows. The default value of the age column is NULL.
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(50) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
Manage constraints
OceanBase AI Database allows you to add column constraints to tables, such as changing an existing column to an auto-increment column, setting whether a column can be null, and specifying column uniqueness. This section describes how to perform these operations.
The syntax for managing constraints is as follows:
ALTER TABLE table_name
MODIFY [COLUMN] column_name data_type
[AUTO_INCREMENT]
[NULL | NOT NULL]
[[PRIMARY] KEY]
[UNIQUE [KEY]];
The related parameters are described as follows:
table_name: the name of the table that contains the column to which you want to add a constraint.column_name: The name of the column to which the constraint is added.data_type: Specifies the data type of the column to be modified. You can specify the current data type or another data type to which the column is to be modified. For information about the supported data types, see [Modify column type](#Modify column type).AUTO_INCREMENT: specifies that the selected column is an auto-increment column.NULL | NOT NULL: specifies whether the selected column can be null (NULL) or cannot be null (NOT NULL).[PRIMARY] KEY: specifies to set the selected column as the primary key.UNIQUE [KEY]: specifies to set uniqueness for the selected column.
Assume that a table named tbl1 exists in the database. The structure of table tbl1 is as follows:
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
Set the
idcolumn as the primary key.obclient> ALTER TABLE tbl1 MODIFY COLUMN id INT PRIMARY KEY;Set the
idcolumn as an auto-increment column.obclient> ALTER TABLE tbl1 MODIFY COLUMN id INT AUTO_INCREMENT;Set the
namecolumn to NOT NULLobclient> ALTER TABLE tbl1 MODIFY COLUMN name VARCHAR(50) NOT NULL;Set the
agecolumn to be uniqueobclient> ALTER TABLE tbl1 MODIFY COLUMN age INT UNIQUE;Execute the
DESCRIBE tbl1;command again to view the table structure of tbl1.+-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | NO | | NULL | | | age | int(11) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+----------------+
Modify the value of an auto-increment column
The syntax for modifying the value of an auto-increment column is as follows:
ALTER TABLE table_name [SET] AUTO_INCREMENT = next_value;
This example assumes a table named tbl1 exists in the database. It demonstrates how to modify the value of an auto-increment column by setting it to 12.
obclient> ALTER TABLE tbl1 AUTO_INCREMENT = 12;
After the modification, the next auto-increment value for the tbl1 table will be set to 12. When inserting new records, the auto-increment column (for example, c1) will start incrementing from 12.