This topic describes how to rename a table, rename a column, modify a column type, rename a column and change the column type, change a normal column to an auto-increment column, add a column, delete a column, add a primary key, modify a primary key, delete a primary key, add a foreign key, delete a foreign key, add a CHECK constraint, and modify a table partition.
Prerequisites
The current user has the ALTER permission.
Example 1
Create a table named tbl1.
obclient> CREATE TABLE tbl1(col1 INT,col2 INT,col3 VARCHAR(50));
Query OK, 0 rows affected
Example 1-1 Rename a table
Rename the tbl1 table as test_tbl1.
obclient> ALTER TABLE tbl1 RENAME test_tbl1;
Query OK, 0 rows affected
Example 1-2 Rename a column
Rename the col3 column in the test_tbl1 table as name.
obclient> ALTER TABLE test_tbl1 CHANGE col3 name VARCHAR(50);
Query OK, 0 rows affected
obclient> DESC test_tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col1 | int(11) | YES | | NULL | |
| col2 | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set
Example 1-3 Change the type of a column
Change the data type of the col2 column in the test_tbl1 table from INT(11) to VARCHAR(128).
obclient> ALTER TABLE test_tbl1 MODIFY col2 VARCHAR(128);
Query OK, 0 rows affected
obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1 | int(11) | YES | | NULL | |
| col2 | varchar(128) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set
Example 1-4 Rename a column and change the column type at the same time
Change the name and data type of the name column in the test_tbl1 table to col3 and LONGTEXT respectively.
obclient> ALTER TABLE test_tbl1 CHANGE name col3 LONGTEXT;
Query OK, 0 rows affected
obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1 | int(11) | YES | | NULL | |
| col2 | varchar(128) | YES | | NULL | |
| col3 | longtext | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set
Example 1-5 Change a normal column to an auto-increment column
Change the col1 column in the test_tbl1 table to an auto-increment column.
obclient> ALTER TABLE test_tbl1 MODIFY col1 INT AUTO_INCREMENT;
Query OK, 0 rows affected
obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| col1 | int(11) | NO | | NULL | auto_increment |
| col2 | varchar(128) | YES | | NULL | |
| col3 | longtext | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set
Example 1-6 Add a column
In the test_tbl1 table, add a column named col4.
obclient> ALTER TABLE test_tbl1 ADD col4 char(1);
Query OK, 0 rows affected
obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| col1 | int(11) | NO | | NULL | auto_increment |
| col2 | varchar(128) | YES | | NULL | |
| col3 | longtext | YES | | NULL | |
| col4 | char(1) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set
Example 1-7 Drop a column
Note
OceanBase Database allows you to drop regular columns and columns with indexes in a table. However, columns with primary keys cannot be dropped directly. To drop a primary key column, you can drop the primary key first and then drop the column.
Example 1-7-1 Drop a regular column
In the test_tbl1 table, drop the col4 column.
obclient> ALTER TABLE test_tbl1 DROP col4;
Query OK, 0 rows affected
obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| col1 | int(11) | NO | | NULL | auto_increment |
| col2 | varchar(128) | YES | | NULL | |
| col3 | longtext | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set
Example 1-7-2 Drop a column with indexes
obclient> CREATE TABLE test_tbl2 (col1 INT,col2 INT,col3 INT,PRIMARY KEY(col1),INDEX(col2,col3));
Query OK, 0 rows affected
obclient> DESC test_tbl2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1 | int(11) | NO | PRI | NULL | |
| col2 | int(11) | YES | MUL | NULL | |
| col3 | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set
#Drop the col2 column.
obclient> ALTER TABLE test_tbl2 DROP col2;
Query OK, 0 rows affected
obclient> DESC test_tbl2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1 | int(11) | NO | PRI | NULL | |
| col3 | int(11) | YES | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set
Example 1-8 Add a primary key
Set the col1 column in the test_tbl1 table as the primary key.
obclient> ALTER TABLE test_tbl1 ADD PRIMARY KEY (col1);
Query OK, 0 rows affected
obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| col1 | int(11) | NO | PRI | NULL | auto_increment |
| col2 | varchar(128) | YES | | NULL | |
| col3 | longtext | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set
Example 1-9 Modify a primary key
Change the primary key of the test_tbl1 table to col2.
obclient> ALTER TABLE test_tbl1 DROP PRIMARY KEY,ADD PRIMARY KEY(col2);
Query OK, 0 rows affected
obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| col1 | int(11) | NO | | NULL | auto_increment |
| col2 | varchar(128) | NO | PRI | NULL | |
| col3 | longtext | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set
Example 1-10 Drop a primary key
Note
In OceanBase Database, you cannot delete a primary key from a MySQL tenant in the following conditions:
- The table is a parent table that contains foreign key information.
- The table is a child table, but the primary key column contains a foreign key reference column.
Examples
Drop the primary key of the test_tbl1 table.
obclient> ALTER TABLE test_tbl1 DROP PRIMARY KEY;
Query OK, 0 rows affected
obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| col1 | int(11) | NO | | NULL | auto_increment |
| col2 | varchar(128) | NO | | NULL | |
| col3 | longtext | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set
Example 1-11 Add a foreign key
Create the tbl1 and tbl2 tables, and add the foreign key fk1_tbl1 to the col2 column in the tbl1 table.
obclient> CREATE TABLE tbl1(col1 INT PRIMARY KEY,col2 INT(50));
Query OK, 0 rows affected
obclient> CREATE TABLE tbl2(col1 INT(11) PRIMARY KEY,col2 INT(50));
Query OK, 0 rows affected
obclient> ALTER TABLE tbl1 ADD CONSTRAINT fk1_tbl1 FOREIGN KEY (col2) REFERENCES tbl2(col1);
Query OK, 0 rows affected
Example 1-12 Drop a foreign key
Drop the foreign key constraint fk1_tbl1 of the tbl1 table.
obclient> ALTER TABLE tbl1 DROP FOREIGN KEY fk1_tbl1;
Query OK, 0 rows affected
Example 1-13 Add a CHECK constraint
Add a CHECK constraint to the col1 column of the tbl1 table. The values in the col1 column must be greater than 0.
obclient> ALTER TABLE test_tbl1 MODIFY col1 INT CHECK(col1>0);
Query OK, 0 rows affected
Example 2 Modify the partitioning rule of a table
OceanBase Database supports the following table partitioning operations:
- Convert a non-partitioned table into a partitioned or subpartitioned table, regardless of whether the subpartitioned table is created based on a template.
- Modify the partitioning mode of a partitioned table. For example, you can change a partitioned table into a subpartitioned table.
Example 2-1 Convert a non-partitioned table into a partitioned table
Create a non-partitioned table named tbl3 and convert the tbl1 table into a HASH-partitioned table.
obclient> CREATE TABLE tbl3(col1 INT,col2 INT);
Query OK, 0 rows affected
obclient> ALTER TABLE tbl3 PARTITION BY HASH(col1) PARTITIONS 5;
Query OK, 0 rows affected
Example 2-2 Convert a non-partitioned table into a subpartitioned table
Create a non-partitioned table named tbl4 and convert the tbl4 table into a template-based HASH-RANGE-subpartitioned table.
obclient> CREATE TABLE tbl4(col1 INT,col2 INT);
Query OK, 0 rows affected
obclient> ALTER TABLE tbl4 PARTITION BY HASH(col1)
SUBPARTITION BY RANGE(col2)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 VALUES LESS THAN (2020),
SUBPARTITION sp2 VALUES LESS THAN (2024))
PARTITIONS 5;
Query OK, 0 rows affected
Example 2-3 Convert a partitioned table into a subpartitioned table
Create a HASH-partitioned table named tbl5 and convert the tbl5 table into a template-based HASH-RANGE-subpartitioned table.
obclient> CREATE TABLE tbl5(col1 INT,col2 INT) PARTITION BY HASH(col1) PARTITIONS 5;
Query OK, 0 rows affected
obclient> ALTER TABLE tbl5 PARTITION BY HASH(col1)
SUBPARTITION BY RANGE COLUMNS(col2)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 VALUES LESS THAN(2022),
SUBPARTITION sp2 VALUES LESS THAN(2024))
PARTITIONS 10;
Query OK, 0 rows affected