ApsaraDB for OceanBase:Incompatibilities of ALTER TABLE

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 type) in the row CHECK expression.

  • When executing the ALTER TABLE MODIFY/CHANGE COLUM statement, you can reference other columns (of any type) 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 type) in the row CHECK expression.

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

    Important

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

Change the type of a column

• MySQL databases and MySQL tenants of OceanBase Database have different limits on the types of constrained columns after change. For more information, see Change the type of a constrained column.

• MySQL databases and MySQL tenants of OceanBase Database have different limits on 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 limits on the length of constrained columns after change. For more information, see Change the length of a constrained column.

• MySQL databases and MySQL tenants of OceanBase Database have different limits on the length of unconstrained columns after change. 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.

  Important

  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 limits 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

Sample code:

 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.