In MySQL, the foreign keys of a table standing for a many to many relationship do not work even though I did set them to the "child" table named
When I execute a statement to insert a new row into
course_inscription, MySQL must launch the error due to the checking of the foreign key constraint, but it does not work. I do not understand why the new row is "added" to the table successfully.
The structure and constraints of the relevant database are:
CREATE TABLE student ( idStudent INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, Gender VARCHAR(10), birthdate VARCHAR(40) NOT NULL, PRIMARY KEY ( idStudent ) ); CREATE TABLE subject ( idSubject INT NOT NULL AUTO_INCREMENT, Name VARCHAR(100) NOT NULL, Credit INT NOT NULL, PRIMARY KEY ( idSubject ) ); CREATE TABLE course_inscription ( idSubject INT NOT NULL, idStudent INT NOT NULL, Classroom VARCHAR(40) NOT NULL, PRIMARY KEY ( idSubject, idStudent ), FOREIGN KEY (idSubject) REFERENCES subject(idSubject), FOREIGN KEY (idStudent) REFERENCES student(idStudent) ); SET FOREIGN_KEY_CHECKS = 1;
The insert statement:
INSERT INTO `studentmgnt`.`course_inscription` (`idSubject`, `idStudent`, `Classroom`) VALUES ('1', '1', '12');
What did I do wrong to my database? How can I correct the issue?