Operations on constraints and indexes - ApsaraDB for OceanBase

This topic describes the supported conversion scope of ALTER TABLE DDL operations on constraints and indexes during data migration from a MySQL database to a MySQL tenant of OceanBase Database.

Syntax

alter_option: {
   ADD {INDEX | KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
        [index_name] [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (col_name,...)
        reference_definition
  | ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
  | DROP {CHECK | CONSTRAINT} symbol
  | ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
  | ALTER INDEX index_name {VISIBLE | INVISIBLE}
  | {DISABLE | ENABLE} KEYS
  | DROP {INDEX | KEY} index_name
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol
  | RENAME {INDEX | KEY} old_index_name TO new_index_name
}

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

Supported operations

Use ADD INDEX | KEY to create a normal index.
Use DROP INDEX to drop an index.
Use ADD FULLTEXT INDEX | KEY to create a full-text index.
Use ADD UNIQUE INDEX | KEY to create a unique index.
Use ADD FOREIGN KEY to create a foreign key.
Use DROP PRIMARY KEY to drop a PRIMARY KEY constraint.
Important
Only OceanBase Database V4.0.0 and later versions support this operation.

Unsupported operations

Important

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 function-based indexes on UNIQUE KEY, INDEX/KEY, and FULLTEXT INDEX columns. You can create these indexes only on fields.
Use ADD SPATIAL INDEX.
Use ADD PRIMARY KEY.
Use ALTER TABLE DISABLE | ENABLE KEYS.
Specify the ON [DELETE | UPDATE] SET NULL keywords for FOREIGN KEY columns.
In MySQL tenants of OceanBase Database of a version earlier than V3.2.3, you cannot perform the following operations:
- Use ADD CHECK.
- Use DROP CHECK.
- Use ALTER CHECK [NOT] ENFORCED.
- Use ALTER INDEX VISIBLE | INVISIBLE.
- Use RENAME INDEX | KEY.
```
ALTER TABLE t rename KEY k TO kk;
ALTER TABLE T RENAME INDEX b TO w;
```

Ignored clauses and options

Important

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.