MySQL – Fix Ignored Foreign Keys Constraints in Many-to-Many Relationship

foreign keyMySQL

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?

Best Answer

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.

Related Question