mysql - Error Code: 1822 when data types are matching, with composite key

Question

Getting an

Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'subject_ibfk_1' in the referenced table 'enrolment'

when attempting to create the subject table. The problem is, the error does not occur on the previous table student. The data types are the same, and the primary keys are defined.

This error occurs for both the enrolment and grade tables.

create table enrolment(
    stud_id char(9) not null,
    subj_code char(8) not null,
    semester tinyint unsigned not null,
    year smallint unsigned not null,
    comment text,

    primary key (stud_id, subj_code, semester, year)
);

create table grade(
    stud_id char(9) not null,
    subj_code char(8) not null,
    semester tinyint unsigned not null,
    year smallint unsigned not null,
    grade tinyint unsigned,

    primary key (stud_id, subj_code, semester, year)
);

create table student(
    stud_id char(9) not null,
    stud_name char(30),
    stud_phone char(12),
    stud_date_of_birth date,
    stud_city char(26),
    stud_address char(30),
    stud_postcode char(4),

    primary key (stud_id),

    foreign key (stud_id)
        references grade(stud_id),
    foreign key (stud_id)
        references enrolment(stud_id)
);

create table subject(
    subj_code char(8) not null,
    subj_title char(40),

    primary key (subj_code),

    foreign key (subj_code)
        references enrolment(subj_code),

    foreign key (subj_code)
        references grade(subj_code)
);

Answer 1

The problem is due to the fact that the foreign key, subj_code, is part of a multi-column primary key (PK) in the referenced table enrolment:

primary key (stud_id, subj_code, semester, year)

where this column (subj_code) is not the leftmost one.

Table student does not have this problem because its foreign key column stud_id is the leftmost column of the PK in the referenced table.

To resolve this you can create a new index for the referened column:

ALTER TABLE enrolment ADD INDEX subj_code_idx (subj_code);

Note: You have to do the same for referenced table grade in the other foreign key.

Demo here