Home Failed to execute SQL script statement – Missing index for constraint 'students_ibfk_1' in the referenced table

Questions

Failed to execute SQL script statement – Missing index for constraint 'students_ibfk_1' in the referenced table

July 9, 2022

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