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 mode 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
Due to the limitations of OceanBase Database's MySQL compatible mode, there may be other unsupported CHECK expressions.
Change the type of a column
For MySQL database and OceanBase database in the MySQL compatible mode, the restrictions on the data type of the modified column with constraints are different. For more information, see Modify Column Type with Constraints.
For MySQL database and OceanBase database's MySQL compatible mode, the restrictions on the data type of the modified column differ when there are no constraints. For more information, see Modify Column Type Without Constraints.
Change the length of a column
For MySQL database and OceanBase database in the MySQL compatible mode, the restrictions on modifying column lengths differ when the modified column has constraints. For more details, please refer to Modifying Column Length with Constraints.
For MySQL databases and OceanBase databases in the MySQL compatible mode, the restrictions on modifying column lengths differ when the modified column has no constraints. For details, see Modifying Column Length Without Constraints.
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.