添加外键约束失败.缺少约束索引错误代码:1822 [英] Failed to add the foreign key constraint. Missing index for constraint Error Code: 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)
);

这篇关于添加外键约束失败.缺少约束索引错误代码:1822的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！