mysql - InnoDB : create foreign key with explicit index name

Question

我正在尝试同时创建一个带有索引的外键，并明确指定它们的名称。

The manual说:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

index_name represents a foreign key ID. If given, this is ignored if an index for the foreign key is defined explicitly. Otherwise, if InnoDB creates an index for the foreign key, it uses index_name for the index name.

所以我尝试了以下方法:

ALTER TABLE MyTable
ADD CONSTRAINT FK_MyTable_Zone FOREIGN KEY
IDX_MyTable_Zone (zoneId)
REFERENCES Zone (id);

但是SHOW CREATE TABLE显示索引名IDX_MyTable_Zone没有被考虑进去，索引名也使用了外键名:

CREATE TABLE `MyTable` (
  `zoneId` int(10) unsigned NOT NULL,
  KEY `FK_MyTable_Zone` (`zoneId`),
  CONSTRAINT `FK_MyTable_Zone` FOREIGN KEY (`zoneId`) REFERENCES `Zone` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我错过了什么吗？

Answer 1

我从未使用过这种语法。为了控制名称，我总是将索引与外键分开创建。

这应该适合你:

ALTER TABLE MyTable
ADD KEY IDX_MyTable_Zone (zoneId),
ADD CONSTRAINT FK_MyTable_Zone FOREIGN KEY (zoneId) REFERENCES Zone (id);