MySQL ignores foreign keys constraints in a table representing a many to many relationship

Question

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

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?

Answer 1

Worth checking that you are using a Storage Engine that supports Foreign Key Constraint checking.

SELECT TABLE_NAME,
       ENGINE
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = 'dbname';

The MyISAM Storage engine for instance does not support checking of Foreign Key Constraints.

If so and you need Foreign Key checks, try changing the storage engine to InnoDB:

ALTER TABLE my_table ENGINE = InnoDB;

If you need this to be the more standard behavior, look at the --default-storage-engine option in your startup.

Answer 2

This is not the expected behaviour of MySQL. I try to reproduce the error, but MySQL prompting foreign key constraint error. For reference:

mysql> create database studentmgmt;
Query OK, 1 row affected (0.00 sec)

mysql> use studentmgmt;
Database changed
mysql>  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 )
    ->   );
Query OK, 0 rows affected (0.45 sec)

mysql>
mysql>   CREATE TABLE subject(
    ->     idSubject INT NOT NULL AUTO_INCREMENT,
    ->     Name VARCHAR(100) NOT NULL,
    ->     Credit INT NOT NULL,
    ->     PRIMARY KEY ( idSubject )
    ->   );
Query OK, 0 rows affected (0.48 sec)

mysql>
mysql>   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)
    ->   );
Query OK, 0 rows affected (0.45 sec)

mysql>  INSERT INTO `studentmgmt`.`course_inscription` (`idSubject`, `idStudent`, `Classroom`) VALUES ('1', '1', '12');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`studentmgmt`.`course_inscription`, CONSTRAINT `course_inscription_ibfk_1` FOREIGN KEY (`idSubject`) REFERENCES `subject` (`idSubject`))
mysql> SET foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)

mysql>  INSERT INTO `studentmgmt`.`course_inscription` (`idSubject`, `idStudent`, `Classroom`) VALUES ('1', '1', '12');
Query OK, 1 row affected (0.09 sec)

There could be possibly something you missed or configured in different way. Check in your configuration file as well as run the following command provide the output:

mysql> show global variables like '%foreign_key%';