Modify the structure of a table|V3.2.4|OceanBase Database| docs|Distributed Database

Modify the structure of a table

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

This topic describes how to rename a column, rename a table, modify the type of a column, add a column, and drop a column in OceanBase Database in Oracle mode.

Prerequisites

You have the ALTER privilege on the target table.

Syntax

alter_table_stmt:
      ALTER TABLE table_name
      alter_table_action_list;
    | RENAME TABLE rename_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
    | DROP TABLEGROUP
    | SET TABLEGROUP tablegroup_name
    | DROP CONSTRAINT constraint_name
    | {ENABLE | DISABLE} CONSTRAINT constraint_name

rename_table_action_list:
    rename_table_action [, rename_table_action ...]

rename_table_action:
    table_name TO table_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
    | replica_num
    | 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/*Only non-template-based subpartitions can be defined.*/]
    
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/*Only non-template-based subpartitions can be defined.*/] 
    [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/*Only non-template-based subpartitions can be defined.*/]
    [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 dropping a partition, ensure that there are no active transactions or queries 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 deleting partition data, ensure that there are no active transactions or queries 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]	Drops a constraint.
SET BLOCK_SIZE	Sets the block size of a table partition.
SET REPLICA_NUM	Specifies the number of replicas of the table.
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 by using a hint > DOP specified by executing the `ALTER SESSION` statement > DOP at the table level.
{ENABLE \| DISABLE} CONSTRAINT constraint_name	Enables or disables the foreign key constraint or `CHECK` constraint.

Examples

Rename a column

Rename the name1 column in the dws_ny table as name.

View the structure of the dws_ny table.

obclient> DESC dws_ny;
+---------+---------------+------+-----+---------+-------+
| FIELD   | TYPE          | NULL | KEY | DEFAULT | EXTRA |
+---------+---------------+------+-----+---------+-------+
| ID      | NUMBER        | NO   | PRI | NULL    | NULL  |
| NAME1   | VARCHAR2(18)  | YES  | NULL | NULL    | NULL |
| SEX     | CHAR(1)       | YES  | NULL | NULL    | NULL |
| AGE     | NUMBER        | YES  | NULL | NULL    | NULL |
| ADDRESS | VARCHAR2(200) | YES  | NULL | NULL    | NULL |
| EMAIL   | VARCHAR2(100) | YES  | NULL | NULL    | NULL |
| C_DATE  | DATE          | YES  | NULL | NULL    | NULL |
+---------+---------------+------+-----+---------+-------+
7 rows in set

Rename the name1 field as name.

obclient> ALTER TABLE dws_ny RENAME COLUMN name1 TO name;
Query OK, 0 rows affected

The name1 column in the dws_ny table changes to the name column.

obclient> DESC dws_ny;
+---------+---------------+------+-----+---------+-------+
| FIELD   | TYPE          | NULL | KEY | DEFAULT | EXTRA |
+---------+---------------+------+-----+---------+-------+
| ID      | NUMBER        | NO   | PRI | NULL    | NULL  |
| NAME    | VARCHAR2(18)  | YES  | NULL | NULL    | NULL  |
| SEX     | CHAR(1)       | YES  | NULL | NULL    | NULL  |
| AGE     | NUMBER        | YES  | NULL | NULL    | NULL  |
| ADDRESS | VARCHAR2(200) | YES  | NULL | NULL    | NULL  |
| EMAIL   | VARCHAR2(100) | YES  | NULL | NULL    | NULL  |
| C_DATE  | DATE          | YES  | NULL | NULL    | NULL  |
+---------+---------------+------+-----+---------+-------+
7 rows in set

Modify the type of a column

Modify the length of the VARCHAR2 type from (18) to (128) for the name column in the dws_ny table.

View the dws_ny table.

obclient> DESC dws_ny;
+---------+---------------+------+-----+---------+-------+
| FIELD   | TYPE          | NULL | KEY | DEFAULT | EXTRA |
+---------+---------------+------+-----+---------+-------+
| ID      | NUMBER        | NO   | PRI | NULL    | NULL  |
| NAME    | VARCHAR2(18)  | YES  | NULL | NULL    | NULL |
| SEX     | CHAR(1)       | YES  | NULL | NULL    | NULL |
| AGE     | NUMBER        | YES  | NULL | NULL    | NULL |
| ADDRESS | VARCHAR2(200) | YES  | NULL | NULL    | NULL |
| EMAIL   | VARCHAR2(100) | YES  | NULL | NULL    | NULL |
| C_DATE  | DATE          | YES  | NULL | NULL    | NULL |
+---------+---------------+------+-----+---------+-------+
7 rows in set

Modify the length of the VARCHAR2 type from (100) to (128) for the name column.

obclient> ALTER TABLE dws_ny MODIFY name VARCHAR2(128);
Query OK, 0 rows affected

The field length of the name column changes from VARCHAR2(18) to VARCHAR2(128).

obclient> DESC dws_ny;
+---------+---------------+------+-----+---------+-------+
| FIELD   | TYPE          | NULL | KEY | DEFAULT | EXTRA |
+---------+---------------+------+-----+---------+-------+
| ID      | NUMBER        | NO   | PRI | NULL    | NULL  |
| NAME    | VARCHAR2(128) | YES  | NULL | NULL    | NULL  |
| SEX     | CHAR(1)       | YES  | NULL | NULL    | NULL  |
| AGE     | NUMBER        | YES  | NULL | NULL    | NULL  |
| ADDRESS | VARCHAR2(200) | YES  | NULL | NULL    | NULL  |
| EMAIL   | VARCHAR2(100) | YES  | NULL | NULL    | NULL  |
| C_DATE  | DATE          | YES  | NULL | NULL    | NULL  |
+---------+---------------+------+-----+---------+-------+
7 rows in set

Add a column

Add an Age column to the dws_ny table.

View the dws_ny table.

obclient> DESC dws_ny;
+---------+---------------+------+-----+---------+-------+
| FIELD   | TYPE          | NULL | KEY | DEFAULT | EXTRA |
+---------+---------------+------+-----+---------+-------+
| ID      | NUMBER        | NO   | PRI | NULL    | NULL  |
| NAME    | VARCHAR2(128) | YES  | NULL | NULL    | NULL  |
| SEX     | CHAR(1)       | YES  | NULL | NULL    | NULL  |
| AGE     | NUMBER        | YES  | NULL | NULL    | NULL  |
| ADDRESS | VARCHAR2(200) | YES  | NULL | NULL    | NULL  |
| EMAIL   | VARCHAR2(100) | YES  | NULL | NULL    | NULL  |
| C_DATE  | DATE          | YES  | NULL | NULL    | NULL  |
+---------+---------------+------+-----+---------+-------+
7 rows in set

Add a City column.

obclient> ALTER TABLE dws_ny ADD City NUMBER;
Query OK, 0 rows affected

The added City column appears in the dws_ny table.

obclient> DESC dws_ny;
+---------+---------------+------+-----+---------+-------+
| FIELD   | TYPE          | NULL | KEY | DEFAULT | EXTRA |
+---------+---------------+------+-----+---------+-------+
| ID      | NUMBER        | NO   | PRI | NULL    | NULL  |
| NAME    | VARCHAR2(128) | YES  | NULL | NULL    | NULL  |
| SEX     | CHAR(1)       | YES  | NULL | NULL    | NULL  |
| AGE     | NUMBER        | YES  | NULL | NULL    | NULL  |
| ADDRESS | VARCHAR2(200) | YES  | NULL | NULL    | NULL  |
| EMAIL   | VARCHAR2(100) | YES  | NULL | NULL    | NULL  |
| C_DATE  | DATE          | YES  | NULL | NULL    | NULL  |
| CITY    | NUMBER        | YES  | NULL | NULL    | NULL  |
+---------+---------------+------+-----+---------+-------+
8 rows in set

Drop a column

Drop the City column from the dws_ny table.

View the dws_ny table.

obclient> DESC dws_ny;
+---------+---------------+------+-----+---------+-------+
| FIELD   | TYPE          | NULL | KEY | DEFAULT | EXTRA |
+---------+---------------+------+-----+---------+-------+
| ID      | NUMBER        | NO   | PRI | NULL    | NULL  |
| NAME    | VARCHAR2(128) | YES  | NULL | NULL    | NULL  |
| SEX     | CHAR(1)       | YES  | NULL | NULL    | NULL  |
| AGE     | NUMBER        | YES  | NULL | NULL    | NULL  |
| ADDRESS | VARCHAR2(200) | YES  | NULL | NULL    | NULL  |
| EMAIL   | VARCHAR2(100) | YES  | NULL | NULL    | NULL  |
| C_DATE  | DATE          | YES  | NULL | NULL    | NULL  |
| CITY    | NUMBER        | YES  | NULL | NULL    | NULL  |
+---------+---------------+------+-----+---------+-------+
8 rows in set

Drop the City column.

obclient> ALTER TABLE dws_ny DROP COLUMN city;
Query OK, 0 rows affected

The City column disappears from the dws_ny table.

obclient> DESC dws_ny;
+---------+---------------+------+-----+---------+-------+
| FIELD   | TYPE          | NULL | KEY | DEFAULT | EXTRA |
+---------+---------------+------+-----+---------+-------+
| ID      | NUMBER        | NO   | PRI | NULL    | NULL  |
| NAME    | VARCHAR2(128) | YES  | NULL | NULL    | NULL  |
| SEX     | CHAR(1)       | YES  | NULL | NULL    | NULL  |
| AGE     | NUMBER        | YES  | NULL | NULL    | NULL  |
| ADDRESS | VARCHAR2(200) | YES  | NULL | NULL    | NULL  |
| EMAIL   | VARCHAR2(100) | YES  | NULL | NULL    | NULL  |
| C_DATE  | DATE          | YES  | NULL | NULL    | NULL  |
+---------+---------------+------+-----+---------+-------+
7 rows in set