Incompatibilities of CREATE TABLE

This topic describes CREATE 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.

Define a generated column based on other generated columns

Use other generated columns in the CREATE TABLE DDL statement to define a newly generated column.

• MySQL databases: supported.

• MySQL-compatible tenants of OceanBase Database: not supported.

Example:

CREATE TABLE group_by_t1
(a INT GENERATED ALWAYS AS (1) VIRTUAL,
 b INT GENERATED ALWAYS AS (a) VIRTUAL,
 c INT GENERATED ALWAYS AS (1) VIRTUAL);

Create an index or a constraint

• When you create a constraint, the column types supported in a MySQL database are different from those supported in a MySQL-compatible tenant of OceanBase Database. For more information, see Column types that are supported to create indexes or constraints.

• Limitations on creating a FOREIGN KEY constraint:

  • MySQL-compatible tenants of OceanBase Database: The referenced column in the parent table must have the UNIQUE KEY or PRIMARY KEY constraint when you create a foreign key.

  • MySQL databases: The referenced column in the parent table must have the UNIQUE KEY, PRIMARY KEY, or KEY constraint when you create a foreign key.