Create an index or a constraint - ApsaraDB for OceanBase

This topic describes the supported conversion scope of CREATE TABLE DDL operations for creating indexes or constraints during data migration from a MySQL database to a MySQL tenant 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 primary keys.
Create unique keys.
Create indexes or keys.
Create foreign keys.
Create foreign keys on fields, functions, or expressions. Specify the ON [DELETE | UPDATE] RESTRICT | CASCADE | NO ACTION | SET DEFAULT options.
Create full-text indexes.
Create CHECK constraints in MySQL tenants of OceanBase Database V3.2.3 and later.
Create prefixed indexes.
Specify the ASC or DESC keyword.

Unsupported operations

Important

If an ALTER TABLE DDL statement to be converted contains the following unsupported definitions, the conversion will fail and the output will be an empty string.

Create the following types of indexes with functional key parts: PRIMARY KEY, UNIQUE KEY, INDEX/KEY, and FULLTEXT INDEX. (You can create these indexes only on fields.)
Primary key, unique key, index, key, or full-text index definitions that contain functions or expressions. Examples:
```
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 spatial indexes.
Specify the ON [DELETE | UPDATE] SET NULL option for foreign keys.

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 index_option
[NOT] ENFORCED for CHECK constraints
MATCH FULL | MATCH PARTIAL | MATCH SIMPLE for foreign keys

Limits

Important

Due to the limits of MySQL tenants of OceanBase Database, an error may be returned when the synchronized DDL statements are converted and executed in a MySQL tenant of OceanBase Database in the following scenarios:

MySQL tenants of OceanBase Database cannot create the following types of constraints on specific types of fields: PRIMARY KEY, UNIQUE KEY, INDEX/KEY, FULLTEXT INDEX, and FOREIGN KEY.
MySQL tenants of OceanBase Database do not support specifying certain functions or expressions in CHECK constraints.
MySQL tenants of OceanBase Database do not support specifying certain functions or expressions in FOREIGN KEY constraints.