Debian Codegraph @

mysql-test/suite/storage_engine/foreign_keys.test

raw
1 #
2 # Simple foreign keys setup
3 #
4
5 --source have_engine.inc
6 --source have_default_index.inc
7
8 --disable_warnings
9 DROP TABLE IF EXISTS t1, t2;
10
11 let $create_definition =
12 a $int_indexed_col,
13 b $char_col,
14 $default_index (a)
15 ;
16 --source create_table.inc
17 if ($mysql_errname)
18 {
19 --source unexpected_result.inc
20 }
21 if (!$mysql_errname)
22 {
23 let $create_definition =
24 a $int_indexed_col,
25 b $char_col,
26 FOREIGN KEY (a) REFERENCES t1(a)
27 ;
28 --let $table_name = t2
29 --source create_table.inc
30 if ($mysql_errname)
31 {
32 --let $functionality = Foreign keys
33 --source unexpected_result.inc
34 }
35 if (!$mysql_errname)
36 {
37 --source mask_engine.inc
38 SHOW CREATE TABLE t2;
39
40 --let $error_codes = ER_NO_REFERENCED_ROW_2
41 INSERT INTO t2 (a,b) VALUES (1,'a'),(2,'b');
42 --source check_errors.inc
43 if ($mysql_errname != ER_NO_REFERENCED_ROW_2)
44 {
45 --let $functionality = Foreign keys
46 --source unexpected_result.inc
47 }
48 INSERT INTO t1 (a,b) VALUES (1,'c'),(2,'d');
49 INSERT INTO t2 (a,b) VALUES (1,'a'),(2,'b');
50
51 --let $error_codes = ER_NO_REFERENCED_ROW_2
52 UPDATE t2 SET a=a+1;
53 --source check_errors.inc
54 if ($mysql_errname != ER_NO_REFERENCED_ROW_2)
55 {
56 --let $functionality = Foreign keys
57 --source unexpected_result.inc
58 }
59
60 --let $error_codes = ER_ROW_IS_REFERENCED_2
61 UPDATE t1 SET a=3 WHERE a=2;
62 --source check_errors.inc
63
64 --let $error_codes = ER_ROW_IS_REFERENCED_2
65 DELETE FROM t1 WHERE a=2;
66 --source check_errors.inc
67 if ($mysql_errname != ER_ROW_IS_REFERENCED_2)
68 {
69 --let $functionality = Foreign keys
70 --source unexpected_result.inc
71 }
72
73 DELETE FROM t2 WHERE a=2;
74
75 --sorted_result
76 SELECT a,b FROM t1;
77 --sorted_result
78 SELECT a,b FROM t2;
79
80 --let $error_codes = ER_ROW_IS_REFERENCED_2
81 DROP TABLE t1;
82 --source check_errors.inc
83 if ($mysql_errname != ER_ROW_IS_REFERENCED_2)
84 {
85 --let $functionality = Foreign keys
86 --source unexpected_result.inc
87 }
88
89 DROP TABLE t2;
90 }
91
92 let $create_definition =
93 a $int_indexed_col,
94 b $char_col,
95 FOREIGN KEY (a) REFERENCES t1(a)
96 ON DELETE CASCADE ON UPDATE CASCADE
97 ;
98 --let $table_name = t2
99 --source create_table.inc
100 if ($mysql_errname)
101 {
102 --let $functionality = Foreign keys or ON DELETE|UPDATE CASCADE
103 --source unexpected_result.inc
104 }
105 if (!$mysql_errname)
106 {
107 --source mask_engine.inc
108 SHOW CREATE TABLE t2;
109
110 --let $error_codes = ER_NO_REFERENCED_ROW_2
111 INSERT INTO t2 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d');
112 --source check_errors.inc
113 INSERT INTO t1 (a,b) VALUES (3,'a'),(4,'a');
114 INSERT INTO t2 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(4,'e'),(3,'a');
115
116 UPDATE t1 SET a=a+1;
117 if ($mysql_errname)
118 {
119 --let $functionality = UPDATE
120 --source unexpected_result.inc
121 }
122 --sorted_result
123 SELECT a,b FROM t2;
124
125 DELETE FROM t1 WHERE b='a' LIMIT 2;
126 if ($mysql_errname)
127 {
128 --let $functionality = DELETE
129 --source unexpected_result.inc
130 }
131 --sorted_result
132 SELECT a,b FROM t2;
133
134 --let $error_codes = ER_TRUNCATE_ILLEGAL_FK
135 TRUNCATE TABLE t1;
136 --source check_errors.inc
137 if ($mysql_errname != ER_TRUNCATE_ILLEGAL_FK)
138 {
139 --let $functionality = Foreign keys
140 --source unexpected_result.inc
141 }
142
143 DROP TABLE t2;
144 }
145 DROP TABLE t1;
146 }
147
148 --source cleanup_engine.inc
149