Skip to main content

Column operations

seekdb supports the following column operations: adding columns at the end, adding columns at other positions, dropping columns, renaming columns, reordering columns, changing column types, managing default values for columns, managing constraints, and changing the values of auto-increment columns.

Add a column​

In seekdb, you can add a column to a table at the end or in the middle of the table. This topic describes how to add a column to a table at the end or in the middle of the table.

Add a column at the end of a table​

Syntax:

ALTER TABLE table_name ADD COLUMN column_name data_type;

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.

  • data_type: the data type of the column to be added.

Assume that a table named tbl1 exists in the database. The following example shows how to add a column named c1 to the end of the tbl1 table.

  1. View the schema of the tbl1 table.

    DESCRIBE tbl1;

    The output shows that the tbl1 table contains three columns: id, name, and age.

    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | NO   | PRI | NULL    |       |
    | name  | varchar(50) | NO   |     | NULL    |       |
    | age   | int(11)     | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+

  2. Add a column named c1 to the end of the tbl1 table. The data type of the column is INT.

    ALTER TABLE tbl1 ADD COLUMN c1 INT;

  3. View the schema of the tbl1 table again.

    DESCRIBE tbl1;

    The output shows that the c1 column has been added to the end of the tbl1 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    |       |
    +-------+-------------+------+-----+---------+-------+

Add a column in the middle of a table​

Syntax:

ALTER TABLE table_name
    ADD COLUMN new_column_name data_type
    {FIRST | BEFORE | AFTER} column_name;

The following table describes the parameters.

  • table_name: the name of the table to which you want to add a column.

  • 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 of the column to be added. You can add the column at the beginning of the table (FIRST), before the specified column (BEFORE), or after the specified column (AFTER).

    tip

    If you specify FIRST to add the column at the beginning of the table, you do not need to specify the column_name parameter. Otherwise, an error will be returned.

  • column_name: the name of the column before or after which you want to add a column.

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

The following example shows how to add a column named c1 to the beginning of the tbl1 table, a column named c2 before the name column, and a column named c3 after the name column.

  1. Add a column named c1 to the beginning of the tbl1 table. The data type of the column is INT.

    ALTER TABLE tbl1 ADD COLUMN c1 INT FIRST;

  2. Add a column named c2 before the name column in the tbl1 table. The data type of the column is VARCHAR.

    ALTER TABLE tbl1 ADD COLUMN c2 VARCHAR(50) BEFORE name;

  3. Add a column named c3 after the name column in the tbl1 table. The data type of the column is VARCHAR, and the column cannot be empty.

    ALTER TABLE tbl1 ADD COLUMN c3 VARCHAR(25) NOT NULL AFTER name;

  4. View the schema of the tbl1 table again.

    DESCRIBE tbl1;

    The output shows that the c1, c2, and c3 columns have been added to the beginning, before the name column, and after the name column of the tbl1 table, respectively.

    +-------+-------------+------+-----+---------+-------+
    | 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 a column​

Syntax:

ALTER TABLE table_name DROP COLUMN column_name

In this syntax, table_name specifies the name of the table to 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 schema 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    |       |
+-------+-------------+------+-----+---------+-------+

The following example shows how to drop the name column from the tbl1 table.

ALTER TABLE tbl1 DROP COLUMN name;

After the name column is dropped, execute the DESCRIBE tbl1; statement again to view the schema of the tbl1 table. The output shows that the name column no longer exists in the tbl1 table.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Drop multiple columns​

Syntax:

ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2, ...;

In this syntax, table_name specifies the name of the table to 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 schema of the tbl1 table 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  |
+-------+--------------+------+------+---------+-------+

The following example shows how to drop multiple columns from the tbl1 table by using the ALTER TABLE table_name DROP COLUMN column_name1, column_name2, ... statement.

ALTER TABLE tbl1 DROP COLUMN C4, DROP COLUMN C5;

After the C4 and C5 columns are dropped, execute the DESCRIBE tbl1; statement again to view the schema of the tbl1 table. The output shows that the C4 and C5 columns no longer exist in the tbl1 table.

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

Syntax:

ALTER TABLE TABLE_NAME FORCE;

The following example shows how to drop the C2, C3, C4, and C5 columns from the tbl1 table.

ALTER TABLE tbl1 FORCE;

Rename columns​

You can rename columns by using the following two statements:

  • Rename a column and change its data type simultaneously.

    ALTER TABLE table_name CHANGE old_col_name new_col_name data_type;

    The following table describes the parameters.

    • table_name Specifies 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 to be renamed.

    • data_type: the data type of the column to be renamed. You can specify the existing data type for this column or change the data type of the column to another supported type. For more information about the data types that can be changed, see [Change column type](#Change the column type).

  • RENAME COLUMN * RENAME COLUMN

    ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name

    The following table describes the parameters:

    • table_name: specifies the name of the table where the column is located.

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

    • new_col_name: Specifies the new column name.

Assume that a table named tbl1 is created. The table structure is shown below:

+-------+-------------+------+-----+---------+-------+
| 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 RENAME COLUMN statement. It describes the process of renaming the column name of the name column to c2, the column name of the age column to c3, and the data type of the age column to VARCHAR.

  1. Rename the name column to c2.

    ALTER TABLE tbl1 RENAME COLUMN name TO c2;

  2. Rename the age column to c3 and change the data type of the age column to VARCHAR.

    ALTER TABLE tbl1 CHANGE age c3 VARCHAR(50);

  3. Execute the DESCRIBE tbl1; command again to view the table structure.

    The following output is displayed. The tbl1 table contains only three columns: id, c2, and c3, with the data type of c3 is VARCHAR.

    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | NO   | PRI | NULL    |       |
    | c2    | varchar(50) | YES  |     | NULL    |       |
    | c3    | varchar(50) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+

Rearranging​

The basic syntax is as follows:

ALTER TABLE table_name
    MODIFY [COLUMN] column_name data_type
    {FIRST | BEFORE | AFTER} column_name;

The following table describes the related parameters:

  • table_name: specifies the name of the table to be rebuilt.

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

  • data_type : specify the data type of the column to be rearranged. The column can be of the original data type or a different data type. For more information about the data types that can be modified, see [Modify a column type](#Change the column type).

  • FIRST | BEFORE | AFTER：Specifies the position where to add a column. You can add a column to the beginning of the table (FIRST), before the column named column_name (BEFORE), or after the column named column_name (AFTER).

    tip

    When you use FIRST to add a column to the beginning of the table, you do not need to set column_name, otherwise an error will be returned.

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

Assume that the database contains a table named tbl1. 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 topic provides an example of how to use a rearrangement statement to move the age column before the name column and to change the data type of the age column to VARCHAR.

ALTER TABLE tbl1 MODIFY COLUMN age VARCHAR(50) BEFORE name;

The output is as follows: The age column is located before the name column, and the data type of the age column is VARCHAR.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| age   | varchar(50) | YES  |     | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Change the column type​

The following table lists the column type conversion supported by seekdb:

  • Conversion of the data types of character type columns, including CHAR, VARCHAR, TINYTEXT, TEXT, and LONGTEXT.

  • Conversion of the data types of numeric type columns, including TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT.

  • Conversion of the data types of binary type columns, including BINARY, VARBINARY, BLOB, TINYBLOB, MEDIUMBLOB, and LONGBLOB.

  • Change of the precision of the data types that support precision, including VARCHAR, FLOAT, DOUBLE, and DECIMAL.

  • Conversion between the data types that support precision, including FLOAT, DOUBLE, and DECIMAL.

  • Conversion between different data types, including INT, VARCHAR, DOUBLE, FLOAT, and DECIMAL.

For more information about the rules for changing the column type, see Rules for changing the column type.

The syntax for changing the 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 to be modified.

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

  • data_type: the new data type.

Examples of conversion between character data types​

The following example creates a table named test01:

CREATE TABLE test01 (c1 INT PRIMARY KEY, c2 CHAR(10), c3 CHAR(10));

The following examples use the test01 table to describe how to change the data type of a character data type column and increase the length.

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

    ALTER TABLE test01 MODIFY c2 VARCHAR(20);

  • Change the data type of the c2 column in the test01 table to VARCHAR and set the maximum length to 40 characters.

    ALTER TABLE test01 MODIFY c2 VARCHAR(40);

  • Change the data type of the c2 column in the test01 table to TINYTEXT.

    ALTER TABLE test01 MODIFY c2 TINYTEXT;

  • Change the data type of the c2 column in the test01 table to LONGTEXT.

    ALTER TABLE test01 MODIFY c2 LONGTEXT;

  • Change the length of the c3 column in the test01 table to 20 characters.

    ALTER TABLE test01 MODIFY c3 CHAR(20);

  • Change the data type of the c3 column in the test01 table to VARCHAR and set the maximum length to 30 characters.

    ALTER TABLE test01 MODIFY c3 VARCHAR(30);

Examples of conversion between numeric data types​

Integer types​

The following example creates a table named test02:

CREATE TABLE test02 (id INT PRIMARY KEY, name VARCHAR(10),age TINYINT, description VARCHAR(65525));

The following examples use the test02 table to describe how to change the data type of an integer type column and increase the length.

  • Change the data type of the age column in the test02 table to SMALLINT.

    ALTER TABLE test02 MODIFY age SMALLINT;

  • Change the data type of the age column in the test02 table to INT.

    ALTER TABLE test02 MODIFY age INT;

  • Change the data type of the age column in the test02 table to BIGINT.

    ALTER TABLE test02 MODIFY age BIGGINT;

Data types with precision​

The following example creates a table named test03:

CREATE TABLE test03(c1 INT, c2 FLOAT(8,2), c3 FLOAT(8,2), UNIQUE(c2, c3));

The following examples use the test03 table to describe how to change the data type and length of a column with precision.

  • Change the number of digits that the c2 column in the test03 table can store to 5.

    ALTER TABLE test03 MODIFY c2 FLOAT(5,2);

  • Change the data type of the c2 column in the test03 table to DOUBLE and set the total number of digits that can be stored to 10, with 4 digits after the decimal point.

    ALTER TABLE test03 MODIFY c2 DOUBLE(10,4);

  • Change the data type of the c2 column in the test03 table to DOUBLE and set the number of digits that can be stored to 5, with 2 digits after the decimal point.

    ALTER TABLE test03 MODIFY c2 DOUBLE(5,2);

  • Change the data type of the c2 column in the test03 table to DECIMAL and set the total number of digits that can be stored to 20, with 4 digits after the decimal point.

    ALTER TABLE test03 MODIFY c2 DECIMAL(20, 4);

Examples of conversion between binary data types​

The following example creates a table named test04:

CREATE TABLE test04 (c1 TINYBLOB, c2 BINARY(64));

The following examples use the test04 table to describe how to change the data type and length of a binary data type column.

  • Change the data type of the c1 column in the test04 table to BLOB.

    ALTER TABLE test04 MODIFY c1 BLOB;

  • Change the data type of the c1 column in the test04 table to BINARY and set the maximum number of bytes that can be stored to 256.

    ALTER TABLE test04 MODIFY c1 BINARY(256);

  • Change the data type of the c1 column in the test04 table to VARCHAR and set the maximum length to 256 characters.

    ALTER TABLE test04 MODIFY c1 VARCHAR(256);

Examples of conversion between integer and character data types​

The following example creates a table named test05:

CREATE TABLE test05 (c1 INT);

  1. Execute the following command to change the data type of the c1 column in the test05 table to VARCHAR and set the maximum length to 64 characters.

    ALTER TABLE test05 MODIFY c1 VARCHAR(64);

  2. Execute the following command to change the data type of the c1 column in the test05 table to INT.

    ALTER TABLE test05 MODIFY c1 INT;

Manage default values of columns​

In seekdb, you can modify and delete the default values of columns. This topic describes how to perform these operations.

Modify the default value of a column​

If no default value is specified for a column, the default value is NULL. To modify the default value of a column, use the following syntax:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT const_value;

The 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 you want to modify.

  • const_value: the new default value of the column.

Assume that the database contains a table named tbl1. The schema 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    |       |
+-------+-------------+------+-----+---------+-------+

The following example shows how to modify the default value of the age column to 18.

ALTER TABLE tbl1 ALTER COLUMN age SET DEFAULT 18;

Run the DESCRIBE tbl1; command again to view the schema of tbl1. The output shows that the default value of the age column is 18.

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

To delete the default value of a column, use the following syntax:

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

In this syntax, table_name specifies the name of the table that contains the column whose default value you want to delete, and column_name specifies the name of the column.

Assume that the database contains a table named tbl1. The schema 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      |       |
+-------+-------------+------+-----+---------+-------+

The following example shows how to delete the default value of the age column.

ALTER TABLE tbl1 ALTER COLUMN age DROP DEFAULT;

Run the DESCRIBE tbl1; command again to view the schema of tbl1. The output shows that 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​

In seekdb, you can add constraints to a table, such as modifying an existing column to an auto-increment column, setting whether a column can be NULL, and specifying the uniqueness of a column. This topic 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 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 you want to add a constraint.

  • data_type: 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 more information about the data types that can be modified, see [Modify the column type](#Change the 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 or cannot be NULL.

  • [PRIMARY] KEY: specifies that the selected column is a primary key.

  • UNIQUE [KEY]: specifies that the selected column is unique.

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

  1. Set the id column as the primary key.

    ALTER TABLE tbl1 MODIFY COLUMN id INT PRIMARY KEY;

  2. Set the id column as an auto-increment column.

    ALTER TABLE tbl1 MODIFY COLUMN id INT AUTO_INCREMENT;

  3. Set the name column to be non-nullable.

    ALTER TABLE tbl1 MODIFY COLUMN name VARCHAR(50) NOT NULL;

  4. Set the age column to be unique.

    ALTER TABLE tbl1 MODIFY COLUMN age INT UNIQUE;

  5. Run the DESCRIBE tbl1; command again to view the schema 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​

To modify the value of an auto-increment column, use the following syntax:

ALTER TABLE table_name [SET] AUTO_INCREMENT = next_value;

Assume that the database contains a table named tbl1. The following example shows how to modify the value of the auto-increment column in tbl1 to 12.

ALTER TABLE tbl1 AUTO_INCREMENT = 12;

After the modification, the next auto-increment value for tbl1 is set to 12. When you insert a new record, the auto-increment column (for example, c1) will start from 12 and increment.

References​

ALTER TABLE

Contents

Edit this page