FOREIGN_KEY_CHECKS=0 changes RESTRICT to NO ACTION in ADD FOREIGN KEY

When a foreign key is added with FOREIGN_KEY_CHECKS=0, an explicit ON DELETE/ON UPDATE RESTRICT is stored as NO ACTION. With FOREIGN_KEY_CHECKS=1, the same statement stores RESTRICT.

Version: 11.4.13 (newer versions not tested)

Test case:

CREATE TABLE parent (id INT PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE child (id INT PRIMARY KEY, parent_id INT, KEY (parent_id)) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE child ADD CONSTRAINT fk_test

  FOREIGN KEY (parent_id) REFERENCES parent(id)

  ON DELETE RESTRICT ON UPDATE RESTRICT;

SET FOREIGN_KEY_CHECKS=1;

SELECT UPDATE_RULE, DELETE_RULE

FROM information_schema.REFERENTIAL_CONSTRAINTS

WHERE CONSTRAINT_NAME = 'fk_test';

Actual result:

Expected result:

Dropping the constraint and adding it again with FOREIGN_KEY_CHECKS=1 gives the expected result (RESTRICT).

Why it matters: Enforcement is the same, but the stored definition depends on a session setting. Schema comparison and sync tools then report differences that weren't intended. For example, Navicat's structure synchronization runs SET FOREIGN_KEY_CHECKS=0 in its scripts, so every sync turns RESTRICT constraints into NO ACTION on the target database.