Global secondary indexes (GSIs) have specific constraints on how you can create them, modify the tables that contain them, and run DDL and DML operations. Understanding these constraints upfront helps you avoid schema design errors and unexpected failures at runtime.
GSI creation constraints
-
GSIs are not supported on ApsaraDB RDS for MySQL 5.6.
-
GSIs cannot be created on single tables or broadcast tables.
-
GSIs cannot be created on tables that have no primary keys.
-
Prefix indexes cannot be used in a UNIQUE GSI.
-
Specify an index name when creating an index table.
-
When creating an index table, specify database sharding rules only, or both database sharding and table sharding rules. Specifying only table sharding rules, or no sharding rules at all, is not allowed.
-
The INDEX column of an index table must include all shard keys.
-
A GSI name cannot duplicate the name of any local index in the primary table.
-
Index columns and covering columns must be distinct in a GSI definition.
-
By default, an index table includes all primary keys and shard keys from the primary table. Any primary keys or shard keys not explicitly listed as index columns are automatically added to the covering columns.
-
For each local index in the primary table, if all the columns it references exist in the index table, that local index is automatically added to the index table.
-
By default, a separate index is created for each index column in a GSI when no existing index covers it.
-
For a GSI with multiple index columns, a composite local index covering all of those columns is created by default.
-
The length parameter on an index column applies only when creating local indexes on the shard keys of the index table.
-
When you create a GSI on an existing table, PolarDB automatically verifies data consistency at the end of the creation process. The DDL statement succeeds only after the data passes verification.
To verify or modify GSI data manually, use the CHECK GLOBAL INDEX statement.
ALTER TABLE constraints
Column operations
The following table shows which ALTER TABLE column operations are supported on tables that have GSIs.
| Statement | Shard keys of the primary table | Primary keys of the primary table (also the primary keys of the index table) | Local unique index column | Shard keys of the index table | Unique index column | Index column | Covering column |
|---|---|---|---|---|---|---|---|
| ADD COLUMN | No such scenario | Not supported | No such scenario | No such scenario | No such scenario | No such scenario | No such scenario |
| ALTER COLUMN SET DEFAULT / ALTER COLUMN DROP DEFAULT | Not supported | Not supported | Supported | Not supported | Not supported | Not supported | Not supported |
| CHANGE COLUMN | Not supported | Not supported | Supported | Not supported | Not supported | Not supported | Not supported |
| DROP COLUMN | Not supported | Not supported | Supported (only when the unique key has exactly one column) | Not supported | Not supported | Not supported | Not supported |
| MODIFY COLUMN | Not supported | Not supported | Supported | Not supported | Not supported | Not supported | Not supported |
Column classifications can overlap. For example, the index column includes the shard keys of the index table, and the covering column includes the shard keys of the primary table, primary keys, and explicitly specified columns. When a column falls into both a "Not supported" and a "Supported" category, "Not supported" takes precedence.
To remove columns from a GSI, use the DROP INDEX statement to drop the GSI and recreate it without the unwanted columns. Dropping columns directly with DROP COLUMN is not supported, to protect GSI stability and performance.
Index operations
The following table shows which ALTER TABLE index operations are supported.
| Statement | Supported |
|---|---|
| ALTER TABLE ADD PRIMARY KEY | Yes |
| ALTER TABLE ADD [UNIQUE | FULLTEXT | SPATIAL | FOREIGN] KEY | Yes. Local indexes can be added to the primary table and the index table at the same time. The index name must differ from the GSI name. |
| ALTER TABLE ALTER INDEX *index_name* {VISIBLE | INVISIBLE} | Yes, on the primary table only. The GSI visibility status cannot be changed. |
| ALTER TABLE {DISABLE | ENABLE} KEYS | Yes, on the primary table only. The GSI status cannot be changed. |
| ALTER TABLE DROP PRIMARY KEY | No |
| ALTER TABLE DROP INDEX | Supported for common indexes and GSIs only. |
| ALTER TABLE RENAME INDEX | No. To rename a GSI, drop it with DROP INDEX and recreate it with the new name. |
Index table constraints
-
DDL and DML statements cannot be executed on index tables.
-
DML statements that include NODE HINT cannot be used to update primary tables or index tables.
Other DDL statement constraints
| Statement | Supported |
|---|---|
| DROP TABLE | Yes |
| DROP INDEX | Yes |
| TRUNCATE TABLE | No. To clear data from both the primary table and its index tables while keeping them consistent, use the DELETE statement instead. |
| RENAME TABLE | No. To rename a table that has a GSI, drop the GSI with DROP INDEX, rename the table, and then recreate the GSI. |
| ALTER TABLE RENAME | No. Use the same workaround as RENAME TABLE above. |
DML statement constraints
DML statements cannot be executed on index tables. For constraints on DML operations against primary tables, see Limits of global secondary indexes on DML.