Operations on columns

This topic describes the supported DDL operations on columns in an ALTER TABLE statement during data migration from a MySQL database to a MySQL-compatible 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 DDL 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 DDL operations

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

• Use ORDER BY col_name to sort a column.

• Use RENAME COLUMN to rename a column.

  ALTER TABLE t RENAME COLUMN d TO g;
  

Ignored attributes

• If an ALTER TABLE statement contains the MODIFY COLUMN or CHANGE COLUMN clause, the FIRST | AFTER keyword will be ignored.

Limitations

• Using the MODIFY COLUMN or CHANGE COLUMN statement to modify the column type may fail. This is because some column types are not supported in MySQL-compatible tenants of OceanBase Database.

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

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

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