mysql - 作为主键给出的一组属性的外键

Question

我有一个Class主键为 Section 的表, Semester和Department 。另Student表 USN作为主键。首先为什么它允许我引用Class.Semester来自Student.SEM ？难道我不应该只允许引用 Class 中的主键吗？来自 Student 中作为外键的一组属性？我还尝试引用Class.section来自Student.Class我收到一条错误消息 #1215 - Cannot add foreign key constraint .

CREATE TABLE `Class` (
  `Semester` int(1) NOT NULL,
  `Section` varchar(1) NOT NULL,
  `Department` varchar(3) NOT NULL,
  `CTID` varchar(10) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `Class`
  ADD PRIMARY KEY (`Semester`,`Section`,`Department`);

CREATE TABLE `student` (
  `USN` varchar(10) NOT NULL,
  `DOB` date DEFAULT NULL,
  `Class` varchar(1) NOT NULL,
  `SEM` int(1) NOT NULL,
  `Dep` varchar(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

ALTER TABLE `student`
  ADD PRIMARY KEY (`USN`),
  ADD KEY `Class` (`Class`,`SEM`,`Dep`),
  ADD KEY `SEM` (`SEM`);

ALTER TABLE `student`
  ADD CONSTRAINT `student_ibfk_1` FOREIGN KEY (`SEM`) REFERENCES `Class` (`Semester`) ON DELETE CASCADE ON UPDATE CASCADE;

Answer 1

MySQL 支持可用键中较少数量的列，只要列列表以键中的列列表开头，如 13.1.17.6 Using FOREIGN KEY Constraints 中所述。 :

• MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.
• InnoDB permits a foreign key to reference any column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

因此，在您的情况下，您可以使用列Semester，因为它是索引(Semester，Section，Department)的开始，但您不能使用Section 或部门。