content_image/812029.jpg content_image/812030.jpg
RENAME INDEX old_index_name  TO new_index_name  renames an index. This is a MySQL extension to standard SQL. The content of the table remains unchanged. old_index_name  must be the name of an existing index in the table that is not dropped by the same ALTER TABLE  statement. new_index_name  is the new index name, which cannot duplicate the name of an index in the resulting table after changes have been applied. Neither index name can be PRIMARY . 

If you use ALTER TABLE  on a MyISAM  table, all nonunique indexes are created in a separate batch (as for REPAIR TABLE ). This should make ALTER TABLE  much faster when you have many indexes. 

For MyISAM  tables, key updating can be controlled explicitly. Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. Then use ALTER TABLE ... ENABLE KEYS  to re-create missing indexes. MyISAM  does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS  requires the INDEX  privilege in addition to the privileges mentioned earlier. 

While the nonunique indexes are disabled, they are ignored for statements such as SELECT  and EXPLAIN that otherwise would use them. 

After an ALTER TABLE  statement, it may be necessary to run ANALYZE TABLE  to update index cardinality information. See Section 13.7.7.22, “SHOW INDEX Statement” . 

The ALTER INDEX  operation permits an index to be made visible or invisible. An invisible index is not used by the optimizer. Modification of index visibility applies to indexes other than primary keys (either explicit or implicit). This feature is storage engine neutral (supported for any engine). For more information, see Section 8.3.12, “Invisible Indexes” . 

## Foreign Keys and Other Constraints 

The FOREIGN KEY  and REFERENCES  clauses are supported by the InnoDB  and NDB  storage engines, which implement ADD [CONSTRAINT [ symbol ]] FOREIGN KEY [ index_name ] (...) REFERENCES ... (...) . See Section 13.1.20.5, “FOREIGN KEY Constraints” . For other storage engines, the clauses are parsed but ignored. 

For ALTER TABLE , unlike CREATE TABLE , ADD FOREIGN KEY  ignores index_name  if given and uses an automatically generated foreign key name. As a workaround, include the CONSTRAINT  clause to specify the foreign key name: 

ADD CONSTRAINT name  FOREIGN KEY (....) ... 

<img src='content_image/812029.jpg'>

<img src='content_image/812030.jpg'>

## Important 

MySQL silently ignores inline REFERENCES  specifications, where the references are defined as part of the column specification. MySQL accepts only REFERENCES clauses defined as part of a separate FOREIGN KEY  specification. 

## Note 

Partitioned InnoDB  tables do not support foreign keys. This restriction does not apply to NDB  tables, including those explicitly partitioned by [LINEAR] KEY . For more information, see Section 24.6.2, “Partitioning Limitations Relating to Storage Engines” . 

MySQL Server and NDB Cluster both support the use of ALTER TABLE  to drop foreign keys: 

ALTER TABLE tbl_name  DROP FOREIGN KEY fk_symbol ; 

