MySQL 外键已创建，但其引用不存在

Question

mysql> INSERT INTO ENROLLMENT (ssn, courseNumber, quarter, grade) 
VALUES ('333333333', '124', 'Fall2012', 'C');

mysql> select * from section;
+--------------+------------+------------+------------------+
| CourseNumber | Quarter    | RoomNumber | DayTime          |
+--------------+------------+------------+------------------+
| 100          | Fall2012   | 5          | MW 1:00-2:OOPM   |
| 100          | Fall2013   | 5          | MW 1:00-2:OOPM   |
| 124          | Fall2013   | 5          | TuTh 3:00-4:OOPM |
| 124          | Spring2011 | 36         | Tu 5:30-8:OOPM   |
| 220          | Winter2014 | 450        | MWF 8:30-10:OOAM |
| 220          | Winter2015 | 450        | MWF 8:30-10:AM   |
| 233          | Fall2013   | 210        | Tu 1:00-3:00PM   |
| 233          | Summer013  | 210        | Tu 1:00-3:00PM   |
| 233          | Summer2010 | 200        | MW 1:OO-2:OOPM   |
| 266          | Summer2013 | 300        | Tu 1:00-3:00PM   |
| 266          | Summer2014 | 300        | Tu 5:00-6:00PM   |
| 266          | Winter2011 | 121        | TuTh 7:30-8:OOPM |
| 400          | Spring2010 | 330        | TuTh 2:00-3:00PM |
+--------------+------------+------------+------------------+


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

我是 SQL 初学者。我正在学习基本的 SQL 查询。在部分表中，Fall2012 的类(class)编号 124 不存在。然而，我能够创造它。怎么可能？

Answer 1

这是可能的，因为 CourseNumber 124 确实存在。并且，Fall2012 季度存在。

从你的问题来看，我认为你想要一个复合外键引用:

CONSTRAINT `enrollment_ibfk_3` FOREIGN KEY (CourseNumber, `Quarter`) REFERENCES `section` (CourseNumber, `Quarter`)

您可能需要在 section 上有适当的索引或唯一约束才能使声明起作用。