Debian Codegraph @

mysql-test/suite/sys_vars/r/foreign_key_checks_func.result

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