Incompatibilities of ALTER TABLE

This topic describes ALTER TABLE DDL operations that are executed in a MySQL database and can be converted by the DDL synchronization component of OceanBase Migration Service (OMS), but they cannot be executed in the MySQL tenant of OceanBase Database when you migrate data from the MySQL database to the MySQL tenant of OceanBase Database.

Reference other columns in a row CHECK expression

• MySQL databases

  • When executing the ALTER TABLE ADD COLUM statement, you cannot reference other columns (of any types) in the row CHECK expression.

  • When executing the ALTER TABLE MODIFY/CHANGE COLUM statement, you can reference other columns (of any types) in the row CHECK expression.

• MySQL tenants of OceanBase Database

  • When executing the ALTER TABLE ADD COLUM statement, you cannot reference other columns (of any types) in the row CHECK expression.

  • When executing the ALTER TABLE MODIFY/CHANGE COLUM statement, you cannot reference other columns (of any types) in the row CHECK expression.

    Warning

    Other CHECK expressions may not be supported due to the limitations of a MySQL tenant of OceanBase Database.

Change the type of a column

• MySQL databases and MySQL tenants of OceanBase Database have different limitations on the target column types for changing the types of constrained columns. For more information, see Change the type of a constrained column.

• MySQL databases and MySQL tenants of OceanBase Database have different limitations on the target column types for changing the types of unconstrained columns. For more information, see Change the type of an unconstrained column.

Change the length of a column

• MySQL databases and MySQL tenants of OceanBase Database have different limitations on changing the lengths of constrained columns. For more information, see Change the length of a constrained column.

• MySQL databases and MySQL tenants of OceanBase Database have different limitations on changing the lengths of unconstrained columns. For more information, see Change the length of an unconstrained column.

Change a NULLABLE column to a NOT NULL column

• MySQL databases

  If a NULLABLE column is not constrained, you can execute the CHANGE/MODIFY COLUMN statement to change the NULLABLE column to a NOT NULL column. The following changes are also supported: NOT NULL -> NULLABLE, NULLABLE -> NULLABLE, and NOT NULL -> NOT NULL.

  Notice

  If a NULL value exists in a NULLABLE column, you cannot change the NULLABLE column to a NOT NULL column.

• MySQL tenants of OceanBase Database

  • If a NULLABLE column is not constrained, you cannot change the NULLABLE column to a NOT NULL column by executing the CHANGE/MODIFY COLUMN statement.

  • If a NULLABLE column is not constrained, the following changes are supported: NOT NULL -> NULLABLE, NULLABLE -> NULLABLE, and NOT NULL -> NOT NULL.

Change the character set or collation

• MySQL databases: supported.

• MySQL tenants of OceanBase Database: not supported.

Delete a constrained column

MySQL databases and MySQL tenants of OceanBase Database have different limitations on deleting a constrained column. For more information, see Delete a constrained column.

CHANGE/MODIFY COLUMN NOT NULL statement and COMMENT statement cannot exist at the same time

Example:

 ALTER TABLE T MODIFY C INT NOT NULL, COMMENT 'INT COLUMN';

• MySQL databases

  • If a column is not constrained, you can change the column to a NOT NULL or NULLABLE column by executing the CHANGE/MODIFY COLUMN statement with a COMMENT clause.

  • If a column has the PRIMARY KEY, UNIQUE KEY, KEY, or FULLTEXT constraint, you can change the column to a NOT NULL column by executing the CHANGE/MODIFY COLUMN statement with a COMMENT clause. However, if you change the column to a NULLABLE column in this case, an error is returned.

• MySQL tenants of OceanBase Database

  • Regardless of whether a NULLABLE or NOT NULL column is constrained or not, you cannot change it to a NOT NULL column by executing the CHANGE/MODIFY COLUMN statement with a COMMENT clause.

  • If a NULLABLE or NOT NULL column is not constrained, you can change it to a NULLABLE column.

  • If a NOT NULL column has a PRIMARY KEY, UNIQUE KEY, KEY, or FULLTEXT constraint, and you want to change the column to a NULLABLE column by executing the CHANGE/MODIFY COLUMN statement with a COMMENT clause, no error is returned but the change does not take effect.