ALTER TABLE

描述

该语句用来修改已存在的表的结构，包括修改表及表属性、新增列、修改列及属性、删除列等。

语法

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)}
    | ADD [CONSTRAINT [constraint_name]] UNIQUE (column_name [, column_name ]...)
    | ADD [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name_list) references_clause
    | ADD [CONSTRAINT [constraint_name]] CHECK (expr)
    | ADD CONSTRAINT constraint_name PRIMARY KEY (column_name)
    | ADD CONSTRAINT constraint_name FOREIGN KEY(foreign_col_name) REFERENCES              
        reference_tbl_name(column_name);
    | ALTER INDEX index_name [VISIBLE | INVISIBLE]

    | ADD range_partition_list
    | DROP {PARTITION | SUBPARTITION} partition_name_list [UPDATE GLOBAL INDEXES]
    | DROP TABLEGROUP
    | DROP CONSTRAINT constraint_name
    | DROP PRIMARY KEY
    | DROP COLUMN column_name
    | {ENABLE | DISABLE} CONSTRAINT constraint_name
    | MODIFY [COLUMN] column_definition
    | MODIFY CONSTRAINT constraint_name { ENABLE | DISABLE }
    | MODIFY PRIMARY KEY (column_name_list)
    | RENAME [TO] table_name
    | RENAME COLUMN old_col_name TO new_col_name
    | SET TABLEGROUP tablegroup_name
    | [SET] table_option_list
    | TRUNCATE {PARTITION | SUBPARTITION} partition_name_list [UPDATE GLOBAL INDEXES]


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
    [GENERATED BY DEFAULT AS IDENTITY | GENERATED ALWAYS AS IDENTITY]
    [DEFAULT const_value]
    [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment


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_option:
      [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name_list)
    | comment

table_option_list:
    table_option [ table_option ...]

table_option:
      table_group
    | block_size
    | compression
    | comment
    | 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)

/*模板化二级分区*/
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)

/*非模板化二级分区*/
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/*仅非模板化二级分区可定义*/]
    
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/*仅非模板化二级分区可定义*/] 
    [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/*仅非模板化二级分区可定义*/]
    [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

语法说明

示例

• 修改表 tbl1 中字段 col1 的字段类型。

  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
  

• 修改表 tbl1 中列 col1 的名称为 col2。

  obclient> ALTER TABLE tbl1 RENAME COLUMN col1 TO col2;
  Query OK, 0 rows affected
  
  obclient> DESCRIBE tbl1;
  +-------+-------------+------+-----+---------+-------+
  | FIELD | TYPE        | NULL | KEY | DEFAULT | EXTRA |
  +-------+-------------+------+-----+---------+-------+
  | COL2  | VARCHAR2(10) | YES  | NULL | NULL    | NULL  |
  +-------+-------------+------+-----+---------+-------+
  1 row in set 
  

• 增加和删除列。

  • 创建表 tbl2。

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

  • 对表 tbl2 增加 col3 列。

    obclient> ALTER TABLE tbl2 ADD col3 NUMBER(30);
    Query OK, 0 rows affected
    
    obclient> DESCRIBE tbl2;
    +-------+--------------+------+-----+---------+-------+
    | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
    +-------+--------------+------+-----+---------+-------+
    | COL1  | NUMBER(30)   | NO   | PRI | NULL    | NULL  |
    | COL2  | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
    | COL3  | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
    +-------+--------------+------+-----+---------+-------+
    3 rows in set
    

  • 删除表 tbl2 的 col3 列。

    obclient> ALTER TABLE tbl2 DROP COLUMN col3;
    Query OK, 0 rows affected
    
    obclient> DESCRIBE tbl2;
    +-------+--------------+------+-----+---------+-------+
    | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
    +-------+--------------+------+-----+---------+-------+
    | COL1  | NUMBER(30)   | NO   | PRI | NULL    | NULL  |
    | COL2  | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
    +-------+--------------+------+-----+---------+-------+
    2 rows in set
    

  • 为表 tbl2 创建唯一性索引。

    obclient> CREATE TABLE tbl2 (col1 NUMBER(30) PRIMARY KEY,col2 VARCHAR(50), col3 INT);
    Query OK, 0 rows affected
    
    obclient> ALTER TABLE tbl2 ADD CONSTRAINT constraint_TBL2 UNIQUE (col2, col3);
    Query OK, 0 rows affected 
    
    obclient [SYS]> DESC tbl2;
    +-------+--------------+------+-----+---------+-------+
    | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
    +-------+--------------+------+-----+---------+-------+
    | COL1  | NUMBER(30)   | NO   | PRI | NULL    | NULL  |
    | COL2  | VARCHAR2(50) | YES  | MUL | NULL    | NULL  |
    | COL3  | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
    +-------+--------------+------+-----+---------+-------+
    3 rows in set
    
    obclient> INSERT INTO tbl2 VALUES('1','2','2');
    Query OK, 1 row affected 
    
    obclient> INSERT INTO tbl2 VALUES('2','2','2');
    ORA-00001: unique constraint '2-2' for key 'CONSTRAINT_TBL2' violated
    
    obclient> INSERT INTO tbl2 VALUES('2','3','2');
    Query OK, 1 row affected 
    

• 为非模板化二级分区表 tbl3 的一级分区 p1 下添加二级分区 p1_r4。

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

• 删除非模板化二级分区表 tbl3 的二级分区 p3_r3。

  obclient> ALTER TABLE tbl3 DROP SUBPARTITION p2_r3;
  Query OK, 0 rows affected
  

• 为非模板化二级分区表 tbl3 添加一级分区 p4，需要同时指定一级分区的定义和该分区下的二级分区定义。

  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
  

• 为模板化二级分区表 tbl4 添加一级分区 p3，只需要指定一级分区的定义，二级分区的定义会自动按照模板填充。

  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
  

• 修改表 tbl5 的并行度为 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
  

• 修改外键约束的状态。

  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
  

• 清空分区表 tbl6 的分区 M202001 和 M202002 中的全部数据。

  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
  

• 删除表 tbl7 的 CHECK 约束 tbl7_equal_check1。

  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
  

• 把表 tbl8 从表组 tblgroup1 中移到表组 tblgroup2 中。

  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
  

• 为表 primary_table 添加外键约束 cons_fk1。

  obclient> CREATE TABLE primary_table (id NUMBER PRIMARY KEY, names VARCHAR(100) NOT NULL, foreign_col NUMBER);
  Query OK, 0 rows affected
  obclient> CREATE TABLE reference_table (id NUMBER PRIMARY key, comments VARCHAR2(100) NOT NULL);
  Query OK, 0 rows affected
  obclient> ALTER TABLE primary_table ADD CONSTRAINT cons_fk1 FOREIGN KEY(foreign_col) REFERENCES reference_table(id);
  Query OK, 0 rows affected
  

• 为表 tbl9 添加主键约束 tbl1_pk。

  obclient> CREATE TABLE tbl9 (col1 NUMBER, col2 INT,col3 VARCHAR2(100));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl9 ADD CONSTRAINT tbl1_pk PRIMARY KEY (col1);
  Query OK, 0 rows affected
  

• 修改表 tbl9 的主键为 col2 列。

  obclient> ALTER TABLE tbl9 MODIFY PRIMARY KEY(col2);
  Query OK, 0 rows affected
  

• 删除表 tbl9 的主键。

  obclient> ALTER TABLE tbl9 DROP PRIMARY KEY;
  Query OK, 0 rows affected