Operations on constraints and indexes

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-compatible 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.

  Notice

  Only OceanBase Database V4.0.0 and later versions support this operation.

Unsupported operations

• 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-compatible 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

• 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

Limitations

• MySQL-compatible 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-compatible tenants of OceanBase Database do not support specifying certain functions or expressions in CHECK constraints.

• MySQL-compatible tenants of OceanBase Database do not support specifying certain functions or expressions in FOREIGN KEY constraints.