[FIXED] Failed to execute SQL script statement - Missing index for constraint 'students_ibfk_1' in the referenced table

Issue

I want to write sql script for integration test in my small service in spring. I am using MySQL.

I have the script below:

drop table if exists students_enrolled_to_courses;
drop table if exists students;
drop table if exists courses;

CREATE TABLE students_enrolled_to_courses
(
    student_id varchar(32),
    course_id  varchar(32)
);
CREATE TABLE students
(
    id              VARCHAR(32),
    name            VARCHAR(50),
    surname         VARCHAR(50),
    date_of_birth   DATE,
    phone_number    VARCHAR(9),
    student_card_ID VARCHAR(6),
    PRIMARY KEY (id)
);
CREATE TABLE courses
(
    id          VARCHAR(32),
    name        VARCHAR(50),
    school_name VARCHAR(100),
    PRIMARY KEY (id)
);
ALTER TABLE students
    ADD FOREIGN KEY (id) REFERENCES students_enrolled_to_courses(student_id);

ALTER TABLE courses
    ADD FOREIGN KEY (id) REFERENCES students_enrolled_to_courses(course_id);

insert into students (id, name, surname, date_of_birth, phone_number, student_card_ID)
    values ('000000000000000000000000000000000001', 'John', 'Smith', '04/04/1997','123456789', '999999');

And when I run test I am getting error:

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #7 of class path resource [students.sql]: ALTER TABLE students ADD FOREIGN KEY (id) REFERENCES students_enrolled_to_courses(student_id); nested exception is java.sql.SQLException: Failed to add the foreign key constraint. Missing index for constraint 'students_ibfk_1' in the referenced table 'students_enrolled_to_courses'

Solution

Change code to this:

CREATE TABLE students_enrolled_to_courses
(
    student_id varchar(32),
    course_id  varchar(32),
    primary key (student_id, course_id)
);
CREATE TABLE students
(
    id              VARCHAR(32),
    name            VARCHAR(50),
    surname         VARCHAR(50),
    date_of_birth   DATE,
    phone_number    VARCHAR(9),
    student_card_ID VARCHAR(6),
    PRIMARY KEY (id)
);
CREATE TABLE courses
(
    id          VARCHAR(32),
    name        VARCHAR(50),
    school_name VARCHAR(100),
    PRIMARY KEY (id)
);
ALTER TABLE students_enrolled_to_courses
    ADD FOREIGN KEY (student_id) REFERENCES students(id);

ALTER TABLE students_enrolled_to_courses
    ADD FOREIGN KEY (course_id) REFERENCES courses(id);

Answered By - Nika
Answer Checked By - Gilberto Lyons (JavaFixing Admin)