Failed to add the foreign key constraint. Missing index for constraint Error Code: 1822

Question

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 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

Gordon Linoff · Accepted Answer · 2018-09-23 21:46:39Z

5

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)
);

answered Sep 23, 2018 at 21:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Failed to add the foreign key constraint. Missing index for constraint Error Code: 1822

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related