因此,我试图创建一个表'Prerequisite_to',该表基本上是一种关系,该关系显示哪些类被视为特定类的先决条件.这是我的SQL表缺陷:
So I'm trying to create a table 'Prerequisite_to', which is basically a relation that shows what classes are considered as a prerequisite for a specific class. Here are my SQL tables defections:
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) )但是,当我执行脚本时,出现此错误:
However, when I execute the script I get this error:
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秒
17:29:43 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) ) Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'prerequisite_to_ibfk_2' in the referenced table 'Class' 0.00038 sec
推荐答案
您有一个复合主键,因此需要一个复合外键:
You have a composite primary key, so you need a composite foreign key:
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) );仅作记录,我不喜欢复合主键.我还认为先决条件需要两个课程参考.所以:
Just for the record, I'm not a fan of composite primary keys. I also think prerequisites need two course references. So:
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) );更多推荐
添加外键约束失败.缺少约束索引错误代码:1822
发布评论