1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
############## mysql-test\t\foreign_key_checks_func.test #####################
# #
# Variable Name: foreign_key_checks #
# Scope: SESSION #
# Access Type: Dynamic #
# Data Type: boolean #
# Default Value: NA #
# Range: NA #
# #
# #
# Creation Date: 2008-03-08 #
# Author: Rizwan #
# #
# Description: Test Cases of Dynamic System Variable foreign_key_checks #
# that checks the behavior of this variable #
# #
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
# server-system-variables.html #
# #
###############################################################################
--source include/have_innodb.inc
--echo '#--------------------FN_DYNVARS_032_01-------------------------#'
####################################################################
# Check if setting foreign_key_checks is changed in new connection #
####################################################################
SET @@session.foreign_key_checks = 0;
# con1 will be default connection from now on
connect (con1,localhost,root,,,,);
connection con1;
SELECT @@session.foreign_key_checks;
SET @@session.foreign_key_checks = 1;
connect (con2,localhost,root,,,,);
connection con2;
SELECT @@session.foreign_key_checks;
disconnect con2;
--echo '#--------------------FN_DYNVARS_032_02-------------------------#'
#################################################################
# Begin the functionality Testing of foreign_key_checks #
#################################################################
connection con1;
--disable_warnings
DROP TABLE IF EXISTS t1,t2;
--enable_warnings
CREATE TABLE t1(a INT PRIMARY KEY)ENGINE = INNODB;
CREATE TABLE t2(a INT PRIMARY KEY,b INT)ENGINE = INNODB;
ALTER TABLE t2
ADD CONSTRAINT fk FOREIGN KEY (b) REFERENCES t1 (a);
#===========================================================
--echo '---Check when foreign_key_checks is enabled---'
#===========================================================
SET @@session.foreign_key_checks = 1;
INSERT INTO t1 values (1),(2),(3);
INSERT INTO t2 values (10,1);
--Error ER_NO_REFERENCED_ROW_2
INSERT INTO t2 values (20,22);
#===========================================================
--echo '---Check when foreign_key_checks is disabled---'
#===========================================================
--Error ER_TRUNCATE_ILLEGAL_FK
TRUNCATE t1;
SET @@session.foreign_key_checks = 0;
TRUNCATE t1;
TRUNCATE t2;
INSERT INTO t1 values (1),(2),(3);
INSERT INTO t2 values (10,1);
INSERT INTO t2 values (20,4);
--echo 'try enabling foreign_key_checks again';
SET @@session.foreign_key_checks = 1;
UPDATE t2 SET b=4 where a=20;
#==============================================================================
--echo 'Check when foreign_key_checks is enabled and FK constraint is re-created'
#==============================================================================
SET @@session.foreign_key_checks = 0;
TRUNCATE t2;
TRUNCATE t1;
INSERT INTO t1 values (1),(2),(3);
INSERT INTO t2 values (10,1),(20,4);
ALTER TABLE t2 DROP FOREIGN KEY fk;
SET @@session.foreign_key_checks = 1;
# Test disabled as error description is different. The resulting description has
# difference in code #sql-xxx_2 where xxx is different for each run.
#--Error ER_NO_REFERENCED_ROW_2
#ALTER TABLE t2
#ADD CONSTRAINT fk FOREIGN KEY (b) REFERENCES t1 (a);
# delete all rows with incorrect reference
DELETE FROM t2 WHERE b not in (SELECT a from t1);
ALTER TABLE t2
ADD CONSTRAINT fk FOREIGN KEY (b) REFERENCES t1 (a);
INSERT INTO t2 values (20,2);
SELECT * from t2;
--disable_warnings
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1;
--enable_warnings
##########################################################
# End of functionality Testing for foreign_key_checks #
##########################################################
|
