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-compatible tenant of OceanBase Database when you migrate data from the MySQL database to the MySQL-compatible tenant of OceanBase Database.
Reference other columns in a row CHECK expression
MySQL databases
When executing the
ALTER TABLE ADD COLUMstatement, you cannot reference other columns (of any types) in the rowCHECKexpression.When executing the
ALTER TABLE MODIFY/CHANGE COLUMstatement, you can reference other columns (of any types) in the rowCHECKexpression.
MySQL-compatible tenants of OceanBase Database
When executing the
ALTER TABLE ADD COLUMstatement, you cannot reference other columns (of any types) in the rowCHECKexpression.When executing the
ALTER TABLE MODIFY/CHANGE COLUMstatement, you cannot reference other columns (of any types) in the rowCHECKexpression.Warning
Other CHECK expressions may not be supported due to the limitations of a MySQL-compatible tenant of OceanBase Database.
Change the type of a column
MySQL databases and MySQL-compatible 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-compatible 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-compatible 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-compatible 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-compatible 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-compatible tenants of OceanBase Database: not supported.
Delete a constrained column
MySQL databases and MySQL-compatible 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-compatible 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.
