因此,我试图创建一个表“ Prerequisite_to”,该表基本上是一种关系,该关系显示哪些类被视为特定类的先决条件。这是我的SQL表缺陷:
CREATE TABLE Class(
infs CHAR(4) NOT NULL,
course_number CHAR(3) NOT NULL,
PRIMARY KEY (infs,course_number));
CREATE TABLE Prerequisite_to(
infs CHAR(4),
course_number CHAR(3),
PRIMARY KEY (infs,course_number),
FOREIGN KEY (infs) REFERENCES Class(infs),
FOREIGN KEY (course_number) REFERENCES Class(course_number)
)
但是,当我执行脚本时,出现此错误:
17:29:43创建表Prerequisite_to(infs CHAR(4),course_number CHAR(3),PRIMARY KEY(infs,course_number),FOREIGN KEY(infs)参考类(infs),FOREIGN KEY(course_number)参考类(course_number) )错误代码:1822。无法添加外键约束。引用表“类”中的约束“ prerequisite_to_ibfk_2”的缺少索引0.00038秒
最佳答案
您有一个复合主键,因此需要一个复合外键:
CREATE TABLE Prerequisite_to(
infs CHAR(4),
course_number CHAR(3),
PRIMARY KEY (infs,course_number),
FOREIGN KEY (infs, course_number) REFERENCES Class(infs, course_number)
);
仅作记录,我不喜欢复合主键。我还认为先决条件需要两个课程参考。所以:
CREATE TABLE Classes (
class_id int auto_increment primary key,
infs CHAR(4) NOT NULL,
course_number CHAR(3) NOT NULL,
unique (infs, course_number)
);
CREATE TABLE Prerequisites (
preresequisite_id int auto_increment primary key,
class_id int,
prerequisite_class_id int,
FOREIGN KEY (class_id) REFERENCES Classes(class_id),
FOREIGN KEY (prerequisite_class_id) REFERENCES Classes(class_id)
);