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 '#--------------------FN_DYNVARS_032_02-------------------------#'
15 'connection con1'
16 DROP TABLE IF EXISTS t1,t2;
17 CREATE TABLE t1(a INT PRIMARY KEY)ENGINE = INNODB;
18 CREATE TABLE t2(a INT PRIMARY KEY,b INT)ENGINE = INNODB;
19 ALTER TABLE t2
20 ADD CONSTRAINT fk FOREIGN KEY (b) REFERENCES t1 (a);
21 '---Check when foreign_key_checks is enabled---'
22 SET @@session.foreign_key_checks = 1;
23 INSERT INTO t1 values (1),(2),(3);
24 INSERT INTO t2 values (10,1);
25 INSERT INTO t2 values (20,22);
26 ERROR 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---'
28 TRUNCATE t1;
29 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `fk`)
30 SET @@session.foreign_key_checks = 0;
31 TRUNCATE t1;
32 TRUNCATE t2;
33 INSERT INTO t1 values (1),(2),(3);
34 INSERT INTO t2 values (10,1);
35 INSERT INTO t2 values (20,4);
36 'try enabling foreign_key_checks again';
37 SET @@session.foreign_key_checks = 1;
38 UPDATE t2 SET b=4 where a=20;
39 'Check when foreign_key_checks is enabled and FK constraint is re-created'
40 SET @@session.foreign_key_checks = 0;
41 TRUNCATE t2;
42 TRUNCATE t1;
43 INSERT INTO t1 values (1),(2),(3);
44 INSERT INTO t2 values (10,1),(20,4);
45 ALTER TABLE t2 DROP FOREIGN KEY fk;
46 SET @@session.foreign_key_checks = 1;
47 DELETE FROM t2 WHERE b not in (SELECT a from t1);
48 ALTER TABLE t2
49 ADD CONSTRAINT fk FOREIGN KEY (b) REFERENCES t1 (a);
50 INSERT INTO t2 values (20,2);
51 SELECT * from t2;
52 a b
53 10 1
54 20 2
55 DROP TABLE IF EXISTS t2;
56 DROP TABLE IF EXISTS t1;