添加外键约束失败.缺少约束索引错误代码:1822

本文介绍了添加外键约束失败.缺少约束索引错误代码:1822的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

因此，我试图创建一个表'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) );