This topic describes the conversion scope of CREATE TABLE DDL operations for creating indexes or constraints during data migration from a MySQL database to the MySQL compatible mode of OceanBase Database.
Syntax
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
| {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| check_constraint_definition
}
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}
|ENGINE_ATTRIBUTE [=] 'string'
|SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}
check_constraint_definition:
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
reference_definition:
REFERENCES tbl_name (key_part,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
Supported operations
Create a PRIMARY KEY.
Create a UNIQUE KEY.
Create an INDEX or KEY.
Create a FOREIGN KEY.
Create a FOREIGN KEY on fields, functions, or expressions. Specify the ON [DELETE | UPDATE] RESTRICT | CASCADE | NO ACTION | SET DEFAULT attribute.
Create a FULLTEXT INDEX.
Create a CHECK constraint in OceanBase Database of the MySQL compatible mode V3.2.3 or later.
Create prefixed indexes.
Specify the ASC or DESC keyword.
Unsupported operations
Warning
If a DDL statement to be synchronized contains the following unsupported definitions, the conversion will fail and the output will be an empty string.
Create a function-based index for the PRIMARY KEY, UNIQUE KEY, INDEX/KEY, or FULLTEXT INDEX. You can create these indexes only on fields.
The following example shows an index definition that contains a function or expression:
CREATE TABLE functional_index_t1 (col1 INT, PRIMARY KEY (col1, (ABS(col1)))); CREATE TABLE functional_index_t1(x VARCHAR(30), INDEX idx ((CAST(x->>'$.name' AS CHAR(30)))));Create a SPATIAL INDEX.
Specify the ON [DELETE | UPDATE] SET NULL attribute for FOREIGN KEY.
Ignored clauses and options
Note
The following clauses and options will be ignored and will not be resolved or converted when they are specified in the synchronized DDL statements.
USING BTREE or USING HASH
KEY_BLOCK_SIZE, index_type, WITH PARSER, COMMENT, VISIBLE | INVISIBLE, ENGINE_ATTRIBUTE, or SECONDARY_ENGINE_ATTRIBUTE in the index option
[NOT] ENFORCED for CHECK constraints
MATCH FULL | MATCH PARTIAL | MATCH SIMPLE for FOREIGN KEY
Limitations
Notice
Due to the limitations of the MySQL compatible mode of OceanBase Database, an error may be returned when the synchronized DDL statements are converted and executed in the MySQL compatible mode in the following scenarios.
The MySQL compatible mode of OceanBase Database does not support creating the PRIMARY KEY, UNIQUE KEY, INDEX/KEY, FULLTEXT INDEX, or FOREIGN KEY on specific types of fields.
The MySQL compatible mode of OceanBase Database does not support specifying certain functions or expressions in CHECK constraints.
The MySQL compatible mode of OceanBase Database does not support specifying certain functions or expressions in FOREIGN KEY constraints.