MySQL 外键错误 1215，即使两列类型相同且不为 NULL

Question

父表team_entrant:

CREATE TABLE IF NOT EXISTS `team_entrant` (
  `entrant_number` tinyint(4) NOT NULL,
  `qualifying_position` tinyint(4) NOT NULL,
  `qualifying_time` time(3) NOT NULL,
  `grid_position` tinyint(4) DEFAULT NULL,
  `best_race_time` datetime DEFAULT NULL,
  `final_position` tinyint(4) DEFAULT NULL,
  `dnf_reason` varchar(45) DEFAULT NULL,
  `team_id` int(11) NOT NULL,
  `competition_year` date NOT NULL,
  PRIMARY KEY (`entrant_number`),
  KEY `fk_team_entrant_team1_idx` (`team_id`),
  CONSTRAINT `fk_team_entrant_team1` FOREIGN KEY (`team_id`) REFERENCES `team` (`team_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

子表/关联表entrant_drivers:

CREATE TABLE IF NOT EXISTS `entrant_drivers` (
  `entrant_number` tinyint(4) NOT NULL,
  `competition_year` date NOT NULL,
  `driver_id` int(11) NOT NULL,
  PRIMARY KEY (`entrant_number`,`competition_year`,`driver_id`),
  CONSTRAINT `ed_entrant_fk` FOREIGN KEY (`entrant_number`) REFERENCES `team_entrant` (`entrant_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

当时 team_entrant 列 competition_year 并不存在。

HeidiSQL 拒绝执行以下代码:

ALTER table entrant_drivers ADD CONSTRAINT ed_comp_year_fk FOREIGN KEY (competition_year) REFERENCES team_entrant(competition_year);

SQL 错误 (1215):无法添加外键约束

外部表，涉及关联表的驱动程序:

-- Dumping structure for table 99_lemans_db1.driver
CREATE TABLE IF NOT EXISTS `driver` (
  `driver_id` int(11) NOT NULL,
  `driver_name` varchar(64) NOT NULL,
  `driver_nationality` varchar(32) NOT NULL,
  `driver_birth_day` date NOT NULL,
  `driver_best_previous_finish_class` varchar(8) DEFAULT NULL,
  `driver_best_previous_finish_position` tinyint(4) DEFAULT NULL,
  `team_entrant_id` int(11) NOT NULL,
  PRIMARY KEY (`driver_id`,`team_entrant_id`),
  KEY `fk_driver_team_entrant1_idx` (`team_entrant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如有任何帮助，我们将不胜感激。

Answer 1

父列必须指定为索引/主键。

team_entrant 应该由复合主键(参赛者编号、比赛年份)组成。