1'#--------------------FN_DYNVARS_032_01-------------------------#' 2SET @@session.foreign_key_checks = 0; 3'connect (con1,localhost,root,,,,)' 4'connection con1' 5SELECT @@session.foreign_key_checks; 6@@session.foreign_key_checks 71 8SET @@session.foreign_key_checks = 1; 9'connect (con2,localhost,root,,,,)' 10'connection con2' 11SELECT @@session.foreign_key_checks; 12@@session.foreign_key_checks 131 14'#--------------------FN_DYNVARS_032_02-------------------------#' 15'connection con1' 16DROP TABLE IF EXISTS t1,t2; 17CREATE TABLE t1(a INT PRIMARY KEY)ENGINE = INNODB; 18CREATE TABLE t2(a INT PRIMARY KEY,b INT)ENGINE = INNODB; 19ALTER TABLE t2 20ADD CONSTRAINT fk FOREIGN KEY (b) REFERENCES t1 (a); 21'---Check when foreign_key_checks is enabled---' 22SET @@session.foreign_key_checks = 1; 23INSERT INTO t1 values (1),(2),(3); 24INSERT INTO t2 values (10,1); 25INSERT INTO t2 values (20,22); 26ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`b`) REFERENCES `t1` (`a`)) 27'---Check when foreign_key_checks is disabled---' 28TRUNCATE t1; 29ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`b`) REFERENCES `test`.`t1` (`a`)) 30SET @@session.foreign_key_checks = 0; 31TRUNCATE t1; 32TRUNCATE t2; 33INSERT INTO t1 values (1),(2),(3); 34INSERT INTO t2 values (10,1); 35INSERT INTO t2 values (20,4); 36'try enabling foreign_key_checks again'; 37SET @@session.foreign_key_checks = 1; 38UPDATE t2 SET b=4 where a=20; 39'Check when foreign_key_checks is enabled and FK constraint is re-created' 40SET @@session.foreign_key_checks = 0; 41TRUNCATE t2; 42TRUNCATE t1; 43INSERT INTO t1 values (1),(2),(3); 44INSERT INTO t2 values (10,1),(20,4); 45ALTER TABLE t2 DROP FOREIGN KEY fk; 46SET @@session.foreign_key_checks = 1; 47DELETE FROM t2 WHERE b not in (SELECT a from t1); 48ALTER TABLE t2 49ADD CONSTRAINT fk FOREIGN KEY (b) REFERENCES t1 (a); 50INSERT INTO t2 values (20,2); 51SELECT * from t2; 52a b 5310 1 5420 2 55DROP TABLE IF EXISTS t2; 56DROP TABLE IF EXISTS t1; 57