MySQL FORIEGN_KEY_CHECKS=1 by default, but not working

Question

On MySQL Ver 14.14 Distrib 5.7.25, for Linux (x86_64), it seems that the default setting @@GLOBAL.foreign_key_checks=1 does not work, because I tried toINSERT a row that contained values that are not present in the parent tables' keys and the INSERT was successful.

Why do I have to SET FOREIGN_KEY_CHECKS=1 even though foreign_key_checks=1 is set by default?

For example, I have a table that looks like the following:

mysql> SHOW CREATE TABLE score\G
*************************** 1. row ***************************
       Table: score
Create Table: CREATE TABLE `score` (
  `student_id` int(10) unsigned NOT NULL,
  `event_id` int(10) unsigned NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`event_id`,`student_id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `fk_event_id` FOREIGN KEY (`event_id`) REFERENCES `grace_event` (`event_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_student_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

...I entered mysql> INSERT INTO score (event_id,student_id,score) VALUES(9999,9999,0); and got this...

Query OK, 1 row affected (0.01 sec)

So, trying to figure why the INSERT was successful when it should not have been, I read the following web pages...

• 'SET foreign_key_checks = 1' does not work again
• Foreign key not working in MySQL: Why can I INSERT a value that's not in the foreign column?

...but none of them seem to explain why/how the INSERT was successful (unfortunately).

I made sure all of the following were true for my 3 tables:

• Foreign keys must be INT UNSIGNED. Yes.
• Default storage engine must be InnoDB. Yes.
• Use ON UPDATE CASCADE for each foreign key declaration. Yes.
• phpmyadmin shows foreign key checks is set to ON. Yes.

...and SELECT @@GLOBAL.foreign_key_checks shows...

mysql> SELECT @@GLOBAL.foreign_key_checks;
+-----------------------------+
| @@GLOBAL.foreign_key_checks |
+-----------------------------+
|                           1 |
+-----------------------------+

With all the above settings, mysql> INSERT INTO score (event_id,student_id,score) VALUES(9999,9999,0); still succeeded.

It was not until I did SET FOREIGN_KEY_CHECKS=1 that INSERT finally failed...

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (sampdb.score, CONSTRAINT fk_event_id FOREIGN KEY (event_id) REFERENCES grace_event (event_id) ON UPDATE CASCADE)

Why did I have to SET FOREIGN_KEY_CHECKS=1 when it (apparently) already was set to 1 by default? Does the default @@GLOBAL.foreign_key_checks setting not mean anything? Is this a bug?

Answer 1

I can't reproduce the problem:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.25    |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.foreign_key_checks, @@SESSION.foreign_key_checks;
+-----------------------------+------------------------------+
| @@GLOBAL.foreign_key_checks | @@SESSION.foreign_key_checks |
+-----------------------------+------------------------------+
|                           1 |                            1 |
+-----------------------------+------------------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `student`, `grace_event`, `score`;
Query OK, 0 rows affected, 3 warnings (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `student` (
    ->   `student_id` int(10) unsigned NOT NULL PRIMARY KEY
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `grace_event` (
    ->   `grace_event` int(10) unsigned NOT NULL PRIMARY KEY
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `score` (
    ->   `student_id` int(10) unsigned NOT NULL,
    ->   `event_id` int(10) unsigned NOT NULL,
    ->   `score` int(11) NOT NULL,
    ->   PRIMARY KEY (`event_id`,`student_id`),
    ->   KEY `student_id` (`student_id`),
    ->   CONSTRAINT `fk_event_id` FOREIGN KEY (`event_id`)
    ->     REFERENCES `grace_event` (`grace_event`) ON UPDATE CASCADE,
    ->   CONSTRAINT `fk_student_id` FOREIGN KEY (`student_id`)
    ->     REFERENCES `student` (`student_id`) ON UPDATE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `score`
    ->   (`event_id`, `student_id`, `score`)
    -> VALUES
    ->   (9999, 9999, 0);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
                    constraint fails (`test`.`score`, CONSTRAINT
                    `fk_event_id` FOREIGN KEY (`event_id`) REFERENCES
                    `grace_event` (`grace_event`) ON UPDATE CASCADE)