ALTER TABLE |V3.2.4|OceanBase Database| docs|Distributed Database

ALTER TABLE

Last Updated：2023-10-24 09:23:03 Updated

Purpose

You can use this statement to modify the structure of an existing table. For example, you can modify a table and its attributes, add columns, modify columns and their attributes, and drop columns.

Syntax

alter_table_stmt:
      ALTER TABLE table_name alter_table_action_list;


alter_table_action_list:
    alter_table_action [, alter_table_action ...]

alter_table_action:
      ADD {column_definition | (column_definition_list)}
    | MODIFY [COLUMN] column_definition
    | MODIFY CONSTRAINT constraint_name { ENABLE | DISABLE }
    | DROP [COLUMN] column_name
    | ADD [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} [index_name] index_desc
    | ADD [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name_list) references_clause
    | ADD [CONSTRAINT [constraint_name]] CHECK (expr)
    | ADD {INDEX | KEY} [index_name] index_desc
    | ALTER INDEX index_name [VISIBLE | INVISIBLE]
    | DROP {INDEX | KEY} index_name
    | ADD PARTITION (range_partition_list)
    | DROP {PARTITION | SUBPARTITION} partition_name_list [UPDATE GLOBAL INDEXES]
    | REORGANIZE PARTITION name_list INTO partition_range_or_list
    | TRUNCATE {PARTITION | SUBPARTITION} partition_name_list [UPDATE GLOBAL INDEXES]
    | [SET] table_option_list
    | RENAME [TO] table_name
    | RENAME COLUMN old_col_name TO new_col_name
    | DROP TABLEGROUP
    | SET TABLEGROUP tablegroup_name
    | DROP CONSTRAINT constraint_name
    | {ENABLE | DISABLE} CONSTRAINT constraint_name



column_definition_list:
    column_definition [, column_definition ...]

column_definition:
    column_name data_type
    [DEFAULT const_value] [AUTO_INCREMENT]
    [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment

index_desc:
   (column_desc_list) [index_type] [index_option_list]

column_desc_list:
    column_desc [, column_desc ...]

column_desc:
     column_name [(length)] [ASC | DESC]

references_clause:
     REFERENCES table_name [ (column_name, column_name ...) ] [ON DELETE {CASCADE}]] 

index_type:
    USING BTREE

index_option_list:
    index_option [ index_option ...]

index_option:
      [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name_list)
    | comment

table_option_list:
    table_option [ table_option ...]

table_option:
    | primary_zone
    | table_tablegroup
    | block_size
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | comment
    | DUPLICATE_SCOPE [=] "none|zone|region|cluster"
    | parallel_clause

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

partition_option:
      PARTITION BY HASH(column_name_list)
      [subpartition_option] hash_partition_define
    | PARTITION BY RANGE (column_name_list)
      [subpartition_option] (range_partition_list)
    | PARTITION BY LIST (column_name_list)
      [subpartition_option] (list_partition_list)

/*Template-based subpartition*/
subpartition_option:
      SUBPARTITION BY HASH (column_name_list) hash_subpartition_define
    | SUBPARTITION BY RANGE (column_name_list) SUBPARTITION TEMPLATE
      (range_subpartition_list)
    | SUBPARTITION BY LIST (column_name_list) SUBPARTITION TEMPLATE 
      (list_subpartition_list)

/*Non-template-based subpartition*/
subpartition_option:
      SUBPARTITION BY HASH (column_name_list)
    | SUBPARTITION BY RANGE (column_name_list) 
    | SUBPARTITION BY LIST (column_name_list) 
    
subpartition_list:
      (hash_subpartition_list)
    | (range_subpartition_list)
    | (list_subpartition_list)
    
hash_partition_define:
      PARTITIONS partition_count [TABLESPACE tablespace] [compression]
    | (hash_partition_list)
    
hash_partition_list:
    hash_partition [, hash_partition, ...]
    
hash_partition:
    partition [partition_name] [subpartition_list/*Definable for non-template-based subpartitions only*/]
    
hash_subpartition_define:
      SUBPARTITIONS subpartition_count
    | SUBPARTITION TEMPLATE (hash_subpartition_list)

hash_subpartition_list:
    hash_subpartition [, hash_subpartition, ...]
    
hash_subpartition:
    subpartition [subpartition_name]
    
range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION [partition_name]
    VALUES LESS THAN {(expression_list) | (MAXVALUE)} 
    [subpartition_list/*Definable for non-template-based subpartitions only*/] 
    [ID = num] [physical_attribute_list] [compression]

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE} [physical_attribute_list]

list_partition_list:
    list_partition [, list_partition] ...

list_partition:
    PARTITION [partition_name] 
    VALUES (DEFAULT|expression_list) 
    [subpartition_list/*Definable for non-template-based subpartitions only*/]
    [ID num] [physical_attribute_list] [compression]

list_subpartition_list:
    list_subpartition [, list_subpartition] ...

list_subpartition:
    SUBPARTITION [partition_name] VALUES (DEFAULT|expression_list) [physical_attribute_list]

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_name_list:
    partition_name [, partition_name ...]

partition_count | subpartition_count:
    INT_VALUE

Parameters

Parameter	Description
ADD	Adds a column. You cannot add a primary key column.
MODIFY [COLUMN]	Modifies the column attribute.
MODIFY CONSTRAINT	Enables or disables the foreign key constraint or `CHECK` constraint.
DROP [COLUMN]	Drops a column. You cannot drop a primary key column or columns that contain indexes.
ADD [UNIQUE INDEX]	Adds a unique index.
ADD [INDEX]	Adds a normal index.
ALTER [INDEX]	Modifies index attributes.
ADD [PARTITION]	Adds a partition.
DROP {PARTITION \| SUBPARTITION}	Drops partitions: `PARTITION`: drops a specified RANGE or LIST partition (all subpartitions under it will also be dropped), including the partition definition and data, and maintains the local indexes on the partitioned table. `SUBPARTITION`: drops a specified -RANGE or -LIST subpartition, including the subpartition definition and data, and maintains the local indexes on the subpartitioned table. If you specify `UPDATE GLOBAL INDEXES`, the system updates global indexes when dropping the partition or subpartition. If you do not specify UPDATE GLOBAL INDEXES, the global indexes on the partitioned or subpartitioned table must be in an unusable state. Separate multiple partition names with commas (,). Notice Before you delete a partition, ensure that no active transactions or queries exist in this partition. Otherwise, SQL statement errors or exceptions may occur.
REORGANIZE [PARTITION]	Reorganizes a partition.
TRUNCATE {PARTITION \| SUBPARTITION}	Truncates partitions: `PARTITION`: clears all data in a specified RANGE or LIST partition (data in all subpartitions under it will also be cleared), and maintains the local indexes on the partitioned table. `SUBPARTITION`: clears all data in a specified -RANGE or -LIST subpartition, and maintains the local indexes on the subpartitioned table. If you specify `UPDATE GLOBAL INDEXES`, the system updates global indexes when clearing the data. If you do not specify UPDATE GLOBAL INDEXES, the global indexes on the partitioned or subpartitioned table must be in an unusable state. Separate multiple partition names with commas (,). Notice Before you delete partition data, ensure that no active transactions or queries exist in this partition. Otherwise, SQL statement errors or exceptions may occur.
RENAME [TO] table_name	Renames a table.
DROP [TABLEGROUP]	Drops a table group.
SET TABLEGROUP	Sets the table group to which the table belongs.
DROP [CONSTRAINT]	Deletes a constraint.
SET BLOCK_SIZE	Sets the block size of a table partition.
SET COMPRESSION	Sets the compression mode of the table.
SET USE_BLOOM_FILTER	Specifies whether to use BloomFilter.
SET COMMENT	Sets comment information.
SET PROGRESSIVE_MERGE_NUM	Sets the number of progressive compaction steps. Value range: [1, 64].
parallel_clause	The degree of parallelism (DOP) at the table level. `NOPARALLEL`: sets the DOP to `1`, which is the default value. `PARALLEL integer`: sets the degree of parallelism to an `integer` greater than or equal to `1`. Notice When you specify the DOP, the following priority order applies: DOP specified through hint > DOP specified through `ALTER SESSION` > DOP at the table level.
{ENABLE \| DISABLE} CONSTRAINT constraint_name	Enables or disables the foreign key constraint or `CHECK` constraint.

Examples

Modify the data type of col1 in the tbl1 table.

obclient> CREATE TABLE tbl1(col1 VARCHAR(3));
Query OK, 0 rows affected

obclient> ALTER TABLE tbl1 MODIFY col1 CHAR(10);
Query OK, 0 rows affected

obclient> DESCRIBE tbl1;
+-------+----------+------+-----+---------+-------+
| FIELD | TYPE     | NULL | KEY | DEFAULT | EXTRA |
+-------+----------+------+-----+---------+-------+
| COL1  | CHAR(10) | YES  | NULL | NULL    | NULL  |
+-------+----------+------+-----+---------+-------+
1 row in set

Add and drop a column.

Create a table named tbl2.

obclient> CREATE TABLE tbl2 (col1 NUMBER(30) PRIMARY KEY,col2 VARCHAR(50));
Query OK, 0 rows affected

Add the col3 column to the tbl2 table.

obclient> ALTER TABLE tbl2 ADD col3 INT;
Query OK, 0 rows affected

Add the p1_r4 subpartition to the p1 partition in a non-template-based subpartitioned table named tbl3.

obclient> ALTER TABLE tbl9 MODIFY PARTITION p1 ADD SUBPARTITION p1_r4 VALUES LESS THAN(2022);
Query OK, 0 rows affected

Drop the p3_r3 subpartition from a non-template-based subpartitioned table named tbl3.
```
obclient> ALTER TABLE tbl3 DROP SUBPARTITION p2_r3;
Query OK, 0 rows affected
```

Add the p4 partition to a non-template-based subpartitioned table named tbl3. You need to specify both the partition definition and the subpartition definition.

obclient> ALTER TABLE tbl3 ADD PARTITION p4 VALUES LESS THAN (400) 
(
  SUBPARTITION p4_r1 VALUES LESS THAN (2019),
  SUBPARTITION p4_r2 VALUES LESS THAN (2020),
  SUBPARTITION p4_r3 VALUES LESS THAN (2021)
);
Query OK, 0 rows affected

Add the p3 partition to a template-based subpartitioned table named tbl4. You need to specify only the partition definition. The subpartition definition is filled in automatically based on the template.

obclient> CREATE TABLE tbl4(col1 INT, col2 INT, PRIMARY KEY(col1,col2))
     PARTITION BY RANGE(col1)
     SUBPARTITION BY RANGE(col2)
     SUBPARTITION TEMPLATE
     (
     SUBPARTITION p0 VALUES LESS THAN (50),
     SUBPARTITION p1 VALUES LESS THAN (100)
     )
     (
     PARTITION p0 VALUES LESS THAN (100),
     PARTITION p1 VALUES LESS THAN (200),
     PARTITION p2 VALUES LESS THAN (300)
     );
Query OK, 0 rows affected

obclient> ALTER TABLE tbl4 ADD PARTITION p3 VALUES LESS THAN (400);
Query OK, 0 rows affected

Change the DOP of the tbl5 table to 3.

obclient> CREATE TABLE tbl5(col1 INT PRIMARY KEY, col2 INT) PARALLEL 5;
Query OK, 0 rows affected

obclient> ALTER TABLE tbl5 PARALLEL 3;
Query OK, 0 rows affected

Modify the status of a foreign key constraint.

obclient> CREATE TABLE MMS_GROUPUSER (
    "ID" VARCHAR2(254 BYTE) NOT NULL,
    "GROUPID" VARCHAR2(254 BYTE),
    "USERID" VARCHAR2(254 BYTE),
     CONSTRAINT "PK_MMS_GROUPUSER" PRIMARY KEY ("ID"),
     CONSTRAINT "FK_MMS_GROUPUSER_02" FOREIGN KEY ("GROUPID") REFERENCES MMS_GROUPUSER ("ID") ON DELETE CASCADE DISABLE
     );
Query OK, 0 rows affected

obclient> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints WHERE CONSTRAINT_NAME LIKE 'FK_MMS_GROUPUSE%';
+---------------------+-----------------+---------------+----------+
| CONSTRAINT_NAME     | CONSTRAINT_TYPE | TABLE_NAME    | STATUS   |
+---------------------+-----------------+---------------+----------+
| FK_MMS_GROUPUSER_02 | R               | MMS_GROUPUSER | DISABLED |
+---------------------+-----------------+---------------+----------+
1 row in set

obclient> ALTER TABLE MMS_GROUPUSER ENABLE CONSTRAINT FK_MMS_GROUPUSER_02;
Query OK, 0 rows affected

obclient> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints WHERE CONSTRAINT_NAME LIKE 'FK_MMS_GROUPUSE%';
+---------------------+-----------------+---------------+---------+
| CONSTRAINT_NAME     | CONSTRAINT_TYPE | TABLE_NAME    | STATUS  |
+---------------------+-----------------+---------------+---------+
| FK_MMS_GROUPUSER_02 | R               | MMS_GROUPUSER | ENABLED |
+---------------------+-----------------+---------------+---------+
1 row in set

Clear all data in the M202001 and M202002 partitions of the tbl6 partitioned table.

obclient> CREATE TABLE tbl6 (log_id number NOT NULL,log_value varchar2(50),log_date date NOT NULL DEFAULT sysdate)
     PARTITION BY RANGE(log_date) 
   (
       PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
     , PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
     , PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
     , PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
     , PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD')) 
     , PARTITION MMAX VALUES LESS THAN (MAXVALUE)
    );
Query OK, 0 rows affected

obclient> ALTER TABLE tbl6 TRUNCATE PARTITION M202001, M202002 UPDATE GLOBAL INDEXES;
Query OK, 0 rows affected

Drop the CHECK constraint named tbl7_equal_check1 on the tbl7 table.

obclient> CREATE TABLE tbl7 (col1 INT, col2 INT, col3 INT,CONSTRAINT tbl7_equal_check1 CHECK(col2 = col3 * 2) ENABLE VALIDATE);
Query OK, 0 rows affected

obclient> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints WHERE TABLE_NAME LIKE 'TBL%';
+-------------------+-----------------+------------+---------+
| CONSTRAINT_NAME   | CONSTRAINT_TYPE | TABLE_NAME | STATUS  |
+-------------------+-----------------+------------+---------+
| TBL8_EQUAL_CHECK1 | C               | TBL8       | ENABLED |
| TBL7_EQUAL_CHECK1 | C               | TBL7       | ENABLED |
+-------------------+-----------------+------------+---------+
2 rows in set

obclient> ALTER TABLE tbl7 DROP CONSTRAINT tbl7_equal_check1;
Query OK, 0 rows affected

obclient> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints WHERE TABLE_NAME LIKE 'TBL%';
+-------------------+-----------------+------------+---------+
| CONSTRAINT_NAME   | CONSTRAINT_TYPE | TABLE_NAME | STATUS  |
+-------------------+-----------------+------------+---------+
| TBL8_EQUAL_CHECK1 | C               | TBL8       | ENABLED |
+-------------------+-----------------+------------+---------+
1 row in set

Move the tbl8 table from the tblgroup1 table group to the tblgroup2 table group.

obclient> SHOW TABLEGROUPS;
+-----------------+------------+---------------+
| TABLEGROUP_NAME | TABLE_NAME | DATABASE_NAME |
+-----------------+------------+---------------+
| TBLGROUP1             | TBL8         | SYS           |
| TBLGROUP2             | NULL       | NULL          |
| oceanbase       | NULL       | NULL          |
+-----------------+------------+---------------+
3 rows in set

obclient> ALTER TABLE tbl8 SET TABLEGROUP tblgroup2;
Query OK, 0 rows affected

obclient> SHOW TABLEGROUPS;
+-----------------+------------+---------------+
| TABLEGROUP_NAME | TABLE_NAME | DATABASE_NAME |
+-----------------+------------+---------------+
| TBLGROUP1             | NULL       | NULL          |
| TBLGROUP2             | TBL8         | SYS           |
| oceanbase       | NULL       | NULL          |
+-----------------+------------+---------------+
3 rows in set