Operations on columns

This topic describes the supported conversion scope of ALTER TABLE DDL operations on columns during data migration from a MySQL database to a MySQL tenant of OceanBase Database.

Syntax

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]

alter_option: {
  ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  | ALTER [COLUMN] col_name {
        SET DEFAULT {literal | (expr)}
      | SET {VISIBLE | INVISIBLE}
      | DROP DEFAULT
    }
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ORDER BY col_name [, col_name] ...
  | RENAME COLUMN old_col_name TO new_col_name
}

Supported operations

• Use ADD COLUMN to add one or more columns. For more information about how to define a column, see Create a column. You can also specify the FIRST | AFTER keyword.

• Use ALTER COLUMN SET DEFAULT to modify the default value of a column.

• Use ALTER COLUMN DROP DEFAULT to drop the default value of a column.

• Use CHANGE COLUMN or MODIFY COLUMN to change or modify a column.

• Use DROP COLUMN to drop a column.

Unsupported operations

• Use ALTER COLUMN SET VISIBLE | INVISIBLE to modify the visibility of a column.

• Use ORDER BY col_name.

• Use RENAME COLUMN to rename a column.

  ALTER TABLE t RENAME COLUMN d TO g;
  

Ignored clauses and options

• FIRST | AFTER attribute (If an ALTER TABLE DDL statement contains the MODIFY COLUMN or CHANGE COLUMN clause, this attribute will be ignored.)

Limitations

• Using the MODIFY COLUMN or CHANGE COLUMN clause to modify the field type may fail. This is because certain field types are not supported in MySQL tenants of OceanBase Database.

• Using the MODIFY COLUMN or CHANGE COLUMN statement to modify the field length may fail.

• Using the ALTER COLUMN SET DEFAULT statement to set a default value may fail. This is because certain functions or expressions are not supported in MySQL tenants of OceanBase Database.

• You cannot drop columns that are used as primary keys, unique keys, or normal indexes, or columns with FOREIGN KEY constraints.