mysql - 简单表-ERROR 1215:无法添加外键约束，

我有这两个简单的表

CREATE TABLE `location_main_master` (
  `location_main_master_id` bigint(16) unsigned NOT NULL,
  `city_id_test` int(10) unsigned NOT NULL,
  PRIMARY KEY (`location_main_master_id`,`city_id_test`),
  UNIQUE KEY `location_main_master_id_UNIQUE` (`location_main_master_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `location_sub_master` (
  `location_sub_master_id` bigint(16) unsigned NOT NULL,
  `city_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`location_sub_master_id`,`city_id`),
  UNIQUE KEY `location_sub_master_id_UNIQUE` (`location_sub_master_id`),
  KEY `fk_location_sub_master_city_id_idx` (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我正在尝试添加外键

ALTER TABLE `location_sub_master`
ADD CONSTRAINT `fk_location_sub_city_id`
  FOREIGN KEY (`city_id`)
  REFERENCES `location_main_master` (`city_id_test`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

它给我这个错误：


  错误1215：无法添加外键约束

最佳答案

您必须在主表中更改主键的顺序。

主键（city_id_test，location_main_master_id）

所以您的主表应该看起来像

创建表location_main_master（
   location_main_master_id BIGINT（16）UNSIGNED NOT NULL，
   city_id_test INT（10）UNSIGNED NOT NULL，
   主键（city_id_test，location_main_master_id），
   唯一键location_main_master_id_UNIQUE（location_main_master_id）
）ENGINE = INNODB DEFAULT CHARSET = utf8;

关于mysql - 简单表-ERROR 1215:无法添加外键约束，，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44826388/