0

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?

  • You don't use the schema when you create the tables, are you by mistake using the wrong table? Check if you have more than one course_inscription table: select table_schema from information_schema.tables where table_name = 'course_inscription'; – Lennart Aug 17 '17 at 8:05
  • No, I have only one course_inscription table – Lee Dat Aug 17 '17 at 8:27
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.

  • it doesnot work too even though I've set all their engine to InnoDB – Lee Dat Aug 16 '17 at 10:13
  • Very strange. With regards to @rathishDBA 's comment (Sorry, I'm not able to post comments yet), run the command from the mysql command line client to check that constraint checking is enabled. – Ian Roberts Aug 16 '17 at 11:34
  • Just a little sense check too. Were the tables InnoDB originally, or did you change them to InnoDB? If they were MYISAM originally, then although it would not have errored, it would not have actually created the Foreign Keys so try creating the Foreign Keys on the table again. ALTER TABLE course_inscription ADD FOREIGN KEY course_inscription_ibfk_1 (idSubject) REFERENCES subject(idSubject); ALTER TABLE course_inscription ADD FOREIGN KEY course_inscription_ibfk_2 (idStudent) REFERENCES student(idStudent); – Ian Roberts Aug 16 '17 at 11:42
  • Glad I could help. :) – Ian Roberts Aug 17 '17 at 22:23
0

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%';
  • where do we type this command ? – Lee Dat Aug 16 '17 at 10:26
  • Type commands in MySQL client, where you have typed the above create table statements. – rathishDBA Aug 16 '17 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.