mysql - 即使数据类型相同也无法添加外键约束

Question

| section | CREATE TABLE `section` (
  `CourseNumber` varchar(10) NOT NULL,
  `Quarter` varchar(10) NOT NULL,
  `RoomNumber` varchar(4) DEFAULT NULL,
  `DayTime` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`CourseNumber`,`Quarter`),
  CONSTRAINT `section_ibfk_1` FOREIGN KEY (`CourseNumber`) REFERENCES `Course` (`CourseNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


| enrollment | CREATE TABLE `enrollment` (
  `SSN` varchar(9) DEFAULT NULL,
  `CourseNumber` varchar(4) DEFAULT NULL,
  `Quarter` varchar(10) DEFAULT NULL,
  `Grade` varchar(1) DEFAULT NULL,
  KEY `SSN` (`SSN`),
  KEY `CourseNumber` (`CourseNumber`),
  CONSTRAINT `enrollment_ibfk_1` FOREIGN KEY (`SSN`) REFERENCES `Student` (`SSN`),
  CONSTRAINT `enrollment_ibfk_2` FOREIGN KEY (`CourseNumber`) REFERENCES `Course` (`CourseNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |



mysql> alter table enrollment add foreign key(Quarter) references section(Quarter);
ERROR 1215 (HY000): Cannot add foreign key constraint

表 Enrollment 和 Section 中的列 Quarter 的数据类型为 VARCHAR(10)，因此我不确定导致错误的原因。

Answer 1

如果表中的数据不尊重外键，则无法创建它。尝试一下

SET foreign_key_checks = 0;
alter table enrollment add foreign key(Quarter) references section(Quarter);
SET foreign_key_checks = 1;

但是我用它来删除表，不知道这种情况会发生什么。