添加MySQL外键时提示'无法找到引用表索引'错误求助

错误信息 #

无法在引用表中找到以引用列为首列的索引，或者表与引用表的列类型不匹配以创建约束。请注意，在使用>=InnoDB-4.1.12创建的表中，ENUM和SET的内部存储类型已更改，旧表中的此类列无法被新表中的此类列引用。

涉及表结构 #

contact_address表 #

CREATE TABLE `contact_address` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `address_type` int(5) DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `postcode` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `area` int(5) DEFAULT NULL,
  `state` int(5) DEFAULT NULL,
  `country` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `fax` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_branch_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int(4) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_c_a_area` (`area`),
  KEY `fk_c_a_state` (`state`),
  KEY `fk_c_a_country` (`country`),
  KEY `fk_c_a_contact_branch_id` (`contact_branch_id`),
  KEY `fk_c_a_created_by` (`created_by`),
  CONSTRAINT `fk_c_a_area` FOREIGN KEY (`area`) REFERENCES `ref_area` (`area_id`),
  CONSTRAINT `fk_c_a_contact_branch_id` FOREIGN KEY (`contact_branch_id`) REFERENCES `contact_branch` (`id`),
  CONSTRAINT `fk_c_a_created_by` FOREIGN KEY (`created_by`) REFERENCES `user` (`id`),
  CONSTRAINT `fk_c_a_state` FOREIGN KEY (`state`) REFERENCES `ref_state` (`state_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ref_countries表 #

CREATE TABLE `ref_countries` (
  `country_code` char(2) NOT NULL,
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(80) NOT NULL,
  `alpha_3` char(3) DEFAULT NULL,
  `calling_code` int(5) NOT NULL,
  `continent_name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`country_code`),
  KEY `country_id` (`country_id`)
) ENGINE=InnoDB AUTO_INCREMENT=253 DEFAULT CHARSET=utf8mb4;

触发错误的SQL语句 #

ALTER TABLE `contact_address`
  ADD CONSTRAINT `fk_c_a_countries` FOREIGN KEY (`country`) 
  REFERENCES `ref_countries` (`country_code`);

问题原因 #

InnoDB创建外键要求关联列的数据类型、长度、排序规则、NULL属性必须完全一致，这里的核心问题有两个：

1. NULL属性不匹配：contact_address.country允许为NULL，而ref_countries.country_code是NOT NULL约束
2. 排序规则可能不匹配：contact_address.country显式指定了utf8mb4_unicode_ci，但ref_countries.country_code使用表默认排序规则（若表默认不是utf8mb4_unicode_ci，就会导致不匹配）

解决办法 #

方案一：修改contact_address.country列属性与引用列完全匹配（推荐） #

先调整country列的NULL约束和排序规则，使其与country_code一致：

ALTER TABLE `contact_address` 
MODIFY COLUMN `country` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

（如果ref_countries.country_code的排序规则不是utf8mb4_unicode_ci，需将上述语句中的排序规则替换为对应值）

修改完成后，重新执行添加外键的语句即可。

方案二：修改ref_countries.country_code允许NULL（不推荐） #

若业务上需要保留contact_address.country的NULL属性，可修改引用表的列约束：

ALTER TABLE `ref_countries` 
MODIFY COLUMN `country_code` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;

调整后再执行添加外键的语句。

额外检查 #

确认ref_countries.country_code是主键（已满足），主键本身就是索引，符合外键对引用列需为索引首列的要求。

内容的提问来源于stack exchange，提问作者yai