mysql - 为什么我不能在 MySQL 中创建这个表？错误 1005 (HY000)

Question

错误信息:

ERROR 1005 (HY000) at line __: Can't create table 'doanv.ENROLLED' (errno: 150) Bye

表STUDENT、COURSE、PROF 已成功创建。无法创建表 ENROLLED 和 TEACHING。我做错了什么？

CREATE TABLE STUDENT
(sID INTEGER PRIMARY KEY NOT NULL,
sFName VARCHAR(20),
sLName VARCHAR(20));

CREATE TABLE COURSE
(cID VARCHAR(20) PRIMARY KEY NOT NULL,
cName VARCHAR(20),
cr INTEGER);

CREATE TABLE PROF
(pID INTEGER PRIMARY KEY NOT NULL,
pFName VARCHAR(20),
pLName VARCHAR(20));

CREATE TABLE ENROLLED
(sID INTEGER NOT NULL,
cID VARCHAR(20) NOT NULL,
PRIMARY KEY (sID, cID),
FOREIGN KEY (sID) REFERENCES STUDENT,
FOREIGN KEY (cID) REFERENCES COURSE);

CREATE TABLE TEACHING
(pID INTEGER NOT NULL,
cID VARCHAR(20) NOT NULL,
PRIMARY KEY (pID, cID),
FOREIGN KEY (pID) REFERENCES PROF,
FOREIGN KEY (cID) REFERENCES COURSE);

Answer 1

在 MySQL 中，您需要指定所引用的列。在 SQL 的其他一些实现中，您可以省略 (sID) 和 (cID)。

CREATE TABLE ENROLLED
(sID INTEGER NOT NULL,
cID VARCHAR(20) NOT NULL,
PRIMARY KEY (sID, cID),
FOREIGN KEY (sID) REFERENCES STUDENT(sID),
FOREIGN KEY (cID) REFERENCES COURSE(cID));