wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2009 October 7 |
2 | # |
||
3 | # The author disclaims copyright to this source code. In place of |
||
4 | # a legal notice, here is a blessing: |
||
5 | # |
||
6 | # May you do good and not evil. |
||
7 | # May you find forgiveness for yourself and forgive others. |
||
8 | # May you share freely, never taking more than you give. |
||
9 | # |
||
10 | #*********************************************************************** |
||
11 | # |
||
12 | # This file implements tests to verify the "testable statements" in the |
||
13 | # foreignkeys.in document. |
||
14 | # |
||
15 | # The tests in this file are arranged to mirror the structure of |
||
16 | # foreignkey.in, with one exception: The statements in section 2, which |
||
17 | # deals with enabling/disabling foreign key support, is tested first, |
||
18 | # before section 1. This is because some statements in section 2 deal |
||
19 | # with builds that do not include complete foreign key support (because |
||
20 | # either SQLITE_OMIT_TRIGGER or SQLITE_OMIT_FOREIGN_KEY was defined |
||
21 | # at build time). |
||
22 | # |
||
23 | |||
24 | set testdir [file dirname $argv0] |
||
25 | source $testdir/tester.tcl |
||
26 | |||
27 | proc eqp {sql {db db}} { uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db } |
||
28 | |||
29 | ########################################################################### |
||
30 | ### SECTION 2: Enabling Foreign Key Support |
||
31 | ########################################################################### |
||
32 | |||
33 | #------------------------------------------------------------------------- |
||
34 | # EVIDENCE-OF: R-33710-56344 In order to use foreign key constraints in |
||
35 | # SQLite, the library must be compiled with neither |
||
36 | # SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined. |
||
37 | # |
||
38 | ifcapable trigger&&foreignkey { |
||
39 | do_test e_fkey-1 { |
||
40 | execsql { |
||
41 | PRAGMA foreign_keys = ON; |
||
42 | CREATE TABLE p(i PRIMARY KEY); |
||
43 | CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE); |
||
44 | INSERT INTO p VALUES('hello'); |
||
45 | INSERT INTO c VALUES('hello'); |
||
46 | UPDATE p SET i = 'world'; |
||
47 | SELECT * FROM c; |
||
48 | } |
||
49 | } {world} |
||
50 | } |
||
51 | |||
52 | #------------------------------------------------------------------------- |
||
53 | # Test the effects of defining OMIT_TRIGGER but not OMIT_FOREIGN_KEY. |
||
54 | # |
||
55 | # EVIDENCE-OF: R-44697-61543 If SQLITE_OMIT_TRIGGER is defined but |
||
56 | # SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to |
||
57 | # version 3.6.19 - foreign key definitions are parsed and may be queried |
||
58 | # using PRAGMA foreign_key_list, but foreign key constraints are not |
||
59 | # enforced. |
||
60 | # |
||
61 | # Specifically, test that "PRAGMA foreign_keys" is a no-op in this case. |
||
62 | # When using the pragma to query the current setting, 0 rows are returned. |
||
63 | # |
||
64 | # EVIDENCE-OF: R-22567-44039 The PRAGMA foreign_keys command is a no-op |
||
65 | # in this configuration. |
||
66 | # |
||
67 | # EVIDENCE-OF: R-41784-13339 Tip: If the command "PRAGMA foreign_keys" |
||
68 | # returns no data instead of a single row containing "0" or "1", then |
||
69 | # the version of SQLite you are using does not support foreign keys |
||
70 | # (either because it is older than 3.6.19 or because it was compiled |
||
71 | # with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined). |
||
72 | # |
||
73 | reset_db |
||
74 | ifcapable !trigger&&foreignkey { |
||
75 | do_test e_fkey-2.1 { |
||
76 | execsql { |
||
77 | PRAGMA foreign_keys = ON; |
||
78 | CREATE TABLE p(i PRIMARY KEY); |
||
79 | CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE); |
||
80 | INSERT INTO p VALUES('hello'); |
||
81 | INSERT INTO c VALUES('hello'); |
||
82 | UPDATE p SET i = 'world'; |
||
83 | SELECT * FROM c; |
||
84 | } |
||
85 | } {hello} |
||
86 | do_test e_fkey-2.2 { |
||
87 | execsql { PRAGMA foreign_key_list(c) } |
||
88 | } {0 0 p j {} CASCADE {NO ACTION} NONE} |
||
89 | do_test e_fkey-2.3 { |
||
90 | execsql { PRAGMA foreign_keys } |
||
91 | } {} |
||
92 | } |
||
93 | |||
94 | |||
95 | #------------------------------------------------------------------------- |
||
96 | # Test the effects of defining OMIT_FOREIGN_KEY. |
||
97 | # |
||
98 | # EVIDENCE-OF: R-58428-36660 If OMIT_FOREIGN_KEY is defined, then |
||
99 | # foreign key definitions cannot even be parsed (attempting to specify a |
||
100 | # foreign key definition is a syntax error). |
||
101 | # |
||
102 | # Specifically, test that foreign key constraints cannot even be parsed |
||
103 | # in such a build. |
||
104 | # |
||
105 | reset_db |
||
106 | ifcapable !foreignkey { |
||
107 | do_test e_fkey-3.1 { |
||
108 | execsql { CREATE TABLE p(i PRIMARY KEY) } |
||
109 | catchsql { CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE) } |
||
110 | } {1 {near "ON": syntax error}} |
||
111 | do_test e_fkey-3.2 { |
||
112 | # This is allowed, as in this build, "REFERENCES" is not a keyword. |
||
113 | # The declared datatype of column j is "REFERENCES p". |
||
114 | execsql { CREATE TABLE c(j REFERENCES p) } |
||
115 | } {} |
||
116 | do_test e_fkey-3.3 { |
||
117 | execsql { PRAGMA table_info(c) } |
||
118 | } {0 j {REFERENCES p} 0 {} 0} |
||
119 | do_test e_fkey-3.4 { |
||
120 | execsql { PRAGMA foreign_key_list(c) } |
||
121 | } {} |
||
122 | do_test e_fkey-3.5 { |
||
123 | execsql { PRAGMA foreign_keys } |
||
124 | } {} |
||
125 | } |
||
126 | |||
127 | ifcapable !foreignkey||!trigger { finish_test ; return } |
||
128 | reset_db |
||
129 | |||
130 | |||
131 | #------------------------------------------------------------------------- |
||
132 | # EVIDENCE-OF: R-07280-60510 Assuming the library is compiled with |
||
133 | # foreign key constraints enabled, it must still be enabled by the |
||
134 | # application at runtime, using the PRAGMA foreign_keys command. |
||
135 | # |
||
136 | # This also tests that foreign key constraints are disabled by default. |
||
137 | # |
||
138 | # EVIDENCE-OF: R-59578-04990 Foreign key constraints are disabled by |
||
139 | # default (for backwards compatibility), so must be enabled separately |
||
140 | # for each database connection separately. |
||
141 | # |
||
142 | drop_all_tables |
||
143 | do_test e_fkey-4.1 { |
||
144 | execsql { |
||
145 | CREATE TABLE p(i PRIMARY KEY); |
||
146 | CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE); |
||
147 | INSERT INTO p VALUES('hello'); |
||
148 | INSERT INTO c VALUES('hello'); |
||
149 | UPDATE p SET i = 'world'; |
||
150 | SELECT * FROM c; |
||
151 | } |
||
152 | } {hello} |
||
153 | do_test e_fkey-4.2 { |
||
154 | execsql { |
||
155 | DELETE FROM c; |
||
156 | DELETE FROM p; |
||
157 | PRAGMA foreign_keys = ON; |
||
158 | INSERT INTO p VALUES('hello'); |
||
159 | INSERT INTO c VALUES('hello'); |
||
160 | UPDATE p SET i = 'world'; |
||
161 | SELECT * FROM c; |
||
162 | } |
||
163 | } {world} |
||
164 | |||
165 | #------------------------------------------------------------------------- |
||
166 | # EVIDENCE-OF: R-15278-54456 The application can can also use a PRAGMA |
||
167 | # foreign_keys statement to determine if foreign keys are currently |
||
168 | # enabled. |
||
169 | # |
||
170 | # This also tests the example code in section 2 of foreignkeys.in. |
||
171 | # |
||
172 | # EVIDENCE-OF: R-11255-19907 |
||
173 | # |
||
174 | reset_db |
||
175 | do_test e_fkey-5.1 { |
||
176 | execsql { PRAGMA foreign_keys } |
||
177 | } {0} |
||
178 | do_test e_fkey-5.2 { |
||
179 | execsql { |
||
180 | PRAGMA foreign_keys = ON; |
||
181 | PRAGMA foreign_keys; |
||
182 | } |
||
183 | } {1} |
||
184 | do_test e_fkey-5.3 { |
||
185 | execsql { |
||
186 | PRAGMA foreign_keys = OFF; |
||
187 | PRAGMA foreign_keys; |
||
188 | } |
||
189 | } {0} |
||
190 | |||
191 | #------------------------------------------------------------------------- |
||
192 | # Test that it is not possible to enable or disable foreign key support |
||
193 | # while not in auto-commit mode. |
||
194 | # |
||
195 | # EVIDENCE-OF: R-46649-58537 It is not possible to enable or disable |
||
196 | # foreign key constraints in the middle of a multi-statement transaction |
||
197 | # (when SQLite is not in autocommit mode). Attempting to do so does not |
||
198 | # return an error; it simply has no effect. |
||
199 | # |
||
200 | reset_db |
||
201 | do_test e_fkey-6.1 { |
||
202 | execsql { |
||
203 | PRAGMA foreign_keys = ON; |
||
204 | CREATE TABLE t1(a UNIQUE, b); |
||
205 | CREATE TABLE t2(c, d REFERENCES t1(a)); |
||
206 | INSERT INTO t1 VALUES(1, 2); |
||
207 | INSERT INTO t2 VALUES(2, 1); |
||
208 | BEGIN; |
||
209 | PRAGMA foreign_keys = OFF; |
||
210 | } |
||
211 | catchsql { |
||
212 | DELETE FROM t1 |
||
213 | } |
||
214 | } {1 {foreign key constraint failed}} |
||
215 | do_test e_fkey-6.2 { |
||
216 | execsql { PRAGMA foreign_keys } |
||
217 | } {1} |
||
218 | do_test e_fkey-6.3 { |
||
219 | execsql { |
||
220 | COMMIT; |
||
221 | PRAGMA foreign_keys = OFF; |
||
222 | BEGIN; |
||
223 | PRAGMA foreign_keys = ON; |
||
224 | DELETE FROM t1; |
||
225 | PRAGMA foreign_keys; |
||
226 | } |
||
227 | } {0} |
||
228 | do_test e_fkey-6.4 { |
||
229 | execsql COMMIT |
||
230 | } {} |
||
231 | |||
232 | ########################################################################### |
||
233 | ### SECTION 1: Introduction to Foreign Key Constraints |
||
234 | ########################################################################### |
||
235 | execsql "PRAGMA foreign_keys = ON" |
||
236 | |||
237 | #------------------------------------------------------------------------- |
||
238 | # Verify that the syntax in the first example in section 1 is valid. |
||
239 | # |
||
240 | # EVIDENCE-OF: R-04042-24825 To do so, a foreign key definition may be |
||
241 | # added by modifying the declaration of the track table to the |
||
242 | # following: CREATE TABLE track( trackid INTEGER, trackname TEXT, |
||
243 | # trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES |
||
244 | # artist(artistid) ); |
||
245 | # |
||
246 | do_test e_fkey-7.1 { |
||
247 | execsql { |
||
248 | CREATE TABLE artist( |
||
249 | artistid INTEGER PRIMARY KEY, |
||
250 | artistname TEXT |
||
251 | ); |
||
252 | CREATE TABLE track( |
||
253 | trackid INTEGER, |
||
254 | trackname TEXT, |
||
255 | trackartist INTEGER, |
||
256 | FOREIGN KEY(trackartist) REFERENCES artist(artistid) |
||
257 | ); |
||
258 | } |
||
259 | } {} |
||
260 | |||
261 | #------------------------------------------------------------------------- |
||
262 | # EVIDENCE-OF: R-61362-32087 Attempting to insert a row into the track |
||
263 | # table that does not correspond to any row in the artist table will |
||
264 | # fail, |
||
265 | # |
||
266 | do_test e_fkey-8.1 { |
||
267 | catchsql { INSERT INTO track VALUES(1, 'track 1', 1) } |
||
268 | } {1 {foreign key constraint failed}} |
||
269 | do_test e_fkey-8.2 { |
||
270 | execsql { INSERT INTO artist VALUES(2, 'artist 1') } |
||
271 | catchsql { INSERT INTO track VALUES(1, 'track 1', 1) } |
||
272 | } {1 {foreign key constraint failed}} |
||
273 | do_test e_fkey-8.2 { |
||
274 | execsql { INSERT INTO track VALUES(1, 'track 1', 2) } |
||
275 | } {} |
||
276 | |||
277 | #------------------------------------------------------------------------- |
||
278 | # Attempting to delete a row from the 'artist' table while there are |
||
279 | # dependent rows in the track table also fails. |
||
280 | # |
||
281 | # EVIDENCE-OF: R-24401-52400 as will attempting to delete a row from the |
||
282 | # artist table when there exist dependent rows in the track table |
||
283 | # |
||
284 | do_test e_fkey-9.1 { |
||
285 | catchsql { DELETE FROM artist WHERE artistid = 2 } |
||
286 | } {1 {foreign key constraint failed}} |
||
287 | do_test e_fkey-9.2 { |
||
288 | execsql { |
||
289 | DELETE FROM track WHERE trackartist = 2; |
||
290 | DELETE FROM artist WHERE artistid = 2; |
||
291 | } |
||
292 | } {} |
||
293 | |||
294 | #------------------------------------------------------------------------- |
||
295 | # If the foreign key column (trackartist) in table 'track' is set to NULL, |
||
296 | # there is no requirement for a matching row in the 'artist' table. |
||
297 | # |
||
298 | # EVIDENCE-OF: R-23980-48859 There is one exception: if the foreign key |
||
299 | # column in the track table is NULL, then no corresponding entry in the |
||
300 | # artist table is required. |
||
301 | # |
||
302 | do_test e_fkey-10.1 { |
||
303 | execsql { |
||
304 | INSERT INTO track VALUES(1, 'track 1', NULL); |
||
305 | INSERT INTO track VALUES(2, 'track 2', NULL); |
||
306 | } |
||
307 | } {} |
||
308 | do_test e_fkey-10.2 { |
||
309 | execsql { SELECT * FROM artist } |
||
310 | } {} |
||
311 | do_test e_fkey-10.3 { |
||
312 | # Setting the trackid to a non-NULL value fails, of course. |
||
313 | catchsql { UPDATE track SET trackartist = 5 WHERE trackid = 1 } |
||
314 | } {1 {foreign key constraint failed}} |
||
315 | do_test e_fkey-10.4 { |
||
316 | execsql { |
||
317 | INSERT INTO artist VALUES(5, 'artist 5'); |
||
318 | UPDATE track SET trackartist = 5 WHERE trackid = 1; |
||
319 | } |
||
320 | catchsql { DELETE FROM artist WHERE artistid = 5} |
||
321 | } {1 {foreign key constraint failed}} |
||
322 | do_test e_fkey-10.5 { |
||
323 | execsql { |
||
324 | UPDATE track SET trackartist = NULL WHERE trackid = 1; |
||
325 | DELETE FROM artist WHERE artistid = 5; |
||
326 | } |
||
327 | } {} |
||
328 | |||
329 | #------------------------------------------------------------------------- |
||
330 | # Test that the following is true fo all rows in the track table: |
||
331 | # |
||
332 | # trackartist IS NULL OR |
||
333 | # EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist) |
||
334 | # |
||
335 | # EVIDENCE-OF: R-52486-21352 Expressed in SQL, this means that for every |
||
336 | # row in the track table, the following expression evaluates to true: |
||
337 | # trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE |
||
338 | # artistid=trackartist) |
||
339 | |||
340 | # This procedure executes a test case to check that statement |
||
341 | # R-52486-21352 is true after executing the SQL statement passed. |
||
342 | # as the second argument. |
||
343 | proc test_r52486_21352 {tn sql} { |
||
344 | set res [catchsql $sql] |
||
345 | set results { |
||
346 | {0 {}} |
||
347 | {1 {PRIMARY KEY must be unique}} |
||
348 | {1 {foreign key constraint failed}} |
||
349 | } |
||
350 | if {[lsearch $results $res]<0} { |
||
351 | error $res |
||
352 | } |
||
353 | |||
354 | do_test e_fkey-11.$tn { |
||
355 | execsql { |
||
356 | SELECT count(*) FROM track WHERE NOT ( |
||
357 | trackartist IS NULL OR |
||
358 | EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist) |
||
359 | ) |
||
360 | } |
||
361 | } {0} |
||
362 | } |
||
363 | |||
364 | # Execute a series of random INSERT, UPDATE and DELETE operations |
||
365 | # (some of which may fail due to FK or PK constraint violations) on |
||
366 | # the two tables in the example schema. Test that R-52486-21352 |
||
367 | # is true after executing each operation. |
||
368 | # |
||
369 | set Template { |
||
370 | {INSERT INTO track VALUES($t, 'track $t', $a)} |
||
371 | {DELETE FROM track WHERE trackid = $t} |
||
372 | {UPDATE track SET trackartist = $a WHERE trackid = $t} |
||
373 | {INSERT INTO artist VALUES($a, 'artist $a')} |
||
374 | {DELETE FROM artist WHERE artistid = $a} |
||
375 | {UPDATE artist SET artistid = $a2 WHERE artistid = $a} |
||
376 | } |
||
377 | for {set i 0} {$i < 500} {incr i} { |
||
378 | set a [expr int(rand()*10)] |
||
379 | set a2 [expr int(rand()*10)] |
||
380 | set t [expr int(rand()*50)] |
||
381 | set sql [subst [lindex $Template [expr int(rand()*6)]]] |
||
382 | |||
383 | test_r52486_21352 $i $sql |
||
384 | } |
||
385 | |||
386 | #------------------------------------------------------------------------- |
||
387 | # Check that a NOT NULL constraint can be added to the example schema |
||
388 | # to prohibit NULL child keys from being inserted. |
||
389 | # |
||
390 | # EVIDENCE-OF: R-42412-59321 Tip: If the application requires a stricter |
||
391 | # relationship between artist and track, where NULL values are not |
||
392 | # permitted in the trackartist column, simply add the appropriate "NOT |
||
393 | # NULL" constraint to the schema. |
||
394 | # |
||
395 | drop_all_tables |
||
396 | do_test e_fkey-12.1 { |
||
397 | execsql { |
||
398 | CREATE TABLE artist( |
||
399 | artistid INTEGER PRIMARY KEY, |
||
400 | artistname TEXT |
||
401 | ); |
||
402 | CREATE TABLE track( |
||
403 | trackid INTEGER, |
||
404 | trackname TEXT, |
||
405 | trackartist INTEGER NOT NULL, |
||
406 | FOREIGN KEY(trackartist) REFERENCES artist(artistid) |
||
407 | ); |
||
408 | } |
||
409 | } {} |
||
410 | do_test e_fkey-12.2 { |
||
411 | catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) } |
||
412 | } {1 {track.trackartist may not be NULL}} |
||
413 | |||
414 | #------------------------------------------------------------------------- |
||
415 | # EVIDENCE-OF: R-16127-35442 |
||
416 | # |
||
417 | # Test an example from foreignkeys.html. |
||
418 | # |
||
419 | drop_all_tables |
||
420 | do_test e_fkey-13.1 { |
||
421 | execsql { |
||
422 | CREATE TABLE artist( |
||
423 | artistid INTEGER PRIMARY KEY, |
||
424 | artistname TEXT |
||
425 | ); |
||
426 | CREATE TABLE track( |
||
427 | trackid INTEGER, |
||
428 | trackname TEXT, |
||
429 | trackartist INTEGER, |
||
430 | FOREIGN KEY(trackartist) REFERENCES artist(artistid) |
||
431 | ); |
||
432 | INSERT INTO artist VALUES(1, 'Dean Martin'); |
||
433 | INSERT INTO artist VALUES(2, 'Frank Sinatra'); |
||
434 | INSERT INTO track VALUES(11, 'That''s Amore', 1); |
||
435 | INSERT INTO track VALUES(12, 'Christmas Blues', 1); |
||
436 | INSERT INTO track VALUES(13, 'My Way', 2); |
||
437 | } |
||
438 | } {} |
||
439 | do_test e_fkey-13.2 { |
||
440 | catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', 3) } |
||
441 | } {1 {foreign key constraint failed}} |
||
442 | do_test e_fkey-13.3 { |
||
443 | execsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) } |
||
444 | } {} |
||
445 | do_test e_fkey-13.4 { |
||
446 | catchsql { |
||
447 | UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; |
||
448 | } |
||
449 | } {1 {foreign key constraint failed}} |
||
450 | do_test e_fkey-13.5 { |
||
451 | execsql { |
||
452 | INSERT INTO artist VALUES(3, 'Sammy Davis Jr.'); |
||
453 | UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; |
||
454 | INSERT INTO track VALUES(15, 'Boogie Woogie', 3); |
||
455 | } |
||
456 | } {} |
||
457 | |||
458 | #------------------------------------------------------------------------- |
||
459 | # EVIDENCE-OF: R-15958-50233 |
||
460 | # |
||
461 | # Test the second example from the first section of foreignkeys.html. |
||
462 | # |
||
463 | do_test e_fkey-14.1 { |
||
464 | catchsql { |
||
465 | DELETE FROM artist WHERE artistname = 'Frank Sinatra'; |
||
466 | } |
||
467 | } {1 {foreign key constraint failed}} |
||
468 | do_test e_fkey-14.2 { |
||
469 | execsql { |
||
470 | DELETE FROM track WHERE trackname = 'My Way'; |
||
471 | DELETE FROM artist WHERE artistname = 'Frank Sinatra'; |
||
472 | } |
||
473 | } {} |
||
474 | do_test e_fkey-14.3 { |
||
475 | catchsql { |
||
476 | UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; |
||
477 | } |
||
478 | } {1 {foreign key constraint failed}} |
||
479 | do_test e_fkey-14.4 { |
||
480 | execsql { |
||
481 | DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues'); |
||
482 | UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; |
||
483 | } |
||
484 | } {} |
||
485 | |||
486 | |||
487 | #------------------------------------------------------------------------- |
||
488 | # EVIDENCE-OF: R-56032-24923 The foreign key constraint is satisfied if |
||
489 | # for each row in the child table either one or more of the child key |
||
490 | # columns are NULL, or there exists a row in the parent table for which |
||
491 | # each parent key column contains a value equal to the value in its |
||
492 | # associated child key column. |
||
493 | # |
||
494 | # Test also that the usual comparison rules are used when testing if there |
||
495 | # is a matching row in the parent table of a foreign key constraint. |
||
496 | # |
||
497 | # EVIDENCE-OF: R-57765-12380 In the above paragraph, the term "equal" |
||
498 | # means equal when values are compared using the rules specified here. |
||
499 | # |
||
500 | drop_all_tables |
||
501 | do_test e_fkey-15.1 { |
||
502 | execsql { |
||
503 | CREATE TABLE par(p PRIMARY KEY); |
||
504 | CREATE TABLE chi(c REFERENCES par); |
||
505 | |||
506 | INSERT INTO par VALUES(1); |
||
507 | INSERT INTO par VALUES('1'); |
||
508 | INSERT INTO par VALUES(X'31'); |
||
509 | SELECT typeof(p) FROM par; |
||
510 | } |
||
511 | } {integer text blob} |
||
512 | |||
513 | proc test_efkey_45 {tn isError sql} { |
||
514 | do_test e_fkey-15.$tn.1 " |
||
515 | catchsql {$sql} |
||
516 | " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError] |
||
517 | |||
518 | do_test e_fkey-15.$tn.2 { |
||
519 | execsql { |
||
520 | SELECT * FROM chi WHERE c IS NOT NULL AND c NOT IN (SELECT p FROM par) |
||
521 | } |
||
522 | } {} |
||
523 | } |
||
524 | |||
525 | test_efkey_45 1 0 "INSERT INTO chi VALUES(1)" |
||
526 | test_efkey_45 2 1 "INSERT INTO chi VALUES('1.0')" |
||
527 | test_efkey_45 3 0 "INSERT INTO chi VALUES('1')" |
||
528 | test_efkey_45 4 1 "DELETE FROM par WHERE p = '1'" |
||
529 | test_efkey_45 5 0 "DELETE FROM chi WHERE c = '1'" |
||
530 | test_efkey_45 6 0 "DELETE FROM par WHERE p = '1'" |
||
531 | test_efkey_45 7 1 "INSERT INTO chi VALUES('1')" |
||
532 | test_efkey_45 8 0 "INSERT INTO chi VALUES(X'31')" |
||
533 | test_efkey_45 9 1 "INSERT INTO chi VALUES(X'32')" |
||
534 | |||
535 | #------------------------------------------------------------------------- |
||
536 | # Specifically, test that when comparing child and parent key values the |
||
537 | # default collation sequence of the parent key column is used. |
||
538 | # |
||
539 | # EVIDENCE-OF: R-15796-47513 When comparing text values, the collating |
||
540 | # sequence associated with the parent key column is always used. |
||
541 | # |
||
542 | drop_all_tables |
||
543 | do_test e_fkey-16.1 { |
||
544 | execsql { |
||
545 | CREATE TABLE t1(a COLLATE nocase PRIMARY KEY); |
||
546 | CREATE TABLE t2(b REFERENCES t1); |
||
547 | } |
||
548 | } {} |
||
549 | do_test e_fkey-16.2 { |
||
550 | execsql { |
||
551 | INSERT INTO t1 VALUES('oNe'); |
||
552 | INSERT INTO t2 VALUES('one'); |
||
553 | INSERT INTO t2 VALUES('ONE'); |
||
554 | UPDATE t2 SET b = 'OnE'; |
||
555 | UPDATE t1 SET a = 'ONE'; |
||
556 | } |
||
557 | } {} |
||
558 | do_test e_fkey-16.3 { |
||
559 | catchsql { UPDATE t2 SET b = 'two' WHERE rowid = 1 } |
||
560 | } {1 {foreign key constraint failed}} |
||
561 | do_test e_fkey-16.4 { |
||
562 | catchsql { DELETE FROM t1 WHERE rowid = 1 } |
||
563 | } {1 {foreign key constraint failed}} |
||
564 | |||
565 | #------------------------------------------------------------------------- |
||
566 | # Specifically, test that when comparing child and parent key values the |
||
567 | # affinity of the parent key column is applied to the child key value |
||
568 | # before the comparison takes place. |
||
569 | # |
||
570 | # EVIDENCE-OF: R-04240-13860 When comparing values, if the parent key |
||
571 | # column has an affinity, then that affinity is applied to the child key |
||
572 | # value before the comparison is performed. |
||
573 | # |
||
574 | drop_all_tables |
||
575 | do_test e_fkey-17.1 { |
||
576 | execsql { |
||
577 | CREATE TABLE t1(a NUMERIC PRIMARY KEY); |
||
578 | CREATE TABLE t2(b TEXT REFERENCES t1); |
||
579 | } |
||
580 | } {} |
||
581 | do_test e_fkey-17.2 { |
||
582 | execsql { |
||
583 | INSERT INTO t1 VALUES(1); |
||
584 | INSERT INTO t1 VALUES(2); |
||
585 | INSERT INTO t1 VALUES('three'); |
||
586 | INSERT INTO t2 VALUES('2.0'); |
||
587 | SELECT b, typeof(b) FROM t2; |
||
588 | } |
||
589 | } {2.0 text} |
||
590 | do_test e_fkey-17.3 { |
||
591 | execsql { SELECT typeof(a) FROM t1 } |
||
592 | } {integer integer text} |
||
593 | do_test e_fkey-17.4 { |
||
594 | catchsql { DELETE FROM t1 WHERE rowid = 2 } |
||
595 | } {1 {foreign key constraint failed}} |
||
596 | |||
597 | ########################################################################### |
||
598 | ### SECTION 3: Required and Suggested Database Indexes |
||
599 | ########################################################################### |
||
600 | |||
601 | #------------------------------------------------------------------------- |
||
602 | # A parent key must be either a PRIMARY KEY, subject to a UNIQUE |
||
603 | # constraint, or have a UNIQUE index created on it. |
||
604 | # |
||
605 | # EVIDENCE-OF: R-13435-26311 Usually, the parent key of a foreign key |
||
606 | # constraint is the primary key of the parent table. If they are not the |
||
607 | # primary key, then the parent key columns must be collectively subject |
||
608 | # to a UNIQUE constraint or have a UNIQUE index. |
||
609 | # |
||
610 | # Also test that if a parent key is not subject to a PRIMARY KEY or UNIQUE |
||
611 | # constraint, but does have a UNIQUE index created on it, then the UNIQUE index |
||
612 | # must use the default collation sequences associated with the parent key |
||
613 | # columns. |
||
614 | # |
||
615 | # EVIDENCE-OF: R-00376-39212 If the parent key columns have a UNIQUE |
||
616 | # index, then that index must use the collation sequences that are |
||
617 | # specified in the CREATE TABLE statement for the parent table. |
||
618 | # |
||
619 | drop_all_tables |
||
620 | do_test e_fkey-18.1 { |
||
621 | execsql { |
||
622 | CREATE TABLE t2(a REFERENCES t1(x)); |
||
623 | } |
||
624 | } {} |
||
625 | proc test_efkey_57 {tn isError sql} { |
||
626 | catchsql { DROP TABLE t1 } |
||
627 | execsql $sql |
||
628 | do_test e_fkey-18.$tn { |
||
629 | catchsql { INSERT INTO t2 VALUES(NULL) } |
||
630 | } [lindex {{0 {}} {1 {foreign key mismatch}}} $isError] |
||
631 | } |
||
632 | test_efkey_57 2 0 { CREATE TABLE t1(x PRIMARY KEY) } |
||
633 | test_efkey_57 3 0 { CREATE TABLE t1(x UNIQUE) } |
||
634 | test_efkey_57 4 0 { CREATE TABLE t1(x); CREATE UNIQUE INDEX t1i ON t1(x) } |
||
635 | test_efkey_57 5 1 { |
||
636 | CREATE TABLE t1(x); |
||
637 | CREATE UNIQUE INDEX t1i ON t1(x COLLATE nocase); |
||
638 | } |
||
639 | test_efkey_57 6 1 { CREATE TABLE t1(x) } |
||
640 | test_efkey_57 7 1 { CREATE TABLE t1(x, y, PRIMARY KEY(x, y)) } |
||
641 | test_efkey_57 8 1 { CREATE TABLE t1(x, y, UNIQUE(x, y)) } |
||
642 | test_efkey_57 9 1 { |
||
643 | CREATE TABLE t1(x, y); |
||
644 | CREATE UNIQUE INDEX t1i ON t1(x, y); |
||
645 | } |
||
646 | |||
647 | |||
648 | #------------------------------------------------------------------------- |
||
649 | # This block tests an example in foreignkeys.html. Several testable |
||
650 | # statements refer to this example, as follows |
||
651 | # |
||
652 | # EVIDENCE-OF: R-27484-01467 |
||
653 | # |
||
654 | # FK Constraints on child1, child2 and child3 are Ok. |
||
655 | # |
||
656 | # Problem with FK on child4: |
||
657 | # |
||
658 | # EVIDENCE-OF: R-51039-44840 The foreign key declared as part of table |
||
659 | # child4 is an error because even though the parent key column is |
||
660 | # indexed, the index is not UNIQUE. |
||
661 | # |
||
662 | # Problem with FK on child5: |
||
663 | # |
||
664 | # EVIDENCE-OF: R-01060-48788 The foreign key for table child5 is an |
||
665 | # error because even though the parent key column has a unique index, |
||
666 | # the index uses a different collating sequence. |
||
667 | # |
||
668 | # Problem with FK on child6 and child7: |
||
669 | # |
||
670 | # EVIDENCE-OF: R-63088-37469 Tables child6 and child7 are incorrect |
||
671 | # because while both have UNIQUE indices on their parent keys, the keys |
||
672 | # are not an exact match to the columns of a single UNIQUE index. |
||
673 | # |
||
674 | drop_all_tables |
||
675 | do_test e_fkey-19.1 { |
||
676 | execsql { |
||
677 | CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f); |
||
678 | CREATE UNIQUE INDEX i1 ON parent(c, d); |
||
679 | CREATE INDEX i2 ON parent(e); |
||
680 | CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase); |
||
681 | |||
682 | CREATE TABLE child1(f, g REFERENCES parent(a)); -- Ok |
||
683 | CREATE TABLE child2(h, i REFERENCES parent(b)); -- Ok |
||
684 | CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d)); -- Ok |
||
685 | CREATE TABLE child4(l, m REFERENCES parent(e)); -- Err |
||
686 | CREATE TABLE child5(n, o REFERENCES parent(f)); -- Err |
||
687 | CREATE TABLE child6(p, q, FOREIGN KEY(p,q) REFERENCES parent(b, c)); -- Err |
||
688 | CREATE TABLE child7(r REFERENCES parent(c)); -- Err |
||
689 | } |
||
690 | } {} |
||
691 | do_test e_fkey-19.2 { |
||
692 | execsql { |
||
693 | INSERT INTO parent VALUES(1, 2, 3, 4, 5, 6); |
||
694 | INSERT INTO child1 VALUES('xxx', 1); |
||
695 | INSERT INTO child2 VALUES('xxx', 2); |
||
696 | INSERT INTO child3 VALUES(3, 4); |
||
697 | } |
||
698 | } {} |
||
699 | do_test e_fkey-19.2 { |
||
700 | catchsql { INSERT INTO child4 VALUES('xxx', 5) } |
||
701 | } {1 {foreign key mismatch}} |
||
702 | do_test e_fkey-19.3 { |
||
703 | catchsql { INSERT INTO child5 VALUES('xxx', 6) } |
||
704 | } {1 {foreign key mismatch}} |
||
705 | do_test e_fkey-19.4 { |
||
706 | catchsql { INSERT INTO child6 VALUES(2, 3) } |
||
707 | } {1 {foreign key mismatch}} |
||
708 | do_test e_fkey-19.5 { |
||
709 | catchsql { INSERT INTO child7 VALUES(3) } |
||
710 | } {1 {foreign key mismatch}} |
||
711 | |||
712 | #------------------------------------------------------------------------- |
||
713 | # Test errors in the database schema that are detected while preparing |
||
714 | # DML statements. The error text for these messages always matches |
||
715 | # either "foreign key mismatch" or "no such table*" (using [string match]). |
||
716 | # |
||
717 | # EVIDENCE-OF: R-45488-08504 If the database schema contains foreign key |
||
718 | # errors that require looking at more than one table definition to |
||
719 | # identify, then those errors are not detected when the tables are |
||
720 | # created. |
||
721 | # |
||
722 | # EVIDENCE-OF: R-48391-38472 Instead, such errors prevent the |
||
723 | # application from preparing SQL statements that modify the content of |
||
724 | # the child or parent tables in ways that use the foreign keys. |
||
725 | # |
||
726 | # EVIDENCE-OF: R-03108-63659 The English language error message for |
||
727 | # foreign key DML errors is usually "foreign key mismatch" but can also |
||
728 | # be "no such table" if the parent table does not exist. |
||
729 | # |
||
730 | # EVIDENCE-OF: R-60781-26576 Foreign key DML errors are may be reported |
||
731 | # if: The parent table does not exist, or The parent key columns named |
||
732 | # in the foreign key constraint do not exist, or The parent key columns |
||
733 | # named in the foreign key constraint are not the primary key of the |
||
734 | # parent table and are not subject to a unique constraint using |
||
735 | # collating sequence specified in the CREATE TABLE, or The child table |
||
736 | # references the primary key of the parent without specifying the |
||
737 | # primary key columns and the number of primary key columns in the |
||
738 | # parent do not match the number of child key columns. |
||
739 | # |
||
740 | do_test e_fkey-20.1 { |
||
741 | execsql { |
||
742 | CREATE TABLE c1(c REFERENCES nosuchtable, d); |
||
743 | |||
744 | CREATE TABLE p2(a, b, UNIQUE(a, b)); |
||
745 | CREATE TABLE c2(c, d, FOREIGN KEY(c, d) REFERENCES p2(a, x)); |
||
746 | |||
747 | CREATE TABLE p3(a PRIMARY KEY, b); |
||
748 | CREATE TABLE c3(c REFERENCES p3(b), d); |
||
749 | |||
750 | CREATE TABLE p4(a PRIMARY KEY, b); |
||
751 | CREATE UNIQUE INDEX p4i ON p4(b COLLATE nocase); |
||
752 | CREATE TABLE c4(c REFERENCES p4(b), d); |
||
753 | |||
754 | CREATE TABLE p5(a PRIMARY KEY, b COLLATE nocase); |
||
755 | CREATE UNIQUE INDEX p5i ON p5(b COLLATE binary); |
||
756 | CREATE TABLE c5(c REFERENCES p5(b), d); |
||
757 | |||
758 | CREATE TABLE p6(a PRIMARY KEY, b); |
||
759 | CREATE TABLE c6(c, d, FOREIGN KEY(c, d) REFERENCES p6); |
||
760 | |||
761 | CREATE TABLE p7(a, b, PRIMARY KEY(a, b)); |
||
762 | CREATE TABLE c7(c, d REFERENCES p7); |
||
763 | } |
||
764 | } {} |
||
765 | |||
766 | foreach {tn tbl ptbl err} { |
||
767 | 2 c1 {} "no such table: main.nosuchtable" |
||
768 | 3 c2 p2 "foreign key mismatch" |
||
769 | 4 c3 p3 "foreign key mismatch" |
||
770 | 5 c4 p4 "foreign key mismatch" |
||
771 | 6 c5 p5 "foreign key mismatch" |
||
772 | 7 c6 p6 "foreign key mismatch" |
||
773 | 8 c7 p7 "foreign key mismatch" |
||
774 | } { |
||
775 | do_test e_fkey-20.$tn.1 { |
||
776 | catchsql "INSERT INTO $tbl VALUES('a', 'b')" |
||
777 | } [list 1 $err] |
||
778 | do_test e_fkey-20.$tn.2 { |
||
779 | catchsql "UPDATE $tbl SET c = ?, d = ?" |
||
780 | } [list 1 $err] |
||
781 | do_test e_fkey-20.$tn.3 { |
||
782 | catchsql "INSERT INTO $tbl SELECT ?, ?" |
||
783 | } [list 1 $err] |
||
784 | |||
785 | if {$ptbl ne ""} { |
||
786 | do_test e_fkey-20.$tn.4 { |
||
787 | catchsql "DELETE FROM $ptbl" |
||
788 | } [list 1 $err] |
||
789 | do_test e_fkey-20.$tn.5 { |
||
790 | catchsql "UPDATE $ptbl SET a = ?, b = ?" |
||
791 | } [list 1 $err] |
||
792 | do_test e_fkey-20.$tn.6 { |
||
793 | catchsql "INSERT INTO $ptbl SELECT ?, ?" |
||
794 | } [list 1 $err] |
||
795 | } |
||
796 | } |
||
797 | |||
798 | #------------------------------------------------------------------------- |
||
799 | # EVIDENCE-OF: R-19353-43643 |
||
800 | # |
||
801 | # Test the example of foreign key mismatch errors caused by implicitly |
||
802 | # mapping a child key to the primary key of the parent table when the |
||
803 | # child key consists of a different number of columns to that primary key. |
||
804 | # |
||
805 | drop_all_tables |
||
806 | do_test e_fkey-21.1 { |
||
807 | execsql { |
||
808 | CREATE TABLE parent2(a, b, PRIMARY KEY(a,b)); |
||
809 | |||
810 | CREATE TABLE child8(x, y, FOREIGN KEY(x,y) REFERENCES parent2); -- Ok |
||
811 | CREATE TABLE child9(x REFERENCES parent2); -- Err |
||
812 | CREATE TABLE child10(x,y,z, FOREIGN KEY(x,y,z) REFERENCES parent2); -- Err |
||
813 | } |
||
814 | } {} |
||
815 | do_test e_fkey-21.2 { |
||
816 | execsql { |
||
817 | INSERT INTO parent2 VALUES('I', 'II'); |
||
818 | INSERT INTO child8 VALUES('I', 'II'); |
||
819 | } |
||
820 | } {} |
||
821 | do_test e_fkey-21.3 { |
||
822 | catchsql { INSERT INTO child9 VALUES('I') } |
||
823 | } {1 {foreign key mismatch}} |
||
824 | do_test e_fkey-21.4 { |
||
825 | catchsql { INSERT INTO child9 VALUES('II') } |
||
826 | } {1 {foreign key mismatch}} |
||
827 | do_test e_fkey-21.5 { |
||
828 | catchsql { INSERT INTO child9 VALUES(NULL) } |
||
829 | } {1 {foreign key mismatch}} |
||
830 | do_test e_fkey-21.6 { |
||
831 | catchsql { INSERT INTO child10 VALUES('I', 'II', 'III') } |
||
832 | } {1 {foreign key mismatch}} |
||
833 | do_test e_fkey-21.7 { |
||
834 | catchsql { INSERT INTO child10 VALUES(1, 2, 3) } |
||
835 | } {1 {foreign key mismatch}} |
||
836 | do_test e_fkey-21.8 { |
||
837 | catchsql { INSERT INTO child10 VALUES(NULL, NULL, NULL) } |
||
838 | } {1 {foreign key mismatch}} |
||
839 | |||
840 | #------------------------------------------------------------------------- |
||
841 | # Test errors that are reported when creating the child table. |
||
842 | # Specifically: |
||
843 | # |
||
844 | # * different number of child and parent key columns, and |
||
845 | # * child columns that do not exist. |
||
846 | # |
||
847 | # EVIDENCE-OF: R-23682-59820 By contrast, if foreign key errors can be |
||
848 | # recognized simply by looking at the definition of the child table and |
||
849 | # without having to consult the parent table definition, then the CREATE |
||
850 | # TABLE statement for the child table fails. |
||
851 | # |
||
852 | # These errors are reported whether or not FK support is enabled. |
||
853 | # |
||
854 | # EVIDENCE-OF: R-33883-28833 Foreign key DDL errors are reported |
||
855 | # regardless of whether or not foreign key constraints are enabled when |
||
856 | # the table is created. |
||
857 | # |
||
858 | drop_all_tables |
||
859 | foreach fk [list OFF ON] { |
||
860 | execsql "PRAGMA foreign_keys = $fk" |
||
861 | set i 0 |
||
862 | foreach {sql error} { |
||
863 | "CREATE TABLE child1(a, b, FOREIGN KEY(a, b) REFERENCES p(c))" |
||
864 | {number of columns in foreign key does not match the number of columns in the referenced table} |
||
865 | "CREATE TABLE child2(a, b, FOREIGN KEY(a, b) REFERENCES p(c, d, e))" |
||
866 | {number of columns in foreign key does not match the number of columns in the referenced table} |
||
867 | "CREATE TABLE child2(a, b, FOREIGN KEY(a, c) REFERENCES p(c, d))" |
||
868 | {unknown column "c" in foreign key definition} |
||
869 | "CREATE TABLE child2(a, b, FOREIGN KEY(c, b) REFERENCES p(c, d))" |
||
870 | {unknown column "c" in foreign key definition} |
||
871 | } { |
||
872 | do_test e_fkey-22.$fk.[incr i] { |
||
873 | catchsql $sql |
||
874 | } [list 1 $error] |
||
875 | } |
||
876 | } |
||
877 | |||
878 | #------------------------------------------------------------------------- |
||
879 | # Test that a REFERENCING clause that does not specify parent key columns |
||
880 | # implicitly maps to the primary key of the parent table. |
||
881 | # |
||
882 | # EVIDENCE-OF: R-43879-08025 Attaching a "REFERENCES <parent-table>" |
||
883 | # clause to a column definition creates a foreign |
||
884 | # key constraint that maps the column to the primary key of |
||
885 | # <parent-table>. |
||
886 | # |
||
887 | do_test e_fkey-23.1 { |
||
888 | execsql { |
||
889 | CREATE TABLE p1(a, b, PRIMARY KEY(a, b)); |
||
890 | CREATE TABLE p2(a, b PRIMARY KEY); |
||
891 | CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p1); |
||
892 | CREATE TABLE c2(a, b REFERENCES p2); |
||
893 | } |
||
894 | } {} |
||
895 | proc test_efkey_60 {tn isError sql} { |
||
896 | do_test e_fkey-23.$tn " |
||
897 | catchsql {$sql} |
||
898 | " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError] |
||
899 | } |
||
900 | |||
901 | test_efkey_60 2 1 "INSERT INTO c1 VALUES(239, 231)" |
||
902 | test_efkey_60 3 0 "INSERT INTO p1 VALUES(239, 231)" |
||
903 | test_efkey_60 4 0 "INSERT INTO c1 VALUES(239, 231)" |
||
904 | test_efkey_60 5 1 "INSERT INTO c2 VALUES(239, 231)" |
||
905 | test_efkey_60 6 0 "INSERT INTO p2 VALUES(239, 231)" |
||
906 | test_efkey_60 7 0 "INSERT INTO c2 VALUES(239, 231)" |
||
907 | |||
908 | #------------------------------------------------------------------------- |
||
909 | # Test that an index on on the child key columns of an FK constraint |
||
910 | # is optional. |
||
911 | # |
||
912 | # EVIDENCE-OF: R-15417-28014 Indices are not required for child key |
||
913 | # columns |
||
914 | # |
||
915 | # Also test that if an index is created on the child key columns, it does |
||
916 | # not make a difference whether or not it is a UNIQUE index. |
||
917 | # |
||
918 | # EVIDENCE-OF: R-15741-50893 The child key index does not have to be |
||
919 | # (and usually will not be) a UNIQUE index. |
||
920 | # |
||
921 | drop_all_tables |
||
922 | do_test e_fkey-24.1 { |
||
923 | execsql { |
||
924 | CREATE TABLE parent(x, y, UNIQUE(y, x)); |
||
925 | CREATE TABLE c1(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y)); |
||
926 | CREATE TABLE c2(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y)); |
||
927 | CREATE TABLE c3(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y)); |
||
928 | CREATE INDEX c2i ON c2(a, b); |
||
929 | CREATE UNIQUE INDEX c3i ON c2(b, a); |
||
930 | } |
||
931 | } {} |
||
932 | proc test_efkey_61 {tn isError sql} { |
||
933 | do_test e_fkey-24.$tn " |
||
934 | catchsql {$sql} |
||
935 | " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError] |
||
936 | } |
||
937 | foreach {tn c} [list 2 c1 3 c2 4 c3] { |
||
938 | test_efkey_61 $tn.1 1 "INSERT INTO $c VALUES(1, 2)" |
||
939 | test_efkey_61 $tn.2 0 "INSERT INTO parent VALUES(1, 2)" |
||
940 | test_efkey_61 $tn.3 0 "INSERT INTO $c VALUES(1, 2)" |
||
941 | |||
942 | execsql "DELETE FROM $c ; DELETE FROM parent" |
||
943 | } |
||
944 | |||
945 | #------------------------------------------------------------------------- |
||
946 | # EVIDENCE-OF: R-00279-52283 |
||
947 | # |
||
948 | # Test an example showing that when a row is deleted from the parent |
||
949 | # table, the child table is queried for orphaned rows as follows: |
||
950 | # |
||
951 | # SELECT rowid FROM track WHERE trackartist = ? |
||
952 | # |
||
953 | # EVIDENCE-OF: R-23302-30956 If this SELECT returns any rows at all, |
||
954 | # then SQLite concludes that deleting the row from the parent table |
||
955 | # would violate the foreign key constraint and returns an error. |
||
956 | # |
||
957 | do_test e_fkey-25.1 { |
||
958 | execsql { |
||
959 | CREATE TABLE artist( |
||
960 | artistid INTEGER PRIMARY KEY, |
||
961 | artistname TEXT |
||
962 | ); |
||
963 | CREATE TABLE track( |
||
964 | trackid INTEGER, |
||
965 | trackname TEXT, |
||
966 | trackartist INTEGER, |
||
967 | FOREIGN KEY(trackartist) REFERENCES artist(artistid) |
||
968 | ); |
||
969 | } |
||
970 | } {} |
||
971 | do_execsql_test e_fkey-25.2 { |
||
972 | PRAGMA foreign_keys = OFF; |
||
973 | EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1; |
||
974 | EXPLAIN QUERY PLAN SELECT rowid FROM track WHERE trackartist = ?; |
||
975 | } { |
||
976 | |||
977 | |||
978 | } |
||
979 | do_execsql_test e_fkey-25.3 { |
||
980 | PRAGMA foreign_keys = ON; |
||
981 | EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1; |
||
982 | } { |
||
983 | |||
984 | |||
985 | } |
||
986 | do_test e_fkey-25.4 { |
||
987 | execsql { |
||
988 | INSERT INTO artist VALUES(5, 'artist 5'); |
||
989 | INSERT INTO artist VALUES(6, 'artist 6'); |
||
990 | INSERT INTO artist VALUES(7, 'artist 7'); |
||
991 | INSERT INTO track VALUES(1, 'track 1', 5); |
||
992 | INSERT INTO track VALUES(2, 'track 2', 6); |
||
993 | } |
||
994 | } {} |
||
995 | |||
996 | do_test e_fkey-25.5 { |
||
997 | concat \ |
||
998 | [execsql { SELECT rowid FROM track WHERE trackartist = 5 }] \ |
||
999 | [catchsql { DELETE FROM artist WHERE artistid = 5 }] |
||
1000 | } {1 1 {foreign key constraint failed}} |
||
1001 | |||
1002 | do_test e_fkey-25.6 { |
||
1003 | concat \ |
||
1004 | [execsql { SELECT rowid FROM track WHERE trackartist = 7 }] \ |
||
1005 | [catchsql { DELETE FROM artist WHERE artistid = 7 }] |
||
1006 | } {0 {}} |
||
1007 | |||
1008 | do_test e_fkey-25.7 { |
||
1009 | concat \ |
||
1010 | [execsql { SELECT rowid FROM track WHERE trackartist = 6 }] \ |
||
1011 | [catchsql { DELETE FROM artist WHERE artistid = 6 }] |
||
1012 | } {2 1 {foreign key constraint failed}} |
||
1013 | |||
1014 | #------------------------------------------------------------------------- |
||
1015 | # EVIDENCE-OF: R-47936-10044 Or, more generally: |
||
1016 | # SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value |
||
1017 | # |
||
1018 | # Test that when a row is deleted from the parent table of an FK |
||
1019 | # constraint, the child table is queried for orphaned rows. The |
||
1020 | # query is equivalent to: |
||
1021 | # |
||
1022 | # SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value |
||
1023 | # |
||
1024 | # Also test that when a row is inserted into the parent table, or when the |
||
1025 | # parent key values of an existing row are modified, a query equivalent |
||
1026 | # to the following is planned. In some cases it is not executed, but it |
||
1027 | # is always planned. |
||
1028 | # |
||
1029 | # SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value |
||
1030 | # |
||
1031 | # EVIDENCE-OF: R-61616-46700 Similar queries may be run if the content |
||
1032 | # of the parent key is modified or a new row is inserted into the parent |
||
1033 | # table. |
||
1034 | # |
||
1035 | # |
||
1036 | drop_all_tables |
||
1037 | do_test e_fkey-26.1 { |
||
1038 | execsql { CREATE TABLE parent(x, y, UNIQUE(y, x)) } |
||
1039 | } {} |
||
1040 | foreach {tn sql} { |
||
1041 | 2 { |
||
1042 | CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y)) |
||
1043 | } |
||
1044 | 3 { |
||
1045 | CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y)); |
||
1046 | CREATE INDEX childi ON child(a, b); |
||
1047 | } |
||
1048 | 4 { |
||
1049 | CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y)); |
||
1050 | CREATE UNIQUE INDEX childi ON child(b, a); |
||
1051 | } |
||
1052 | } { |
||
1053 | execsql $sql |
||
1054 | |||
1055 | execsql {PRAGMA foreign_keys = OFF} |
||
1056 | set delete [concat \ |
||
1057 | [eqp "DELETE FROM parent WHERE 1"] \ |
||
1058 | [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"] |
||
1059 | ] |
||
1060 | set update [concat \ |
||
1061 | [eqp "UPDATE parent SET x=?, y=?"] \ |
||
1062 | [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"] \ |
||
1063 | [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"] |
||
1064 | ] |
||
1065 | execsql {PRAGMA foreign_keys = ON} |
||
1066 | |||
1067 | do_test e_fkey-26.$tn.1 { eqp "DELETE FROM parent WHERE 1" } $delete |
||
1068 | do_test e_fkey-26.$tn.2 { eqp "UPDATE parent set x=?, y=?" } $update |
||
1069 | |||
1070 | execsql {DROP TABLE child} |
||
1071 | } |
||
1072 | |||
1073 | #------------------------------------------------------------------------- |
||
1074 | # EVIDENCE-OF: R-14553-34013 |
||
1075 | # |
||
1076 | # Test the example schema at the end of section 3. Also test that is |
||
1077 | # is "efficient". In this case "efficient" means that foreign key |
||
1078 | # related operations on the parent table do not provoke linear scans. |
||
1079 | # |
||
1080 | drop_all_tables |
||
1081 | do_test e_fkey-27.1 { |
||
1082 | execsql { |
||
1083 | CREATE TABLE artist( |
||
1084 | artistid INTEGER PRIMARY KEY, |
||
1085 | artistname TEXT |
||
1086 | ); |
||
1087 | CREATE TABLE track( |
||
1088 | trackid INTEGER, |
||
1089 | trackname TEXT, |
||
1090 | trackartist INTEGER REFERENCES artist |
||
1091 | ); |
||
1092 | CREATE INDEX trackindex ON track(trackartist); |
||
1093 | } |
||
1094 | } {} |
||
1095 | do_test e_fkey-27.2 { |
||
1096 | eqp { INSERT INTO artist VALUES(?, ?) } |
||
1097 | } {} |
||
1098 | do_execsql_test e_fkey-27.3 { |
||
1099 | EXPLAIN QUERY PLAN UPDATE artist SET artistid = ?, artistname = ? |
||
1100 | } { |
||
1101 | |||
1102 | |||
1103 | |||
1104 | } |
||
1105 | do_execsql_test e_fkey-27.4 { |
||
1106 | EXPLAIN QUERY PLAN DELETE FROM artist |
||
1107 | } { |
||
1108 | |||
1109 | |||
1110 | } |
||
1111 | |||
1112 | |||
1113 | ########################################################################### |
||
1114 | ### SECTION 4.1: Composite Foreign Key Constraints |
||
1115 | ########################################################################### |
||
1116 | |||
1117 | #------------------------------------------------------------------------- |
||
1118 | # Check that parent and child keys must have the same number of columns. |
||
1119 | # |
||
1120 | # EVIDENCE-OF: R-41062-34431 Parent and child keys must have the same |
||
1121 | # cardinality. |
||
1122 | # |
||
1123 | foreach {tn sql err} { |
||
1124 | 1 "CREATE TABLE c(jj REFERENCES p(x, y))" |
||
1125 | {foreign key on jj should reference only one column of table p} |
||
1126 | |||
1127 | 2 "CREATE TABLE c(jj REFERENCES p())" {near ")": syntax error} |
||
1128 | |||
1129 | 3 "CREATE TABLE c(jj, FOREIGN KEY(jj) REFERENCES p(x, y))" |
||
1130 | {number of columns in foreign key does not match the number of columns in the referenced table} |
||
1131 | |||
1132 | 4 "CREATE TABLE c(jj, FOREIGN KEY(jj) REFERENCES p())" |
||
1133 | {near ")": syntax error} |
||
1134 | |||
1135 | 5 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p())" |
||
1136 | {near ")": syntax error} |
||
1137 | |||
1138 | 6 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p(x))" |
||
1139 | {number of columns in foreign key does not match the number of columns in the referenced table} |
||
1140 | |||
1141 | 7 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p(x,y,z))" |
||
1142 | {number of columns in foreign key does not match the number of columns in the referenced table} |
||
1143 | } { |
||
1144 | drop_all_tables |
||
1145 | do_test e_fkey-28.$tn [list catchsql $sql] [list 1 $err] |
||
1146 | } |
||
1147 | do_test e_fkey-28.8 { |
||
1148 | drop_all_tables |
||
1149 | execsql { |
||
1150 | CREATE TABLE p(x PRIMARY KEY); |
||
1151 | CREATE TABLE c(a, b, FOREIGN KEY(a,b) REFERENCES p); |
||
1152 | } |
||
1153 | catchsql {DELETE FROM p} |
||
1154 | } {1 {foreign key mismatch}} |
||
1155 | do_test e_fkey-28.9 { |
||
1156 | drop_all_tables |
||
1157 | execsql { |
||
1158 | CREATE TABLE p(x, y, PRIMARY KEY(x,y)); |
||
1159 | CREATE TABLE c(a REFERENCES p); |
||
1160 | } |
||
1161 | catchsql {DELETE FROM p} |
||
1162 | } {1 {foreign key mismatch}} |
||
1163 | |||
1164 | |||
1165 | #------------------------------------------------------------------------- |
||
1166 | # EVIDENCE-OF: R-24676-09859 |
||
1167 | # |
||
1168 | # Test the example schema in the "Composite Foreign Key Constraints" |
||
1169 | # section. |
||
1170 | # |
||
1171 | do_test e_fkey-29.1 { |
||
1172 | execsql { |
||
1173 | CREATE TABLE album( |
||
1174 | albumartist TEXT, |
||
1175 | albumname TEXT, |
||
1176 | albumcover BINARY, |
||
1177 | PRIMARY KEY(albumartist, albumname) |
||
1178 | ); |
||
1179 | CREATE TABLE song( |
||
1180 | songid INTEGER, |
||
1181 | songartist TEXT, |
||
1182 | songalbum TEXT, |
||
1183 | songname TEXT, |
||
1184 | FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist,albumname) |
||
1185 | ); |
||
1186 | } |
||
1187 | } {} |
||
1188 | |||
1189 | do_test e_fkey-29.2 { |
||
1190 | execsql { |
||
1191 | INSERT INTO album VALUES('Elvis Presley', 'Elvis'' Christmas Album', NULL); |
||
1192 | INSERT INTO song VALUES( |
||
1193 | 1, 'Elvis Presley', 'Elvis'' Christmas Album', 'Here Comes Santa Clause' |
||
1194 | ); |
||
1195 | } |
||
1196 | } {} |
||
1197 | do_test e_fkey-29.3 { |
||
1198 | catchsql { |
||
1199 | INSERT INTO song VALUES(2, 'Elvis Presley', 'Elvis Is Back!', 'Fever'); |
||
1200 | } |
||
1201 | } {1 {foreign key constraint failed}} |
||
1202 | |||
1203 | |||
1204 | #------------------------------------------------------------------------- |
||
1205 | # EVIDENCE-OF: R-33626-48418 In SQLite, if any of the child key columns |
||
1206 | # (in this case songartist and songalbum) are NULL, then there is no |
||
1207 | # requirement for a corresponding row in the parent table. |
||
1208 | # |
||
1209 | do_test e_fkey-30.1 { |
||
1210 | execsql { |
||
1211 | INSERT INTO song VALUES(2, 'Elvis Presley', NULL, 'Fever'); |
||
1212 | INSERT INTO song VALUES(3, NULL, 'Elvis Is Back', 'Soldier Boy'); |
||
1213 | } |
||
1214 | } {} |
||
1215 | |||
1216 | ########################################################################### |
||
1217 | ### SECTION 4.2: Deferred Foreign Key Constraints |
||
1218 | ########################################################################### |
||
1219 | |||
1220 | #------------------------------------------------------------------------- |
||
1221 | # Test that if a statement violates an immediate FK constraint, and the |
||
1222 | # database does not satisfy the FK constraint once all effects of the |
||
1223 | # statement have been applied, an error is reported and the effects of |
||
1224 | # the statement rolled back. |
||
1225 | # |
||
1226 | # EVIDENCE-OF: R-09323-30470 If a statement modifies the contents of the |
||
1227 | # database so that an immediate foreign key constraint is in violation |
||
1228 | # at the conclusion the statement, an exception is thrown and the |
||
1229 | # effects of the statement are reverted. |
||
1230 | # |
||
1231 | drop_all_tables |
||
1232 | do_test e_fkey-31.1 { |
||
1233 | execsql { |
||
1234 | CREATE TABLE king(a, b, PRIMARY KEY(a)); |
||
1235 | CREATE TABLE prince(c REFERENCES king, d); |
||
1236 | } |
||
1237 | } {} |
||
1238 | |||
1239 | do_test e_fkey-31.2 { |
||
1240 | # Execute a statement that violates the immediate FK constraint. |
||
1241 | catchsql { INSERT INTO prince VALUES(1, 2) } |
||
1242 | } {1 {foreign key constraint failed}} |
||
1243 | |||
1244 | do_test e_fkey-31.3 { |
||
1245 | # This time, use a trigger to fix the constraint violation before the |
||
1246 | # statement has finished executing. Then execute the same statement as |
||
1247 | # in the previous test case. This time, no error. |
||
1248 | execsql { |
||
1249 | CREATE TRIGGER kt AFTER INSERT ON prince WHEN |
||
1250 | NOT EXISTS (SELECT a FROM king WHERE a = new.c) |
||
1251 | BEGIN |
||
1252 | INSERT INTO king VALUES(new.c, NULL); |
||
1253 | END |
||
1254 | } |
||
1255 | execsql { INSERT INTO prince VALUES(1, 2) } |
||
1256 | } {} |
||
1257 | |||
1258 | # Test that operating inside a transaction makes no difference to |
||
1259 | # immediate constraint violation handling. |
||
1260 | do_test e_fkey-31.4 { |
||
1261 | execsql { |
||
1262 | BEGIN; |
||
1263 | INSERT INTO prince VALUES(2, 3); |
||
1264 | DROP TRIGGER kt; |
||
1265 | } |
||
1266 | catchsql { INSERT INTO prince VALUES(3, 4) } |
||
1267 | } {1 {foreign key constraint failed}} |
||
1268 | do_test e_fkey-31.5 { |
||
1269 | execsql { |
||
1270 | COMMIT; |
||
1271 | SELECT * FROM king; |
||
1272 | } |
||
1273 | } {1 {} 2 {}} |
||
1274 | |||
1275 | #------------------------------------------------------------------------- |
||
1276 | # Test that if a deferred constraint is violated within a transaction, |
||
1277 | # nothing happens immediately and the database is allowed to persist |
||
1278 | # in a state that does not satisfy the FK constraint. However attempts |
||
1279 | # to COMMIT the transaction fail until the FK constraint is satisfied. |
||
1280 | # |
||
1281 | # EVIDENCE-OF: R-49178-21358 By contrast, if a statement modifies the |
||
1282 | # contents of the database such that a deferred foreign key constraint |
||
1283 | # is violated, the violation is not reported immediately. |
||
1284 | # |
||
1285 | # EVIDENCE-OF: R-39692-12488 Deferred foreign key constraints are not |
||
1286 | # checked until the transaction tries to COMMIT. |
||
1287 | # |
||
1288 | # EVIDENCE-OF: R-55147-47664 For as long as the user has an open |
||
1289 | # transaction, the database is allowed to exist in a state that violates |
||
1290 | # any number of deferred foreign key constraints. |
||
1291 | # |
||
1292 | # EVIDENCE-OF: R-29604-30395 However, COMMIT will fail as long as |
||
1293 | # foreign key constraints remain in violation. |
||
1294 | # |
||
1295 | proc test_efkey_34 {tn isError sql} { |
||
1296 | do_test e_fkey-32.$tn " |
||
1297 | catchsql {$sql} |
||
1298 | " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError] |
||
1299 | } |
||
1300 | drop_all_tables |
||
1301 | |||
1302 | test_efkey_34 1 0 { |
||
1303 | CREATE TABLE ll(k PRIMARY KEY); |
||
1304 | CREATE TABLE kk(c REFERENCES ll DEFERRABLE INITIALLY DEFERRED); |
||
1305 | } |
||
1306 | test_efkey_34 2 0 "BEGIN" |
||
1307 | test_efkey_34 3 0 "INSERT INTO kk VALUES(5)" |
||
1308 | test_efkey_34 4 0 "INSERT INTO kk VALUES(10)" |
||
1309 | test_efkey_34 5 1 "COMMIT" |
||
1310 | test_efkey_34 6 0 "INSERT INTO ll VALUES(10)" |
||
1311 | test_efkey_34 7 1 "COMMIT" |
||
1312 | test_efkey_34 8 0 "INSERT INTO ll VALUES(5)" |
||
1313 | test_efkey_34 9 0 "COMMIT" |
||
1314 | |||
1315 | #------------------------------------------------------------------------- |
||
1316 | # When not running inside a transaction, a deferred constraint is similar |
||
1317 | # to an immediate constraint (violations are reported immediately). |
||
1318 | # |
||
1319 | # EVIDENCE-OF: R-56844-61705 If the current statement is not inside an |
||
1320 | # explicit transaction (a BEGIN/COMMIT/ROLLBACK block), then an implicit |
||
1321 | # transaction is committed as soon as the statement has finished |
||
1322 | # executing. In this case deferred constraints behave the same as |
||
1323 | # immediate constraints. |
||
1324 | # |
||
1325 | drop_all_tables |
||
1326 | proc test_efkey_35 {tn isError sql} { |
||
1327 | do_test e_fkey-33.$tn " |
||
1328 | catchsql {$sql} |
||
1329 | " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError] |
||
1330 | } |
||
1331 | do_test e_fkey-33.1 { |
||
1332 | execsql { |
||
1333 | CREATE TABLE parent(x, y); |
||
1334 | CREATE UNIQUE INDEX pi ON parent(x, y); |
||
1335 | CREATE TABLE child(a, b, |
||
1336 | FOREIGN KEY(a, b) REFERENCES parent(x, y) DEFERRABLE INITIALLY DEFERRED |
||
1337 | ); |
||
1338 | } |
||
1339 | } {} |
||
1340 | test_efkey_35 2 1 "INSERT INTO child VALUES('x', 'y')" |
||
1341 | test_efkey_35 3 0 "INSERT INTO parent VALUES('x', 'y')" |
||
1342 | test_efkey_35 4 0 "INSERT INTO child VALUES('x', 'y')" |
||
1343 | |||
1344 | |||
1345 | #------------------------------------------------------------------------- |
||
1346 | # EVIDENCE-OF: R-12782-61841 |
||
1347 | # |
||
1348 | # Test that an FK constraint is made deferred by adding the following |
||
1349 | # to the definition: |
||
1350 | # |
||
1351 | # DEFERRABLE INITIALLY DEFERRED |
||
1352 | # |
||
1353 | # EVIDENCE-OF: R-09005-28791 |
||
1354 | # |
||
1355 | # Also test that adding any of the following to a foreign key definition |
||
1356 | # makes the constraint IMMEDIATE: |
||
1357 | # |
||
1358 | # NOT DEFERRABLE INITIALLY DEFERRED |
||
1359 | # NOT DEFERRABLE INITIALLY IMMEDIATE |
||
1360 | # NOT DEFERRABLE |
||
1361 | # DEFERRABLE INITIALLY IMMEDIATE |
||
1362 | # DEFERRABLE |
||
1363 | # |
||
1364 | # Foreign keys are IMMEDIATE by default (if there is no DEFERRABLE or NOT |
||
1365 | # DEFERRABLE clause). |
||
1366 | # |
||
1367 | # EVIDENCE-OF: R-35290-16460 Foreign key constraints are immediate by |
||
1368 | # default. |
||
1369 | # |
||
1370 | # EVIDENCE-OF: R-30323-21917 Each foreign key constraint in SQLite is |
||
1371 | # classified as either immediate or deferred. |
||
1372 | # |
||
1373 | drop_all_tables |
||
1374 | do_test e_fkey-34.1 { |
||
1375 | execsql { |
||
1376 | CREATE TABLE parent(x, y, z, PRIMARY KEY(x,y,z)); |
||
1377 | CREATE TABLE c1(a, b, c, |
||
1378 | FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY DEFERRED |
||
1379 | ); |
||
1380 | CREATE TABLE c2(a, b, c, |
||
1381 | FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY IMMEDIATE |
||
1382 | ); |
||
1383 | CREATE TABLE c3(a, b, c, |
||
1384 | FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE |
||
1385 | ); |
||
1386 | CREATE TABLE c4(a, b, c, |
||
1387 | FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE INITIALLY IMMEDIATE |
||
1388 | ); |
||
1389 | CREATE TABLE c5(a, b, c, |
||
1390 | FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE |
||
1391 | ); |
||
1392 | CREATE TABLE c6(a, b, c, FOREIGN KEY(a, b, c) REFERENCES parent); |
||
1393 | |||
1394 | -- This FK constraint is the only deferrable one. |
||
1395 | CREATE TABLE c7(a, b, c, |
||
1396 | FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE INITIALLY DEFERRED |
||
1397 | ); |
||
1398 | |||
1399 | INSERT INTO parent VALUES('a', 'b', 'c'); |
||
1400 | INSERT INTO parent VALUES('d', 'e', 'f'); |
||
1401 | INSERT INTO parent VALUES('g', 'h', 'i'); |
||
1402 | INSERT INTO parent VALUES('j', 'k', 'l'); |
||
1403 | INSERT INTO parent VALUES('m', 'n', 'o'); |
||
1404 | INSERT INTO parent VALUES('p', 'q', 'r'); |
||
1405 | INSERT INTO parent VALUES('s', 't', 'u'); |
||
1406 | |||
1407 | INSERT INTO c1 VALUES('a', 'b', 'c'); |
||
1408 | INSERT INTO c2 VALUES('d', 'e', 'f'); |
||
1409 | INSERT INTO c3 VALUES('g', 'h', 'i'); |
||
1410 | INSERT INTO c4 VALUES('j', 'k', 'l'); |
||
1411 | INSERT INTO c5 VALUES('m', 'n', 'o'); |
||
1412 | INSERT INTO c6 VALUES('p', 'q', 'r'); |
||
1413 | INSERT INTO c7 VALUES('s', 't', 'u'); |
||
1414 | } |
||
1415 | } {} |
||
1416 | |||
1417 | proc test_efkey_29 {tn sql isError} { |
||
1418 | do_test e_fkey-34.$tn "catchsql {$sql}" [ |
||
1419 | lindex {{0 {}} {1 {foreign key constraint failed}}} $isError |
||
1420 | ] |
||
1421 | } |
||
1422 | test_efkey_29 2 "BEGIN" 0 |
||
1423 | test_efkey_29 3 "DELETE FROM parent WHERE x = 'a'" 1 |
||
1424 | test_efkey_29 4 "DELETE FROM parent WHERE x = 'd'" 1 |
||
1425 | test_efkey_29 5 "DELETE FROM parent WHERE x = 'g'" 1 |
||
1426 | test_efkey_29 6 "DELETE FROM parent WHERE x = 'j'" 1 |
||
1427 | test_efkey_29 7 "DELETE FROM parent WHERE x = 'm'" 1 |
||
1428 | test_efkey_29 8 "DELETE FROM parent WHERE x = 'p'" 1 |
||
1429 | test_efkey_29 9 "DELETE FROM parent WHERE x = 's'" 0 |
||
1430 | test_efkey_29 10 "COMMIT" 1 |
||
1431 | test_efkey_29 11 "ROLLBACK" 0 |
||
1432 | |||
1433 | test_efkey_29 9 "BEGIN" 0 |
||
1434 | test_efkey_29 10 "UPDATE parent SET z = 'z' WHERE z = 'c'" 1 |
||
1435 | test_efkey_29 11 "UPDATE parent SET z = 'z' WHERE z = 'f'" 1 |
||
1436 | test_efkey_29 12 "UPDATE parent SET z = 'z' WHERE z = 'i'" 1 |
||
1437 | test_efkey_29 13 "UPDATE parent SET z = 'z' WHERE z = 'l'" 1 |
||
1438 | test_efkey_29 14 "UPDATE parent SET z = 'z' WHERE z = 'o'" 1 |
||
1439 | test_efkey_29 15 "UPDATE parent SET z = 'z' WHERE z = 'r'" 1 |
||
1440 | test_efkey_29 16 "UPDATE parent SET z = 'z' WHERE z = 'u'" 0 |
||
1441 | test_efkey_29 17 "COMMIT" 1 |
||
1442 | test_efkey_29 18 "ROLLBACK" 0 |
||
1443 | |||
1444 | test_efkey_29 17 "BEGIN" 0 |
||
1445 | test_efkey_29 18 "INSERT INTO c1 VALUES(1, 2, 3)" 1 |
||
1446 | test_efkey_29 19 "INSERT INTO c2 VALUES(1, 2, 3)" 1 |
||
1447 | test_efkey_29 20 "INSERT INTO c3 VALUES(1, 2, 3)" 1 |
||
1448 | test_efkey_29 21 "INSERT INTO c4 VALUES(1, 2, 3)" 1 |
||
1449 | test_efkey_29 22 "INSERT INTO c5 VALUES(1, 2, 3)" 1 |
||
1450 | test_efkey_29 22 "INSERT INTO c6 VALUES(1, 2, 3)" 1 |
||
1451 | test_efkey_29 22 "INSERT INTO c7 VALUES(1, 2, 3)" 0 |
||
1452 | test_efkey_29 23 "COMMIT" 1 |
||
1453 | test_efkey_29 24 "INSERT INTO parent VALUES(1, 2, 3)" 0 |
||
1454 | test_efkey_29 25 "COMMIT" 0 |
||
1455 | |||
1456 | test_efkey_29 26 "BEGIN" 0 |
||
1457 | test_efkey_29 27 "UPDATE c1 SET a = 10" 1 |
||
1458 | test_efkey_29 28 "UPDATE c2 SET a = 10" 1 |
||
1459 | test_efkey_29 29 "UPDATE c3 SET a = 10" 1 |
||
1460 | test_efkey_29 30 "UPDATE c4 SET a = 10" 1 |
||
1461 | test_efkey_29 31 "UPDATE c5 SET a = 10" 1 |
||
1462 | test_efkey_29 31 "UPDATE c6 SET a = 10" 1 |
||
1463 | test_efkey_29 31 "UPDATE c7 SET a = 10" 0 |
||
1464 | test_efkey_29 32 "COMMIT" 1 |
||
1465 | test_efkey_29 33 "ROLLBACK" 0 |
||
1466 | |||
1467 | #------------------------------------------------------------------------- |
||
1468 | # EVIDENCE-OF: R-24499-57071 |
||
1469 | # |
||
1470 | # Test an example from foreignkeys.html dealing with a deferred foreign |
||
1471 | # key constraint. |
||
1472 | # |
||
1473 | do_test e_fkey-35.1 { |
||
1474 | drop_all_tables |
||
1475 | execsql { |
||
1476 | CREATE TABLE artist( |
||
1477 | artistid INTEGER PRIMARY KEY, |
||
1478 | artistname TEXT |
||
1479 | ); |
||
1480 | CREATE TABLE track( |
||
1481 | trackid INTEGER, |
||
1482 | trackname TEXT, |
||
1483 | trackartist INTEGER REFERENCES artist(artistid) DEFERRABLE INITIALLY DEFERRED |
||
1484 | ); |
||
1485 | } |
||
1486 | } {} |
||
1487 | do_test e_fkey-35.2 { |
||
1488 | execsql { |
||
1489 | BEGIN; |
||
1490 | INSERT INTO track VALUES(1, 'White Christmas', 5); |
||
1491 | } |
||
1492 | catchsql COMMIT |
||
1493 | } {1 {foreign key constraint failed}} |
||
1494 | do_test e_fkey-35.3 { |
||
1495 | execsql { |
||
1496 | INSERT INTO artist VALUES(5, 'Bing Crosby'); |
||
1497 | COMMIT; |
||
1498 | } |
||
1499 | } {} |
||
1500 | |||
1501 | #------------------------------------------------------------------------- |
||
1502 | # Verify that a nested savepoint may be released without satisfying |
||
1503 | # deferred foreign key constraints. |
||
1504 | # |
||
1505 | # EVIDENCE-OF: R-07223-48323 A nested savepoint transaction may be |
||
1506 | # RELEASEd while the database is in a state that does not satisfy a |
||
1507 | # deferred foreign key constraint. |
||
1508 | # |
||
1509 | drop_all_tables |
||
1510 | do_test e_fkey-36.1 { |
||
1511 | execsql { |
||
1512 | CREATE TABLE t1(a PRIMARY KEY, |
||
1513 | b REFERENCES t1 DEFERRABLE INITIALLY DEFERRED |
||
1514 | ); |
||
1515 | INSERT INTO t1 VALUES(1, 1); |
||
1516 | INSERT INTO t1 VALUES(2, 2); |
||
1517 | INSERT INTO t1 VALUES(3, 3); |
||
1518 | } |
||
1519 | } {} |
||
1520 | do_test e_fkey-36.2 { |
||
1521 | execsql { |
||
1522 | BEGIN; |
||
1523 | SAVEPOINT one; |
||
1524 | INSERT INTO t1 VALUES(4, 5); |
||
1525 | RELEASE one; |
||
1526 | } |
||
1527 | } {} |
||
1528 | do_test e_fkey-36.3 { |
||
1529 | catchsql COMMIT |
||
1530 | } {1 {foreign key constraint failed}} |
||
1531 | do_test e_fkey-36.4 { |
||
1532 | execsql { |
||
1533 | UPDATE t1 SET a = 5 WHERE a = 4; |
||
1534 | COMMIT; |
||
1535 | } |
||
1536 | } {} |
||
1537 | |||
1538 | |||
1539 | #------------------------------------------------------------------------- |
||
1540 | # Check that a transaction savepoint (an outermost savepoint opened when |
||
1541 | # the database was in auto-commit mode) cannot be released without |
||
1542 | # satisfying deferred foreign key constraints. It may be rolled back. |
||
1543 | # |
||
1544 | # EVIDENCE-OF: R-44295-13823 A transaction savepoint (a non-nested |
||
1545 | # savepoint that was opened while there was not currently an open |
||
1546 | # transaction), on the other hand, is subject to the same restrictions |
||
1547 | # as a COMMIT - attempting to RELEASE it while the database is in such a |
||
1548 | # state will fail. |
||
1549 | # |
||
1550 | do_test e_fkey-37.1 { |
||
1551 | execsql { |
||
1552 | SAVEPOINT one; |
||
1553 | SAVEPOINT two; |
||
1554 | INSERT INTO t1 VALUES(6, 7); |
||
1555 | RELEASE two; |
||
1556 | } |
||
1557 | } {} |
||
1558 | do_test e_fkey-37.2 { |
||
1559 | catchsql {RELEASE one} |
||
1560 | } {1 {foreign key constraint failed}} |
||
1561 | do_test e_fkey-37.3 { |
||
1562 | execsql { |
||
1563 | UPDATE t1 SET a = 7 WHERE a = 6; |
||
1564 | RELEASE one; |
||
1565 | } |
||
1566 | } {} |
||
1567 | do_test e_fkey-37.4 { |
||
1568 | execsql { |
||
1569 | SAVEPOINT one; |
||
1570 | SAVEPOINT two; |
||
1571 | INSERT INTO t1 VALUES(9, 10); |
||
1572 | RELEASE two; |
||
1573 | } |
||
1574 | } {} |
||
1575 | do_test e_fkey-37.5 { |
||
1576 | catchsql {RELEASE one} |
||
1577 | } {1 {foreign key constraint failed}} |
||
1578 | do_test e_fkey-37.6 { |
||
1579 | execsql {ROLLBACK TO one ; RELEASE one} |
||
1580 | } {} |
||
1581 | |||
1582 | #------------------------------------------------------------------------- |
||
1583 | # Test that if a COMMIT operation fails due to deferred foreign key |
||
1584 | # constraints, any nested savepoints remain open. |
||
1585 | # |
||
1586 | # EVIDENCE-OF: R-37736-42616 If a COMMIT statement (or the RELEASE of a |
||
1587 | # transaction SAVEPOINT) fails because the database is currently in a |
||
1588 | # state that violates a deferred foreign key constraint and there are |
||
1589 | # currently nested savepoints, the nested savepoints remain open. |
||
1590 | # |
||
1591 | do_test e_fkey-38.1 { |
||
1592 | execsql { |
||
1593 | DELETE FROM t1 WHERE a>3; |
||
1594 | SELECT * FROM t1; |
||
1595 | } |
||
1596 | } {1 1 2 2 3 3} |
||
1597 | do_test e_fkey-38.2 { |
||
1598 | execsql { |
||
1599 | BEGIN; |
||
1600 | INSERT INTO t1 VALUES(4, 4); |
||
1601 | SAVEPOINT one; |
||
1602 | INSERT INTO t1 VALUES(5, 6); |
||
1603 | SELECT * FROM t1; |
||
1604 | } |
||
1605 | } {1 1 2 2 3 3 4 4 5 6} |
||
1606 | do_test e_fkey-38.3 { |
||
1607 | catchsql COMMIT |
||
1608 | } {1 {foreign key constraint failed}} |
||
1609 | do_test e_fkey-38.4 { |
||
1610 | execsql { |
||
1611 | ROLLBACK TO one; |
||
1612 | COMMIT; |
||
1613 | SELECT * FROM t1; |
||
1614 | } |
||
1615 | } {1 1 2 2 3 3 4 4} |
||
1616 | |||
1617 | do_test e_fkey-38.5 { |
||
1618 | execsql { |
||
1619 | SAVEPOINT a; |
||
1620 | INSERT INTO t1 VALUES(5, 5); |
||
1621 | SAVEPOINT b; |
||
1622 | INSERT INTO t1 VALUES(6, 7); |
||
1623 | SAVEPOINT c; |
||
1624 | INSERT INTO t1 VALUES(7, 8); |
||
1625 | } |
||
1626 | } {} |
||
1627 | do_test e_fkey-38.6 { |
||
1628 | catchsql {RELEASE a} |
||
1629 | } {1 {foreign key constraint failed}} |
||
1630 | do_test e_fkey-38.7 { |
||
1631 | execsql {ROLLBACK TO c} |
||
1632 | catchsql {RELEASE a} |
||
1633 | } {1 {foreign key constraint failed}} |
||
1634 | do_test e_fkey-38.8 { |
||
1635 | execsql { |
||
1636 | ROLLBACK TO b; |
||
1637 | RELEASE a; |
||
1638 | SELECT * FROM t1; |
||
1639 | } |
||
1640 | } {1 1 2 2 3 3 4 4 5 5} |
||
1641 | |||
1642 | ########################################################################### |
||
1643 | ### SECTION 4.3: ON DELETE and ON UPDATE Actions |
||
1644 | ########################################################################### |
||
1645 | |||
1646 | #------------------------------------------------------------------------- |
||
1647 | # Test that configured ON DELETE and ON UPDATE actions take place when |
||
1648 | # deleting or modifying rows of the parent table, respectively. |
||
1649 | # |
||
1650 | # EVIDENCE-OF: R-48270-44282 Foreign key ON DELETE and ON UPDATE clauses |
||
1651 | # are used to configure actions that take place when deleting rows from |
||
1652 | # the parent table (ON DELETE), or modifying the parent key values of |
||
1653 | # existing rows (ON UPDATE). |
||
1654 | # |
||
1655 | # Test that a single FK constraint may have different actions configured |
||
1656 | # for ON DELETE and ON UPDATE. |
||
1657 | # |
||
1658 | # EVIDENCE-OF: R-48124-63225 A single foreign key constraint may have |
||
1659 | # different actions configured for ON DELETE and ON UPDATE. |
||
1660 | # |
||
1661 | do_test e_fkey-39.1 { |
||
1662 | execsql { |
||
1663 | CREATE TABLE p(a, b PRIMARY KEY, c); |
||
1664 | CREATE TABLE c1(d, e, f DEFAULT 'k0' REFERENCES p |
||
1665 | ON UPDATE SET DEFAULT |
||
1666 | ON DELETE SET NULL |
||
1667 | ); |
||
1668 | |||
1669 | INSERT INTO p VALUES(0, 'k0', ''); |
||
1670 | INSERT INTO p VALUES(1, 'k1', 'I'); |
||
1671 | INSERT INTO p VALUES(2, 'k2', 'II'); |
||
1672 | INSERT INTO p VALUES(3, 'k3', 'III'); |
||
1673 | |||
1674 | INSERT INTO c1 VALUES(1, 'xx', 'k1'); |
||
1675 | INSERT INTO c1 VALUES(2, 'xx', 'k2'); |
||
1676 | INSERT INTO c1 VALUES(3, 'xx', 'k3'); |
||
1677 | } |
||
1678 | } {} |
||
1679 | do_test e_fkey-39.2 { |
||
1680 | execsql { |
||
1681 | UPDATE p SET b = 'k4' WHERE a = 1; |
||
1682 | SELECT * FROM c1; |
||
1683 | } |
||
1684 | } {1 xx k0 2 xx k2 3 xx k3} |
||
1685 | do_test e_fkey-39.3 { |
||
1686 | execsql { |
||
1687 | DELETE FROM p WHERE a = 2; |
||
1688 | SELECT * FROM c1; |
||
1689 | } |
||
1690 | } {1 xx k0 2 xx {} 3 xx k3} |
||
1691 | do_test e_fkey-39.4 { |
||
1692 | execsql { |
||
1693 | CREATE UNIQUE INDEX pi ON p(c); |
||
1694 | REPLACE INTO p VALUES(5, 'k5', 'III'); |
||
1695 | SELECT * FROM c1; |
||
1696 | } |
||
1697 | } {1 xx k0 2 xx {} 3 xx {}} |
||
1698 | |||
1699 | #------------------------------------------------------------------------- |
||
1700 | # Each foreign key in the system has an ON UPDATE and ON DELETE action, |
||
1701 | # either "NO ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE". |
||
1702 | # |
||
1703 | # EVIDENCE-OF: R-33326-45252 The ON DELETE and ON UPDATE action |
||
1704 | # associated with each foreign key in an SQLite database is one of "NO |
||
1705 | # ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE". |
||
1706 | # |
||
1707 | # If none is specified explicitly, "NO ACTION" is the default. |
||
1708 | # |
||
1709 | # EVIDENCE-OF: R-19803-45884 If an action is not explicitly specified, |
||
1710 | # it defaults to "NO ACTION". |
||
1711 | # |
||
1712 | drop_all_tables |
||
1713 | do_test e_fkey-40.1 { |
||
1714 | execsql { |
||
1715 | CREATE TABLE parent(x PRIMARY KEY, y); |
||
1716 | CREATE TABLE child1(a, |
||
1717 | b REFERENCES parent ON UPDATE NO ACTION ON DELETE RESTRICT |
||
1718 | ); |
||
1719 | CREATE TABLE child2(a, |
||
1720 | b REFERENCES parent ON UPDATE RESTRICT ON DELETE SET NULL |
||
1721 | ); |
||
1722 | CREATE TABLE child3(a, |
||
1723 | b REFERENCES parent ON UPDATE SET NULL ON DELETE SET DEFAULT |
||
1724 | ); |
||
1725 | CREATE TABLE child4(a, |
||
1726 | b REFERENCES parent ON UPDATE SET DEFAULT ON DELETE CASCADE |
||
1727 | ); |
||
1728 | |||
1729 | -- Create some foreign keys that use the default action - "NO ACTION" |
||
1730 | CREATE TABLE child5(a, b REFERENCES parent ON UPDATE CASCADE); |
||
1731 | CREATE TABLE child6(a, b REFERENCES parent ON DELETE RESTRICT); |
||
1732 | CREATE TABLE child7(a, b REFERENCES parent ON DELETE NO ACTION); |
||
1733 | CREATE TABLE child8(a, b REFERENCES parent ON UPDATE NO ACTION); |
||
1734 | } |
||
1735 | } {} |
||
1736 | |||
1737 | foreach {tn zTab lRes} { |
||
1738 | 2 child1 {0 0 parent b {} {NO ACTION} RESTRICT NONE} |
||
1739 | 3 child2 {0 0 parent b {} RESTRICT {SET NULL} NONE} |
||
1740 | 4 child3 {0 0 parent b {} {SET NULL} {SET DEFAULT} NONE} |
||
1741 | 5 child4 {0 0 parent b {} {SET DEFAULT} CASCADE NONE} |
||
1742 | 6 child5 {0 0 parent b {} CASCADE {NO ACTION} NONE} |
||
1743 | 7 child6 {0 0 parent b {} {NO ACTION} RESTRICT NONE} |
||
1744 | 8 child7 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE} |
||
1745 | 9 child8 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE} |
||
1746 | } { |
||
1747 | do_test e_fkey-40.$tn { execsql "PRAGMA foreign_key_list($zTab)" } $lRes |
||
1748 | } |
||
1749 | |||
1750 | #------------------------------------------------------------------------- |
||
1751 | # Test that "NO ACTION" means that nothing happens to a child row when |
||
1752 | # it's parent row is updated or deleted. |
||
1753 | # |
||
1754 | # EVIDENCE-OF: R-19971-54976 Configuring "NO ACTION" means just that: |
||
1755 | # when a parent key is modified or deleted from the database, no special |
||
1756 | # action is taken. |
||
1757 | # |
||
1758 | drop_all_tables |
||
1759 | do_test e_fkey-41.1 { |
||
1760 | execsql { |
||
1761 | CREATE TABLE parent(p1, p2, PRIMARY KEY(p1, p2)); |
||
1762 | CREATE TABLE child(c1, c2, |
||
1763 | FOREIGN KEY(c1, c2) REFERENCES parent |
||
1764 | ON UPDATE NO ACTION |
||
1765 | ON DELETE NO ACTION |
||
1766 | DEFERRABLE INITIALLY DEFERRED |
||
1767 | ); |
||
1768 | INSERT INTO parent VALUES('j', 'k'); |
||
1769 | INSERT INTO parent VALUES('l', 'm'); |
||
1770 | INSERT INTO child VALUES('j', 'k'); |
||
1771 | INSERT INTO child VALUES('l', 'm'); |
||
1772 | } |
||
1773 | } {} |
||
1774 | do_test e_fkey-41.2 { |
||
1775 | execsql { |
||
1776 | BEGIN; |
||
1777 | UPDATE parent SET p1='k' WHERE p1='j'; |
||
1778 | DELETE FROM parent WHERE p1='l'; |
||
1779 | SELECT * FROM child; |
||
1780 | } |
||
1781 | } {j k l m} |
||
1782 | do_test e_fkey-41.3 { |
||
1783 | catchsql COMMIT |
||
1784 | } {1 {foreign key constraint failed}} |
||
1785 | do_test e_fkey-41.4 { |
||
1786 | execsql ROLLBACK |
||
1787 | } {} |
||
1788 | |||
1789 | #------------------------------------------------------------------------- |
||
1790 | # Test that "RESTRICT" means the application is prohibited from deleting |
||
1791 | # or updating a parent table row when there exists one or more child keys |
||
1792 | # mapped to it. |
||
1793 | # |
||
1794 | # EVIDENCE-OF: R-04272-38653 The "RESTRICT" action means that the |
||
1795 | # application is prohibited from deleting (for ON DELETE RESTRICT) or |
||
1796 | # modifying (for ON UPDATE RESTRICT) a parent key when there exists one |
||
1797 | # or more child keys mapped to it. |
||
1798 | # |
||
1799 | drop_all_tables |
||
1800 | do_test e_fkey-41.1 { |
||
1801 | execsql { |
||
1802 | CREATE TABLE parent(p1, p2); |
||
1803 | CREATE UNIQUE INDEX parent_i ON parent(p1, p2); |
||
1804 | CREATE TABLE child1(c1, c2, |
||
1805 | FOREIGN KEY(c2, c1) REFERENCES parent(p1, p2) ON DELETE RESTRICT |
||
1806 | ); |
||
1807 | CREATE TABLE child2(c1, c2, |
||
1808 | FOREIGN KEY(c2, c1) REFERENCES parent(p1, p2) ON UPDATE RESTRICT |
||
1809 | ); |
||
1810 | } |
||
1811 | } {} |
||
1812 | do_test e_fkey-41.2 { |
||
1813 | execsql { |
||
1814 | INSERT INTO parent VALUES('a', 'b'); |
||
1815 | INSERT INTO parent VALUES('c', 'd'); |
||
1816 | INSERT INTO child1 VALUES('b', 'a'); |
||
1817 | INSERT INTO child2 VALUES('d', 'c'); |
||
1818 | } |
||
1819 | } {} |
||
1820 | do_test e_fkey-41.3 { |
||
1821 | catchsql { DELETE FROM parent WHERE p1 = 'a' } |
||
1822 | } {1 {foreign key constraint failed}} |
||
1823 | do_test e_fkey-41.4 { |
||
1824 | catchsql { UPDATE parent SET p2 = 'e' WHERE p1 = 'c' } |
||
1825 | } {1 {foreign key constraint failed}} |
||
1826 | |||
1827 | #------------------------------------------------------------------------- |
||
1828 | # Test that RESTRICT is slightly different from NO ACTION for IMMEDIATE |
||
1829 | # constraints, in that it is enforced immediately, not at the end of the |
||
1830 | # statement. |
||
1831 | # |
||
1832 | # EVIDENCE-OF: R-37997-42187 The difference between the effect of a |
||
1833 | # RESTRICT action and normal foreign key constraint enforcement is that |
||
1834 | # the RESTRICT action processing happens as soon as the field is updated |
||
1835 | # - not at the end of the current statement as it would with an |
||
1836 | # immediate constraint, or at the end of the current transaction as it |
||
1837 | # would with a deferred constraint. |
||
1838 | # |
||
1839 | drop_all_tables |
||
1840 | do_test e_fkey-42.1 { |
||
1841 | execsql { |
||
1842 | CREATE TABLE parent(x PRIMARY KEY); |
||
1843 | CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT); |
||
1844 | CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION); |
||
1845 | |||
1846 | INSERT INTO parent VALUES('key1'); |
||
1847 | INSERT INTO parent VALUES('key2'); |
||
1848 | INSERT INTO child1 VALUES('key1'); |
||
1849 | INSERT INTO child2 VALUES('key2'); |
||
1850 | |||
1851 | CREATE TRIGGER parent_t AFTER UPDATE ON parent BEGIN |
||
1852 | UPDATE child1 set c = new.x WHERE c = old.x; |
||
1853 | UPDATE child2 set c = new.x WHERE c = old.x; |
||
1854 | END; |
||
1855 | } |
||
1856 | } {} |
||
1857 | do_test e_fkey-42.2 { |
||
1858 | catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' } |
||
1859 | } {1 {foreign key constraint failed}} |
||
1860 | do_test e_fkey-42.3 { |
||
1861 | execsql { |
||
1862 | UPDATE parent SET x = 'key two' WHERE x = 'key2'; |
||
1863 | SELECT * FROM child2; |
||
1864 | } |
||
1865 | } {{key two}} |
||
1866 | |||
1867 | drop_all_tables |
||
1868 | do_test e_fkey-42.4 { |
||
1869 | execsql { |
||
1870 | CREATE TABLE parent(x PRIMARY KEY); |
||
1871 | CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT); |
||
1872 | CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION); |
||
1873 | |||
1874 | INSERT INTO parent VALUES('key1'); |
||
1875 | INSERT INTO parent VALUES('key2'); |
||
1876 | INSERT INTO child1 VALUES('key1'); |
||
1877 | INSERT INTO child2 VALUES('key2'); |
||
1878 | |||
1879 | CREATE TRIGGER parent_t AFTER DELETE ON parent BEGIN |
||
1880 | UPDATE child1 SET c = NULL WHERE c = old.x; |
||
1881 | UPDATE child2 SET c = NULL WHERE c = old.x; |
||
1882 | END; |
||
1883 | } |
||
1884 | } {} |
||
1885 | do_test e_fkey-42.5 { |
||
1886 | catchsql { DELETE FROM parent WHERE x = 'key1' } |
||
1887 | } {1 {foreign key constraint failed}} |
||
1888 | do_test e_fkey-42.6 { |
||
1889 | execsql { |
||
1890 | DELETE FROM parent WHERE x = 'key2'; |
||
1891 | SELECT * FROM child2; |
||
1892 | } |
||
1893 | } {{}} |
||
1894 | |||
1895 | drop_all_tables |
||
1896 | do_test e_fkey-42.7 { |
||
1897 | execsql { |
||
1898 | CREATE TABLE parent(x PRIMARY KEY); |
||
1899 | CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT); |
||
1900 | CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION); |
||
1901 | |||
1902 | INSERT INTO parent VALUES('key1'); |
||
1903 | INSERT INTO parent VALUES('key2'); |
||
1904 | INSERT INTO child1 VALUES('key1'); |
||
1905 | INSERT INTO child2 VALUES('key2'); |
||
1906 | } |
||
1907 | } {} |
||
1908 | do_test e_fkey-42.8 { |
||
1909 | catchsql { REPLACE INTO parent VALUES('key1') } |
||
1910 | } {1 {foreign key constraint failed}} |
||
1911 | do_test e_fkey-42.9 { |
||
1912 | execsql { |
||
1913 | REPLACE INTO parent VALUES('key2'); |
||
1914 | SELECT * FROM child2; |
||
1915 | } |
||
1916 | } {key2} |
||
1917 | |||
1918 | #------------------------------------------------------------------------- |
||
1919 | # Test that RESTRICT is enforced immediately, even for a DEFERRED constraint. |
||
1920 | # |
||
1921 | # EVIDENCE-OF: R-24179-60523 Even if the foreign key constraint it is |
||
1922 | # attached to is deferred, configuring a RESTRICT action causes SQLite |
||
1923 | # to return an error immediately if a parent key with dependent child |
||
1924 | # keys is deleted or modified. |
||
1925 | # |
||
1926 | drop_all_tables |
||
1927 | do_test e_fkey-43.1 { |
||
1928 | execsql { |
||
1929 | CREATE TABLE parent(x PRIMARY KEY); |
||
1930 | CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT |
||
1931 | DEFERRABLE INITIALLY DEFERRED |
||
1932 | ); |
||
1933 | CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION |
||
1934 | DEFERRABLE INITIALLY DEFERRED |
||
1935 | ); |
||
1936 | |||
1937 | INSERT INTO parent VALUES('key1'); |
||
1938 | INSERT INTO parent VALUES('key2'); |
||
1939 | INSERT INTO child1 VALUES('key1'); |
||
1940 | INSERT INTO child2 VALUES('key2'); |
||
1941 | BEGIN; |
||
1942 | } |
||
1943 | } {} |
||
1944 | do_test e_fkey-43.2 { |
||
1945 | catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' } |
||
1946 | } {1 {foreign key constraint failed}} |
||
1947 | do_test e_fkey-43.3 { |
||
1948 | execsql { UPDATE parent SET x = 'key two' WHERE x = 'key2' } |
||
1949 | } {} |
||
1950 | do_test e_fkey-43.4 { |
||
1951 | catchsql COMMIT |
||
1952 | } {1 {foreign key constraint failed}} |
||
1953 | do_test e_fkey-43.5 { |
||
1954 | execsql { |
||
1955 | UPDATE child2 SET c = 'key two'; |
||
1956 | COMMIT; |
||
1957 | } |
||
1958 | } {} |
||
1959 | |||
1960 | drop_all_tables |
||
1961 | do_test e_fkey-43.6 { |
||
1962 | execsql { |
||
1963 | CREATE TABLE parent(x PRIMARY KEY); |
||
1964 | CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT |
||
1965 | DEFERRABLE INITIALLY DEFERRED |
||
1966 | ); |
||
1967 | CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION |
||
1968 | DEFERRABLE INITIALLY DEFERRED |
||
1969 | ); |
||
1970 | |||
1971 | INSERT INTO parent VALUES('key1'); |
||
1972 | INSERT INTO parent VALUES('key2'); |
||
1973 | INSERT INTO child1 VALUES('key1'); |
||
1974 | INSERT INTO child2 VALUES('key2'); |
||
1975 | BEGIN; |
||
1976 | } |
||
1977 | } {} |
||
1978 | do_test e_fkey-43.7 { |
||
1979 | catchsql { DELETE FROM parent WHERE x = 'key1' } |
||
1980 | } {1 {foreign key constraint failed}} |
||
1981 | do_test e_fkey-43.8 { |
||
1982 | execsql { DELETE FROM parent WHERE x = 'key2' } |
||
1983 | } {} |
||
1984 | do_test e_fkey-43.9 { |
||
1985 | catchsql COMMIT |
||
1986 | } {1 {foreign key constraint failed}} |
||
1987 | do_test e_fkey-43.10 { |
||
1988 | execsql { |
||
1989 | UPDATE child2 SET c = NULL; |
||
1990 | COMMIT; |
||
1991 | } |
||
1992 | } {} |
||
1993 | |||
1994 | #------------------------------------------------------------------------- |
||
1995 | # Test SET NULL actions. |
||
1996 | # |
||
1997 | # EVIDENCE-OF: R-03353-05327 If the configured action is "SET NULL", |
||
1998 | # then when a parent key is deleted (for ON DELETE SET NULL) or modified |
||
1999 | # (for ON UPDATE SET NULL), the child key columns of all rows in the |
||
2000 | # child table that mapped to the parent key are set to contain SQL NULL |
||
2001 | # values. |
||
2002 | # |
||
2003 | drop_all_tables |
||
2004 | do_test e_fkey-44.1 { |
||
2005 | execsql { |
||
2006 | CREATE TABLE pA(x PRIMARY KEY); |
||
2007 | CREATE TABLE cA(c REFERENCES pA ON DELETE SET NULL); |
||
2008 | CREATE TABLE cB(c REFERENCES pA ON UPDATE SET NULL); |
||
2009 | |||
2010 | INSERT INTO pA VALUES(X'ABCD'); |
||
2011 | INSERT INTO pA VALUES(X'1234'); |
||
2012 | INSERT INTO cA VALUES(X'ABCD'); |
||
2013 | INSERT INTO cB VALUES(X'1234'); |
||
2014 | } |
||
2015 | } {} |
||
2016 | do_test e_fkey-44.2 { |
||
2017 | execsql { |
||
2018 | DELETE FROM pA WHERE rowid = 1; |
||
2019 | SELECT quote(x) FROM pA; |
||
2020 | } |
||
2021 | } {X'1234'} |
||
2022 | do_test e_fkey-44.3 { |
||
2023 | execsql { |
||
2024 | SELECT quote(c) FROM cA; |
||
2025 | } |
||
2026 | } {NULL} |
||
2027 | do_test e_fkey-44.4 { |
||
2028 | execsql { |
||
2029 | UPDATE pA SET x = X'8765' WHERE rowid = 2; |
||
2030 | SELECT quote(x) FROM pA; |
||
2031 | } |
||
2032 | } {X'8765'} |
||
2033 | do_test e_fkey-44.5 { |
||
2034 | execsql { SELECT quote(c) FROM cB } |
||
2035 | } {NULL} |
||
2036 | |||
2037 | #------------------------------------------------------------------------- |
||
2038 | # Test SET DEFAULT actions. |
||
2039 | # |
||
2040 | # EVIDENCE-OF: R-43054-54832 The "SET DEFAULT" actions are similar to |
||
2041 | # "SET NULL", except that each of the child key columns is set to |
||
2042 | # contain the columns default value instead of NULL. |
||
2043 | # |
||
2044 | drop_all_tables |
||
2045 | do_test e_fkey-45.1 { |
||
2046 | execsql { |
||
2047 | CREATE TABLE pA(x PRIMARY KEY); |
||
2048 | CREATE TABLE cA(c DEFAULT X'0000' REFERENCES pA ON DELETE SET DEFAULT); |
||
2049 | CREATE TABLE cB(c DEFAULT X'9999' REFERENCES pA ON UPDATE SET DEFAULT); |
||
2050 | |||
2051 | INSERT INTO pA(rowid, x) VALUES(1, X'0000'); |
||
2052 | INSERT INTO pA(rowid, x) VALUES(2, X'9999'); |
||
2053 | INSERT INTO pA(rowid, x) VALUES(3, X'ABCD'); |
||
2054 | INSERT INTO pA(rowid, x) VALUES(4, X'1234'); |
||
2055 | |||
2056 | INSERT INTO cA VALUES(X'ABCD'); |
||
2057 | INSERT INTO cB VALUES(X'1234'); |
||
2058 | } |
||
2059 | } {} |
||
2060 | do_test e_fkey-45.2 { |
||
2061 | execsql { |
||
2062 | DELETE FROM pA WHERE rowid = 3; |
||
2063 | SELECT quote(x) FROM pA; |
||
2064 | } |
||
2065 | } {X'0000' X'9999' X'1234'} |
||
2066 | do_test e_fkey-45.3 { |
||
2067 | execsql { SELECT quote(c) FROM cA } |
||
2068 | } {X'0000'} |
||
2069 | do_test e_fkey-45.4 { |
||
2070 | execsql { |
||
2071 | UPDATE pA SET x = X'8765' WHERE rowid = 4; |
||
2072 | SELECT quote(x) FROM pA; |
||
2073 | } |
||
2074 | } {X'0000' X'9999' X'8765'} |
||
2075 | do_test e_fkey-45.5 { |
||
2076 | execsql { SELECT quote(c) FROM cB } |
||
2077 | } {X'9999'} |
||
2078 | |||
2079 | #------------------------------------------------------------------------- |
||
2080 | # Test ON DELETE CASCADE actions. |
||
2081 | # |
||
2082 | # EVIDENCE-OF: R-61376-57267 A "CASCADE" action propagates the delete or |
||
2083 | # update operation on the parent key to each dependent child key. |
||
2084 | # |
||
2085 | # EVIDENCE-OF: R-61809-62207 For an "ON DELETE CASCADE" action, this |
||
2086 | # means that each row in the child table that was associated with the |
||
2087 | # deleted parent row is also deleted. |
||
2088 | # |
||
2089 | drop_all_tables |
||
2090 | do_test e_fkey-46.1 { |
||
2091 | execsql { |
||
2092 | CREATE TABLE p1(a, b UNIQUE); |
||
2093 | CREATE TABLE c1(c REFERENCES p1(b) ON DELETE CASCADE, d); |
||
2094 | INSERT INTO p1 VALUES(NULL, NULL); |
||
2095 | INSERT INTO p1 VALUES(4, 4); |
||
2096 | INSERT INTO p1 VALUES(5, 5); |
||
2097 | INSERT INTO c1 VALUES(NULL, NULL); |
||
2098 | INSERT INTO c1 VALUES(4, 4); |
||
2099 | INSERT INTO c1 VALUES(5, 5); |
||
2100 | SELECT count(*) FROM c1; |
||
2101 | } |
||
2102 | } {3} |
||
2103 | do_test e_fkey-46.2 { |
||
2104 | execsql { |
||
2105 | DELETE FROM p1 WHERE a = 4; |
||
2106 | SELECT d, c FROM c1; |
||
2107 | } |
||
2108 | } {{} {} 5 5} |
||
2109 | do_test e_fkey-46.3 { |
||
2110 | execsql { |
||
2111 | DELETE FROM p1; |
||
2112 | SELECT d, c FROM c1; |
||
2113 | } |
||
2114 | } {{} {}} |
||
2115 | do_test e_fkey-46.4 { |
||
2116 | execsql { SELECT * FROM p1 } |
||
2117 | } {} |
||
2118 | |||
2119 | |||
2120 | #------------------------------------------------------------------------- |
||
2121 | # Test ON UPDATE CASCADE actions. |
||
2122 | # |
||
2123 | # EVIDENCE-OF: R-13877-64542 For an "ON UPDATE CASCADE" action, it means |
||
2124 | # that the values stored in each dependent child key are modified to |
||
2125 | # match the new parent key values. |
||
2126 | # |
||
2127 | # EVIDENCE-OF: R-61376-57267 A "CASCADE" action propagates the delete or |
||
2128 | # update operation on the parent key to each dependent child key. |
||
2129 | # |
||
2130 | drop_all_tables |
||
2131 | do_test e_fkey-47.1 { |
||
2132 | execsql { |
||
2133 | CREATE TABLE p1(a, b UNIQUE); |
||
2134 | CREATE TABLE c1(c REFERENCES p1(b) ON UPDATE CASCADE, d); |
||
2135 | INSERT INTO p1 VALUES(NULL, NULL); |
||
2136 | INSERT INTO p1 VALUES(4, 4); |
||
2137 | INSERT INTO p1 VALUES(5, 5); |
||
2138 | INSERT INTO c1 VALUES(NULL, NULL); |
||
2139 | INSERT INTO c1 VALUES(4, 4); |
||
2140 | INSERT INTO c1 VALUES(5, 5); |
||
2141 | SELECT count(*) FROM c1; |
||
2142 | } |
||
2143 | } {3} |
||
2144 | do_test e_fkey-47.2 { |
||
2145 | execsql { |
||
2146 | UPDATE p1 SET b = 10 WHERE b = 5; |
||
2147 | SELECT d, c FROM c1; |
||
2148 | } |
||
2149 | } {{} {} 4 4 5 10} |
||
2150 | do_test e_fkey-47.3 { |
||
2151 | execsql { |
||
2152 | UPDATE p1 SET b = 11 WHERE b = 4; |
||
2153 | SELECT d, c FROM c1; |
||
2154 | } |
||
2155 | } {{} {} 4 11 5 10} |
||
2156 | do_test e_fkey-47.4 { |
||
2157 | execsql { |
||
2158 | UPDATE p1 SET b = 6 WHERE b IS NULL; |
||
2159 | SELECT d, c FROM c1; |
||
2160 | } |
||
2161 | } {{} {} 4 11 5 10} |
||
2162 | do_test e_fkey-46.5 { |
||
2163 | execsql { SELECT * FROM p1 } |
||
2164 | } {{} 6 4 11 5 10} |
||
2165 | |||
2166 | #------------------------------------------------------------------------- |
||
2167 | # EVIDENCE-OF: R-65058-57158 |
||
2168 | # |
||
2169 | # Test an example from the "ON DELETE and ON UPDATE Actions" section |
||
2170 | # of foreignkeys.html. |
||
2171 | # |
||
2172 | drop_all_tables |
||
2173 | do_test e_fkey-48.1 { |
||
2174 | execsql { |
||
2175 | CREATE TABLE artist( |
||
2176 | artistid INTEGER PRIMARY KEY, |
||
2177 | artistname TEXT |
||
2178 | ); |
||
2179 | CREATE TABLE track( |
||
2180 | trackid INTEGER, |
||
2181 | trackname TEXT, |
||
2182 | trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE |
||
2183 | ); |
||
2184 | |||
2185 | INSERT INTO artist VALUES(1, 'Dean Martin'); |
||
2186 | INSERT INTO artist VALUES(2, 'Frank Sinatra'); |
||
2187 | INSERT INTO track VALUES(11, 'That''s Amore', 1); |
||
2188 | INSERT INTO track VALUES(12, 'Christmas Blues', 1); |
||
2189 | INSERT INTO track VALUES(13, 'My Way', 2); |
||
2190 | } |
||
2191 | } {} |
||
2192 | do_test e_fkey-48.2 { |
||
2193 | execsql { |
||
2194 | UPDATE artist SET artistid = 100 WHERE artistname = 'Dean Martin'; |
||
2195 | } |
||
2196 | } {} |
||
2197 | do_test e_fkey-48.3 { |
||
2198 | execsql { SELECT * FROM artist } |
||
2199 | } {2 {Frank Sinatra} 100 {Dean Martin}} |
||
2200 | do_test e_fkey-48.4 { |
||
2201 | execsql { SELECT * FROM track } |
||
2202 | } {11 {That's Amore} 100 12 {Christmas Blues} 100 13 {My Way} 2} |
||
2203 | |||
2204 | |||
2205 | #------------------------------------------------------------------------- |
||
2206 | # Verify that adding an FK action does not absolve the user of the |
||
2207 | # requirement not to violate the foreign key constraint. |
||
2208 | # |
||
2209 | # EVIDENCE-OF: R-53968-51642 Configuring an ON UPDATE or ON DELETE |
||
2210 | # action does not mean that the foreign key constraint does not need to |
||
2211 | # be satisfied. |
||
2212 | # |
||
2213 | drop_all_tables |
||
2214 | do_test e_fkey-49.1 { |
||
2215 | execsql { |
||
2216 | CREATE TABLE parent(a COLLATE nocase, b, c, PRIMARY KEY(c, a)); |
||
2217 | CREATE TABLE child(d DEFAULT 'a', e, f DEFAULT 'c', |
||
2218 | FOREIGN KEY(f, d) REFERENCES parent ON UPDATE SET DEFAULT |
||
2219 | ); |
||
2220 | |||
2221 | INSERT INTO parent VALUES('A', 'b', 'c'); |
||
2222 | INSERT INTO parent VALUES('ONE', 'two', 'three'); |
||
2223 | INSERT INTO child VALUES('one', 'two', 'three'); |
||
2224 | } |
||
2225 | } {} |
||
2226 | do_test e_fkey-49.2 { |
||
2227 | execsql { |
||
2228 | BEGIN; |
||
2229 | UPDATE parent SET a = '' WHERE a = 'oNe'; |
||
2230 | SELECT * FROM child; |
||
2231 | } |
||
2232 | } {a two c} |
||
2233 | do_test e_fkey-49.3 { |
||
2234 | execsql { |
||
2235 | ROLLBACK; |
||
2236 | DELETE FROM parent WHERE a = 'A'; |
||
2237 | SELECT * FROM parent; |
||
2238 | } |
||
2239 | } {ONE two three} |
||
2240 | do_test e_fkey-49.4 { |
||
2241 | catchsql { UPDATE parent SET a = '' WHERE a = 'oNe' } |
||
2242 | } {1 {foreign key constraint failed}} |
||
2243 | |||
2244 | |||
2245 | #------------------------------------------------------------------------- |
||
2246 | # EVIDENCE-OF: R-11856-19836 |
||
2247 | # |
||
2248 | # Test an example from the "ON DELETE and ON UPDATE Actions" section |
||
2249 | # of foreignkeys.html. This example shows that adding an "ON DELETE DEFAULT" |
||
2250 | # clause does not abrogate the need to satisfy the foreign key constraint |
||
2251 | # (R-28220-46694). |
||
2252 | # |
||
2253 | # EVIDENCE-OF: R-28220-46694 For example, if an "ON DELETE SET DEFAULT" |
||
2254 | # action is configured, but there is no row in the parent table that |
||
2255 | # corresponds to the default values of the child key columns, deleting a |
||
2256 | # parent key while dependent child keys exist still causes a foreign key |
||
2257 | # violation. |
||
2258 | # |
||
2259 | drop_all_tables |
||
2260 | do_test e_fkey-50.1 { |
||
2261 | execsql { |
||
2262 | CREATE TABLE artist( |
||
2263 | artistid INTEGER PRIMARY KEY, |
||
2264 | artistname TEXT |
||
2265 | ); |
||
2266 | CREATE TABLE track( |
||
2267 | trackid INTEGER, |
||
2268 | trackname TEXT, |
||
2269 | trackartist INTEGER DEFAULT 0 REFERENCES artist(artistid) ON DELETE SET DEFAULT |
||
2270 | ); |
||
2271 | INSERT INTO artist VALUES(3, 'Sammy Davis Jr.'); |
||
2272 | INSERT INTO track VALUES(14, 'Mr. Bojangles', 3); |
||
2273 | } |
||
2274 | } {} |
||
2275 | do_test e_fkey-50.2 { |
||
2276 | catchsql { DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.' } |
||
2277 | } {1 {foreign key constraint failed}} |
||
2278 | do_test e_fkey-50.3 { |
||
2279 | execsql { |
||
2280 | INSERT INTO artist VALUES(0, 'Unknown Artist'); |
||
2281 | DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.'; |
||
2282 | } |
||
2283 | } {} |
||
2284 | do_test e_fkey-50.4 { |
||
2285 | execsql { SELECT * FROM artist } |
||
2286 | } {0 {Unknown Artist}} |
||
2287 | do_test e_fkey-50.5 { |
||
2288 | execsql { SELECT * FROM track } |
||
2289 | } {14 {Mr. Bojangles} 0} |
||
2290 | |||
2291 | #------------------------------------------------------------------------- |
||
2292 | # EVIDENCE-OF: R-09564-22170 |
||
2293 | # |
||
2294 | # Check that the order of steps in an UPDATE or DELETE on a parent |
||
2295 | # table is as follows: |
||
2296 | # |
||
2297 | # 1. Execute applicable BEFORE trigger programs, |
||
2298 | # 2. Check local (non foreign key) constraints, |
||
2299 | # 3. Update or delete the row in the parent table, |
||
2300 | # 4. Perform any required foreign key actions, |
||
2301 | # 5. Execute applicable AFTER trigger programs. |
||
2302 | # |
||
2303 | drop_all_tables |
||
2304 | do_test e_fkey-51.1 { |
||
2305 | proc maxparent {args} { db one {SELECT max(x) FROM parent} } |
||
2306 | db func maxparent maxparent |
||
2307 | |||
2308 | execsql { |
||
2309 | CREATE TABLE parent(x PRIMARY KEY); |
||
2310 | |||
2311 | CREATE TRIGGER bu BEFORE UPDATE ON parent BEGIN |
||
2312 | INSERT INTO parent VALUES(new.x-old.x); |
||
2313 | END; |
||
2314 | CREATE TABLE child( |
||
2315 | a DEFAULT (maxparent()) REFERENCES parent ON UPDATE SET DEFAULT |
||
2316 | ); |
||
2317 | CREATE TRIGGER au AFTER UPDATE ON parent BEGIN |
||
2318 | INSERT INTO parent VALUES(new.x+old.x); |
||
2319 | END; |
||
2320 | |||
2321 | INSERT INTO parent VALUES(1); |
||
2322 | INSERT INTO child VALUES(1); |
||
2323 | } |
||
2324 | } {} |
||
2325 | do_test e_fkey-51.2 { |
||
2326 | execsql { |
||
2327 | UPDATE parent SET x = 22; |
||
2328 | SELECT * FROM parent UNION ALL SELECT 'xxx' UNION ALL SELECT a FROM child; |
||
2329 | } |
||
2330 | } {22 21 23 xxx 22} |
||
2331 | do_test e_fkey-51.3 { |
||
2332 | execsql { |
||
2333 | DELETE FROM child; |
||
2334 | DELETE FROM parent; |
||
2335 | INSERT INTO parent VALUES(-1); |
||
2336 | INSERT INTO child VALUES(-1); |
||
2337 | UPDATE parent SET x = 22; |
||
2338 | SELECT * FROM parent UNION ALL SELECT 'xxx' UNION ALL SELECT a FROM child; |
||
2339 | } |
||
2340 | } {22 23 21 xxx 23} |
||
2341 | |||
2342 | |||
2343 | #------------------------------------------------------------------------- |
||
2344 | # Verify that ON UPDATE actions only actually take place if the parent key |
||
2345 | # is set to a new value that is distinct from the old value. The default |
||
2346 | # collation sequence and affinity are used to determine if the new value |
||
2347 | # is 'distinct' from the old or not. |
||
2348 | # |
||
2349 | # EVIDENCE-OF: R-27383-10246 An ON UPDATE action is only taken if the |
||
2350 | # values of the parent key are modified so that the new parent key |
||
2351 | # values are not equal to the old. |
||
2352 | # |
||
2353 | drop_all_tables |
||
2354 | do_test e_fkey-52.1 { |
||
2355 | execsql { |
||
2356 | CREATE TABLE zeus(a INTEGER COLLATE NOCASE, b, PRIMARY KEY(a, b)); |
||
2357 | CREATE TABLE apollo(c, d, |
||
2358 | FOREIGN KEY(c, d) REFERENCES zeus ON UPDATE CASCADE |
||
2359 | ); |
||
2360 | INSERT INTO zeus VALUES('abc', 'xyz'); |
||
2361 | INSERT INTO apollo VALUES('ABC', 'xyz'); |
||
2362 | } |
||
2363 | execsql { |
||
2364 | UPDATE zeus SET a = 'aBc'; |
||
2365 | SELECT * FROM apollo; |
||
2366 | } |
||
2367 | } {ABC xyz} |
||
2368 | do_test e_fkey-52.2 { |
||
2369 | execsql { |
||
2370 | UPDATE zeus SET a = 1, b = 1; |
||
2371 | SELECT * FROM apollo; |
||
2372 | } |
||
2373 | } {1 1} |
||
2374 | do_test e_fkey-52.3 { |
||
2375 | execsql { |
||
2376 | UPDATE zeus SET a = 1, b = 1; |
||
2377 | SELECT typeof(c), c, typeof(d), d FROM apollo; |
||
2378 | } |
||
2379 | } {integer 1 integer 1} |
||
2380 | do_test e_fkey-52.4 { |
||
2381 | execsql { |
||
2382 | UPDATE zeus SET a = '1'; |
||
2383 | SELECT typeof(c), c, typeof(d), d FROM apollo; |
||
2384 | } |
||
2385 | } {integer 1 integer 1} |
||
2386 | do_test e_fkey-52.5 { |
||
2387 | execsql { |
||
2388 | UPDATE zeus SET b = '1'; |
||
2389 | SELECT typeof(c), c, typeof(d), d FROM apollo; |
||
2390 | } |
||
2391 | } {integer 1 text 1} |
||
2392 | do_test e_fkey-52.6 { |
||
2393 | execsql { |
||
2394 | UPDATE zeus SET b = NULL; |
||
2395 | SELECT typeof(c), c, typeof(d), d FROM apollo; |
||
2396 | } |
||
2397 | } {integer 1 null {}} |
||
2398 | |||
2399 | #------------------------------------------------------------------------- |
||
2400 | # EVIDENCE-OF: R-35129-58141 |
||
2401 | # |
||
2402 | # Test an example from the "ON DELETE and ON UPDATE Actions" section |
||
2403 | # of foreignkeys.html. This example demonstrates that ON UPDATE actions |
||
2404 | # only take place if at least one parent key column is set to a value |
||
2405 | # that is distinct from its previous value. |
||
2406 | # |
||
2407 | drop_all_tables |
||
2408 | do_test e_fkey-53.1 { |
||
2409 | execsql { |
||
2410 | CREATE TABLE parent(x PRIMARY KEY); |
||
2411 | CREATE TABLE child(y REFERENCES parent ON UPDATE SET NULL); |
||
2412 | INSERT INTO parent VALUES('key'); |
||
2413 | INSERT INTO child VALUES('key'); |
||
2414 | } |
||
2415 | } {} |
||
2416 | do_test e_fkey-53.2 { |
||
2417 | execsql { |
||
2418 | UPDATE parent SET x = 'key'; |
||
2419 | SELECT IFNULL(y, 'null') FROM child; |
||
2420 | } |
||
2421 | } {key} |
||
2422 | do_test e_fkey-53.3 { |
||
2423 | execsql { |
||
2424 | UPDATE parent SET x = 'key2'; |
||
2425 | SELECT IFNULL(y, 'null') FROM child; |
||
2426 | } |
||
2427 | } {null} |
||
2428 | |||
2429 | ########################################################################### |
||
2430 | ### SECTION 5: CREATE, ALTER and DROP TABLE commands |
||
2431 | ########################################################################### |
||
2432 | |||
2433 | #------------------------------------------------------------------------- |
||
2434 | # Test that parent keys are not checked when tables are created. |
||
2435 | # |
||
2436 | # EVIDENCE-OF: R-36018-21755 The parent key definitions of foreign key |
||
2437 | # constraints are not checked when a table is created. |
||
2438 | # |
||
2439 | # EVIDENCE-OF: R-25384-39337 There is nothing stopping the user from |
||
2440 | # creating a foreign key definition that refers to a parent table that |
||
2441 | # does not exist, or to parent key columns that do not exist or are not |
||
2442 | # collectively bound by a PRIMARY KEY or UNIQUE constraint. |
||
2443 | # |
||
2444 | # Child keys are checked to ensure all component columns exist. If parent |
||
2445 | # key columns are explicitly specified, SQLite checks to make sure there |
||
2446 | # are the same number of columns in the child and parent keys. (TODO: This |
||
2447 | # is tested but does not correspond to any testable statement.) |
||
2448 | # |
||
2449 | # Also test that the above statements are true regardless of whether or not |
||
2450 | # foreign keys are enabled: "A CREATE TABLE command operates the same whether |
||
2451 | # or not foreign key constraints are enabled." |
||
2452 | # |
||
2453 | # EVIDENCE-OF: R-08908-23439 A CREATE TABLE command operates the same |
||
2454 | # whether or not foreign key constraints are enabled. |
||
2455 | # |
||
2456 | foreach {tn zCreateTbl lRes} { |
||
2457 | 1 "CREATE TABLE t1(a, b REFERENCES t1)" {0 {}} |
||
2458 | 2 "CREATE TABLE t1(a, b REFERENCES t2)" {0 {}} |
||
2459 | 3 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t1)" {0 {}} |
||
2460 | 4 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2)" {0 {}} |
||
2461 | 5 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2)" {0 {}} |
||
2462 | 6 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2(n,d))" {0 {}} |
||
2463 | 7 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t1(a,b))" {0 {}} |
||
2464 | |||
2465 | A "CREATE TABLE t1(a, b, FOREIGN KEY(c,b) REFERENCES t2)" |
||
2466 | {1 {unknown column "c" in foreign key definition}} |
||
2467 | B "CREATE TABLE t1(a, b, FOREIGN KEY(c,b) REFERENCES t2(d))" |
||
2468 | {1 {number of columns in foreign key does not match the number of columns in the referenced table}} |
||
2469 | } { |
||
2470 | do_test e_fkey-54.$tn.off { |
||
2471 | drop_all_tables |
||
2472 | execsql {PRAGMA foreign_keys = OFF} |
||
2473 | catchsql $zCreateTbl |
||
2474 | } $lRes |
||
2475 | do_test e_fkey-54.$tn.on { |
||
2476 | drop_all_tables |
||
2477 | execsql {PRAGMA foreign_keys = ON} |
||
2478 | catchsql $zCreateTbl |
||
2479 | } $lRes |
||
2480 | } |
||
2481 | |||
2482 | #------------------------------------------------------------------------- |
||
2483 | # EVIDENCE-OF: R-47952-62498 It is not possible to use the "ALTER TABLE |
||
2484 | # ... ADD COLUMN" syntax to add a column that includes a REFERENCES |
||
2485 | # clause, unless the default value of the new column is NULL. Attempting |
||
2486 | # to do so returns an error. |
||
2487 | # |
||
2488 | proc test_efkey_6 {tn zAlter isError} { |
||
2489 | drop_all_tables |
||
2490 | |||
2491 | do_test e_fkey-56.$tn.1 " |
||
2492 | execsql { CREATE TABLE tbl(a, b) } |
||
2493 | [list catchsql $zAlter] |
||
2494 | " [lindex {{0 {}} {1 {Cannot add a REFERENCES column with non-NULL default value}}} $isError] |
||
2495 | |||
2496 | } |
||
2497 | |||
2498 | test_efkey_6 1 "ALTER TABLE tbl ADD COLUMN c REFERENCES xx" 0 |
||
2499 | test_efkey_6 2 "ALTER TABLE tbl ADD COLUMN c DEFAULT NULL REFERENCES xx" 0 |
||
2500 | test_efkey_6 3 "ALTER TABLE tbl ADD COLUMN c DEFAULT 0 REFERENCES xx" 1 |
||
2501 | |||
2502 | #------------------------------------------------------------------------- |
||
2503 | # Test that ALTER TABLE adjusts REFERENCES clauses when the parent table |
||
2504 | # is RENAMED. |
||
2505 | # |
||
2506 | # EVIDENCE-OF: R-47080-02069 If an "ALTER TABLE ... RENAME TO" command |
||
2507 | # is used to rename a table that is the parent table of one or more |
||
2508 | # foreign key constraints, the definitions of the foreign key |
||
2509 | # constraints are modified to refer to the parent table by its new name |
||
2510 | # |
||
2511 | # Test that these adjustments are visible in the sqlite_master table. |
||
2512 | # |
||
2513 | # EVIDENCE-OF: R-63827-54774 The text of the child CREATE TABLE |
||
2514 | # statement or statements stored in the sqlite_master table are modified |
||
2515 | # to reflect the new parent table name. |
||
2516 | # |
||
2517 | do_test e_fkey-56.1 { |
||
2518 | drop_all_tables |
||
2519 | execsql { |
||
2520 | CREATE TABLE 'p 1 "parent one"'(a REFERENCES 'p 1 "parent one"', b, PRIMARY KEY(b)); |
||
2521 | |||
2522 | CREATE TABLE c1(c, d REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE); |
||
2523 | CREATE TABLE c2(e, f, FOREIGN KEY(f) REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE); |
||
2524 | CREATE TABLE c3(e, 'f col 2', FOREIGN KEY('f col 2') REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE); |
||
2525 | |||
2526 | INSERT INTO 'p 1 "parent one"' VALUES(1, 1); |
||
2527 | INSERT INTO c1 VALUES(1, 1); |
||
2528 | INSERT INTO c2 VALUES(1, 1); |
||
2529 | INSERT INTO c3 VALUES(1, 1); |
||
2530 | |||
2531 | -- CREATE TABLE q(a, b, PRIMARY KEY(b)); |
||
2532 | } |
||
2533 | } {} |
||
2534 | do_test e_fkey-56.2 { |
||
2535 | execsql { ALTER TABLE 'p 1 "parent one"' RENAME TO p } |
||
2536 | } {} |
||
2537 | do_test e_fkey-56.3 { |
||
2538 | execsql { |
||
2539 | UPDATE p SET a = 'xxx', b = 'xxx'; |
||
2540 | SELECT * FROM p; |
||
2541 | SELECT * FROM c1; |
||
2542 | SELECT * FROM c2; |
||
2543 | SELECT * FROM c3; |
||
2544 | } |
||
2545 | } {xxx xxx 1 xxx 1 xxx 1 xxx} |
||
2546 | do_test e_fkey-56.4 { |
||
2547 | execsql { SELECT sql FROM sqlite_master WHERE type = 'table'} |
||
2548 | } [list \ |
||
2549 | {CREATE TABLE "p"(a REFERENCES "p", b, PRIMARY KEY(b))} \ |
||
2550 | {CREATE TABLE c1(c, d REFERENCES "p" ON UPDATE CASCADE)} \ |
||
2551 | {CREATE TABLE c2(e, f, FOREIGN KEY(f) REFERENCES "p" ON UPDATE CASCADE)} \ |
||
2552 | {CREATE TABLE c3(e, 'f col 2', FOREIGN KEY('f col 2') REFERENCES "p" ON UPDATE CASCADE)} \ |
||
2553 | ] |
||
2554 | |||
2555 | #------------------------------------------------------------------------- |
||
2556 | # Check that a DROP TABLE does an implicit DELETE FROM. Which does not |
||
2557 | # cause any triggers to fire, but does fire foreign key actions. |
||
2558 | # |
||
2559 | # EVIDENCE-OF: R-14208-23986 If foreign key constraints are enabled when |
||
2560 | # it is prepared, the DROP TABLE command performs an implicit DELETE to |
||
2561 | # remove all rows from the table before dropping it. |
||
2562 | # |
||
2563 | # EVIDENCE-OF: R-11078-03945 The implicit DELETE does not cause any SQL |
||
2564 | # triggers to fire, but may invoke foreign key actions or constraint |
||
2565 | # violations. |
||
2566 | # |
||
2567 | do_test e_fkey-57.1 { |
||
2568 | drop_all_tables |
||
2569 | execsql { |
||
2570 | CREATE TABLE p(a, b, PRIMARY KEY(a, b)); |
||
2571 | |||
2572 | CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE SET NULL); |
||
2573 | CREATE TABLE c2(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE SET DEFAULT); |
||
2574 | CREATE TABLE c3(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE CASCADE); |
||
2575 | CREATE TABLE c4(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE RESTRICT); |
||
2576 | CREATE TABLE c5(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE NO ACTION); |
||
2577 | |||
2578 | CREATE TABLE c6(c, d, |
||
2579 | FOREIGN KEY(c, d) REFERENCES p ON DELETE RESTRICT |
||
2580 | DEFERRABLE INITIALLY DEFERRED |
||
2581 | ); |
||
2582 | CREATE TABLE c7(c, d, |
||
2583 | FOREIGN KEY(c, d) REFERENCES p ON DELETE NO ACTION |
||
2584 | DEFERRABLE INITIALLY DEFERRED |
||
2585 | ); |
||
2586 | |||
2587 | CREATE TABLE log(msg); |
||
2588 | CREATE TRIGGER tt AFTER DELETE ON p BEGIN |
||
2589 | INSERT INTO log VALUES('delete ' || old.rowid); |
||
2590 | END; |
||
2591 | } |
||
2592 | } {} |
||
2593 | |||
2594 | do_test e_fkey-57.2 { |
||
2595 | execsql { |
||
2596 | INSERT INTO p VALUES('a', 'b'); |
||
2597 | INSERT INTO c1 VALUES('a', 'b'); |
||
2598 | INSERT INTO c2 VALUES('a', 'b'); |
||
2599 | INSERT INTO c3 VALUES('a', 'b'); |
||
2600 | BEGIN; |
||
2601 | DROP TABLE p; |
||
2602 | SELECT * FROM c1; |
||
2603 | } |
||
2604 | } {{} {}} |
||
2605 | do_test e_fkey-57.3 { |
||
2606 | execsql { SELECT * FROM c2 } |
||
2607 | } {{} {}} |
||
2608 | do_test e_fkey-57.4 { |
||
2609 | execsql { SELECT * FROM c3 } |
||
2610 | } {} |
||
2611 | do_test e_fkey-57.5 { |
||
2612 | execsql { SELECT * FROM log } |
||
2613 | } {} |
||
2614 | do_test e_fkey-57.6 { |
||
2615 | execsql ROLLBACK |
||
2616 | } {} |
||
2617 | do_test e_fkey-57.7 { |
||
2618 | execsql { |
||
2619 | BEGIN; |
||
2620 | DELETE FROM p; |
||
2621 | SELECT * FROM log; |
||
2622 | ROLLBACK; |
||
2623 | } |
||
2624 | } {{delete 1}} |
||
2625 | |||
2626 | #------------------------------------------------------------------------- |
||
2627 | # If an IMMEDIATE foreign key fails as a result of a DROP TABLE, the |
||
2628 | # DROP TABLE command fails. |
||
2629 | # |
||
2630 | # EVIDENCE-OF: R-32768-47925 If an immediate foreign key constraint is |
||
2631 | # violated, the DROP TABLE statement fails and the table is not dropped. |
||
2632 | # |
||
2633 | do_test e_fkey-58.1 { |
||
2634 | execsql { |
||
2635 | DELETE FROM c1; |
||
2636 | DELETE FROM c2; |
||
2637 | DELETE FROM c3; |
||
2638 | } |
||
2639 | execsql { INSERT INTO c5 VALUES('a', 'b') } |
||
2640 | catchsql { DROP TABLE p } |
||
2641 | } {1 {foreign key constraint failed}} |
||
2642 | do_test e_fkey-58.2 { |
||
2643 | execsql { SELECT * FROM p } |
||
2644 | } {a b} |
||
2645 | do_test e_fkey-58.3 { |
||
2646 | catchsql { |
||
2647 | BEGIN; |
||
2648 | DROP TABLE p; |
||
2649 | } |
||
2650 | } {1 {foreign key constraint failed}} |
||
2651 | do_test e_fkey-58.4 { |
||
2652 | execsql { |
||
2653 | SELECT * FROM p; |
||
2654 | SELECT * FROM c5; |
||
2655 | ROLLBACK; |
||
2656 | } |
||
2657 | } {a b a b} |
||
2658 | |||
2659 | #------------------------------------------------------------------------- |
||
2660 | # If a DEFERRED foreign key fails as a result of a DROP TABLE, attempting |
||
2661 | # to commit the transaction fails unless the violation is fixed. |
||
2662 | # |
||
2663 | # EVIDENCE-OF: R-05903-08460 If a deferred foreign key constraint is |
||
2664 | # violated, then an error is reported when the user attempts to commit |
||
2665 | # the transaction if the foreign key constraint violations still exist |
||
2666 | # at that point. |
||
2667 | # |
||
2668 | do_test e_fkey-59.1 { |
||
2669 | execsql { |
||
2670 | DELETE FROM c1 ; DELETE FROM c2 ; DELETE FROM c3 ; |
||
2671 | DELETE FROM c4 ; DELETE FROM c5 ; DELETE FROM c6 ; |
||
2672 | DELETE FROM c7 |
||
2673 | } |
||
2674 | } {} |
||
2675 | do_test e_fkey-59.2 { |
||
2676 | execsql { INSERT INTO c7 VALUES('a', 'b') } |
||
2677 | execsql { |
||
2678 | BEGIN; |
||
2679 | DROP TABLE p; |
||
2680 | } |
||
2681 | } {} |
||
2682 | do_test e_fkey-59.3 { |
||
2683 | catchsql COMMIT |
||
2684 | } {1 {foreign key constraint failed}} |
||
2685 | do_test e_fkey-59.4 { |
||
2686 | execsql { CREATE TABLE p(a, b, PRIMARY KEY(a, b)) } |
||
2687 | catchsql COMMIT |
||
2688 | } {1 {foreign key constraint failed}} |
||
2689 | do_test e_fkey-59.5 { |
||
2690 | execsql { INSERT INTO p VALUES('a', 'b') } |
||
2691 | execsql COMMIT |
||
2692 | } {} |
||
2693 | |||
2694 | #------------------------------------------------------------------------- |
||
2695 | # Any "foreign key mismatch" errors encountered while running an implicit |
||
2696 | # "DELETE FROM tbl" are ignored. |
||
2697 | # |
||
2698 | # EVIDENCE-OF: R-57242-37005 Any "foreign key mismatch" errors |
||
2699 | # encountered as part of an implicit DELETE are ignored. |
||
2700 | # |
||
2701 | drop_all_tables |
||
2702 | do_test e_fkey-60.1 { |
||
2703 | execsql { |
||
2704 | PRAGMA foreign_keys = OFF; |
||
2705 | |||
2706 | CREATE TABLE p(a PRIMARY KEY, b REFERENCES nosuchtable); |
||
2707 | CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES a); |
||
2708 | CREATE TABLE c2(c REFERENCES p(b), d); |
||
2709 | CREATE TABLE c3(c REFERENCES p ON DELETE SET NULL, d); |
||
2710 | |||
2711 | INSERT INTO p VALUES(1, 2); |
||
2712 | INSERT INTO c1 VALUES(1, 2); |
||
2713 | INSERT INTO c2 VALUES(1, 2); |
||
2714 | INSERT INTO c3 VALUES(1, 2); |
||
2715 | } |
||
2716 | } {} |
||
2717 | do_test e_fkey-60.2 { |
||
2718 | execsql { PRAGMA foreign_keys = ON } |
||
2719 | catchsql { DELETE FROM p } |
||
2720 | } {1 {no such table: main.nosuchtable}} |
||
2721 | do_test e_fkey-60.3 { |
||
2722 | execsql { |
||
2723 | BEGIN; |
||
2724 | DROP TABLE p; |
||
2725 | SELECT * FROM c3; |
||
2726 | ROLLBACK; |
||
2727 | } |
||
2728 | } {{} 2} |
||
2729 | do_test e_fkey-60.4 { |
||
2730 | execsql { CREATE TABLE nosuchtable(x PRIMARY KEY) } |
||
2731 | catchsql { DELETE FROM p } |
||
2732 | } {1 {foreign key mismatch}} |
||
2733 | do_test e_fkey-60.5 { |
||
2734 | execsql { DROP TABLE c1 } |
||
2735 | catchsql { DELETE FROM p } |
||
2736 | } {1 {foreign key mismatch}} |
||
2737 | do_test e_fkey-60.6 { |
||
2738 | execsql { DROP TABLE c2 } |
||
2739 | execsql { DELETE FROM p } |
||
2740 | } {} |
||
2741 | |||
2742 | #------------------------------------------------------------------------- |
||
2743 | # Test that the special behaviours of ALTER and DROP TABLE are only |
||
2744 | # activated when foreign keys are enabled. Special behaviours are: |
||
2745 | # |
||
2746 | # 1. ADD COLUMN not allowing a REFERENCES clause with a non-NULL |
||
2747 | # default value. |
||
2748 | # 2. Modifying foreign key definitions when a parent table is RENAMEd. |
||
2749 | # 3. Running an implicit DELETE FROM command as part of DROP TABLE. |
||
2750 | # |
||
2751 | # EVIDENCE-OF: R-54142-41346 The properties of the DROP TABLE and ALTER |
||
2752 | # TABLE commands described above only apply if foreign keys are enabled. |
||
2753 | # |
||
2754 | do_test e_fkey-61.1.1 { |
||
2755 | drop_all_tables |
||
2756 | execsql { CREATE TABLE t1(a, b) } |
||
2757 | catchsql { ALTER TABLE t1 ADD COLUMN c DEFAULT 'xxx' REFERENCES t2 } |
||
2758 | } {1 {Cannot add a REFERENCES column with non-NULL default value}} |
||
2759 | do_test e_fkey-61.1.2 { |
||
2760 | execsql { PRAGMA foreign_keys = OFF } |
||
2761 | execsql { ALTER TABLE t1 ADD COLUMN c DEFAULT 'xxx' REFERENCES t2 } |
||
2762 | execsql { SELECT sql FROM sqlite_master WHERE name = 't1' } |
||
2763 | } {{CREATE TABLE t1(a, b, c DEFAULT 'xxx' REFERENCES t2)}} |
||
2764 | do_test e_fkey-61.1.3 { |
||
2765 | execsql { PRAGMA foreign_keys = ON } |
||
2766 | } {} |
||
2767 | |||
2768 | do_test e_fkey-61.2.1 { |
||
2769 | drop_all_tables |
||
2770 | execsql { |
||
2771 | CREATE TABLE p(a UNIQUE); |
||
2772 | CREATE TABLE c(b REFERENCES p(a)); |
||
2773 | BEGIN; |
||
2774 | ALTER TABLE p RENAME TO parent; |
||
2775 | SELECT sql FROM sqlite_master WHERE name = 'c'; |
||
2776 | ROLLBACK; |
||
2777 | } |
||
2778 | } {{CREATE TABLE c(b REFERENCES "parent"(a))}} |
||
2779 | do_test e_fkey-61.2.2 { |
||
2780 | execsql { |
||
2781 | PRAGMA foreign_keys = OFF; |
||
2782 | ALTER TABLE p RENAME TO parent; |
||
2783 | SELECT sql FROM sqlite_master WHERE name = 'c'; |
||
2784 | } |
||
2785 | } {{CREATE TABLE c(b REFERENCES p(a))}} |
||
2786 | do_test e_fkey-61.2.3 { |
||
2787 | execsql { PRAGMA foreign_keys = ON } |
||
2788 | } {} |
||
2789 | |||
2790 | do_test e_fkey-61.3.1 { |
||
2791 | drop_all_tables |
||
2792 | execsql { |
||
2793 | CREATE TABLE p(a UNIQUE); |
||
2794 | CREATE TABLE c(b REFERENCES p(a) ON DELETE SET NULL); |
||
2795 | INSERT INTO p VALUES('x'); |
||
2796 | INSERT INTO c VALUES('x'); |
||
2797 | BEGIN; |
||
2798 | DROP TABLE p; |
||
2799 | SELECT * FROM c; |
||
2800 | ROLLBACK; |
||
2801 | } |
||
2802 | } {{}} |
||
2803 | do_test e_fkey-61.3.2 { |
||
2804 | execsql { |
||
2805 | PRAGMA foreign_keys = OFF; |
||
2806 | DROP TABLE p; |
||
2807 | SELECT * FROM c; |
||
2808 | } |
||
2809 | } {x} |
||
2810 | do_test e_fkey-61.3.3 { |
||
2811 | execsql { PRAGMA foreign_keys = ON } |
||
2812 | } {} |
||
2813 | |||
2814 | ########################################################################### |
||
2815 | ### SECTION 6: Limits and Unsupported Features |
||
2816 | ########################################################################### |
||
2817 | |||
2818 | #------------------------------------------------------------------------- |
||
2819 | # Test that MATCH clauses are parsed, but SQLite treats every foreign key |
||
2820 | # constraint as if it were "MATCH SIMPLE". |
||
2821 | # |
||
2822 | # EVIDENCE-OF: R-24728-13230 SQLite parses MATCH clauses (i.e. does not |
||
2823 | # report a syntax error if you specify one), but does not enforce them. |
||
2824 | # |
||
2825 | # EVIDENCE-OF: R-24450-46174 All foreign key constraints in SQLite are |
||
2826 | # handled as if MATCH SIMPLE were specified. |
||
2827 | # |
||
2828 | foreach zMatch [list SIMPLE PARTIAL FULL Simple parTIAL FuLL ] { |
||
2829 | drop_all_tables |
||
2830 | do_test e_fkey-62.$zMatch.1 { |
||
2831 | execsql " |
||
2832 | CREATE TABLE p(a, b, c, PRIMARY KEY(b, c)); |
||
2833 | CREATE TABLE c(d, e, f, FOREIGN KEY(e, f) REFERENCES p MATCH $zMatch); |
||
2834 | " |
||
2835 | } {} |
||
2836 | do_test e_fkey-62.$zMatch.2 { |
||
2837 | execsql { INSERT INTO p VALUES(1, 2, 3) } |
||
2838 | |||
2839 | # MATCH SIMPLE behaviour: Allow any child key that contains one or more |
||
2840 | # NULL value to be inserted. Non-NULL values do not have to map to any |
||
2841 | # parent key values, so long as at least one field of the child key is |
||
2842 | # NULL. |
||
2843 | execsql { INSERT INTO c VALUES('w', 2, 3) } |
||
2844 | execsql { INSERT INTO c VALUES('x', 'x', NULL) } |
||
2845 | execsql { INSERT INTO c VALUES('y', NULL, 'x') } |
||
2846 | execsql { INSERT INTO c VALUES('z', NULL, NULL) } |
||
2847 | |||
2848 | # Check that the FK is enforced properly if there are no NULL values |
||
2849 | # in the child key columns. |
||
2850 | catchsql { INSERT INTO c VALUES('a', 2, 4) } |
||
2851 | } {1 {foreign key constraint failed}} |
||
2852 | } |
||
2853 | |||
2854 | #------------------------------------------------------------------------- |
||
2855 | # Test that SQLite does not support the SET CONSTRAINT statement. And |
||
2856 | # that it is possible to create both immediate and deferred constraints. |
||
2857 | # |
||
2858 | # EVIDENCE-OF: R-21599-16038 In SQLite, a foreign key constraint is |
||
2859 | # permanently marked as deferred or immediate when it is created. |
||
2860 | # |
||
2861 | drop_all_tables |
||
2862 | do_test e_fkey-62.1 { |
||
2863 | catchsql { SET CONSTRAINTS ALL IMMEDIATE } |
||
2864 | } {1 {near "SET": syntax error}} |
||
2865 | do_test e_fkey-62.2 { |
||
2866 | catchsql { SET CONSTRAINTS ALL DEFERRED } |
||
2867 | } {1 {near "SET": syntax error}} |
||
2868 | |||
2869 | do_test e_fkey-62.3 { |
||
2870 | execsql { |
||
2871 | CREATE TABLE p(a, b, PRIMARY KEY(a, b)); |
||
2872 | CREATE TABLE cd(c, d, |
||
2873 | FOREIGN KEY(c, d) REFERENCES p DEFERRABLE INITIALLY DEFERRED); |
||
2874 | CREATE TABLE ci(c, d, |
||
2875 | FOREIGN KEY(c, d) REFERENCES p DEFERRABLE INITIALLY IMMEDIATE); |
||
2876 | BEGIN; |
||
2877 | } |
||
2878 | } {} |
||
2879 | do_test e_fkey-62.4 { |
||
2880 | catchsql { INSERT INTO ci VALUES('x', 'y') } |
||
2881 | } {1 {foreign key constraint failed}} |
||
2882 | do_test e_fkey-62.5 { |
||
2883 | catchsql { INSERT INTO cd VALUES('x', 'y') } |
||
2884 | } {0 {}} |
||
2885 | do_test e_fkey-62.6 { |
||
2886 | catchsql { COMMIT } |
||
2887 | } {1 {foreign key constraint failed}} |
||
2888 | do_test e_fkey-62.7 { |
||
2889 | execsql { |
||
2890 | DELETE FROM cd; |
||
2891 | COMMIT; |
||
2892 | } |
||
2893 | } {} |
||
2894 | |||
2895 | #------------------------------------------------------------------------- |
||
2896 | # Test that the maximum recursion depth of foreign key action programs is |
||
2897 | # governed by the SQLITE_MAX_TRIGGER_DEPTH and SQLITE_LIMIT_TRIGGER_DEPTH |
||
2898 | # settings. |
||
2899 | # |
||
2900 | # EVIDENCE-OF: R-42264-30503 The SQLITE_MAX_TRIGGER_DEPTH and |
||
2901 | # SQLITE_LIMIT_TRIGGER_DEPTH settings determine the maximum allowable |
||
2902 | # depth of trigger program recursion. For the purposes of these limits, |
||
2903 | # foreign key actions are considered trigger programs. |
||
2904 | # |
||
2905 | proc test_on_delete_recursion {limit} { |
||
2906 | drop_all_tables |
||
2907 | execsql { |
||
2908 | BEGIN; |
||
2909 | CREATE TABLE t0(a PRIMARY KEY, b); |
||
2910 | INSERT INTO t0 VALUES('x0', NULL); |
||
2911 | } |
||
2912 | for {set i 1} {$i <= $limit} {incr i} { |
||
2913 | execsql " |
||
2914 | CREATE TABLE t$i ( |
||
2915 | a PRIMARY KEY, b REFERENCES t[expr $i-1] ON DELETE CASCADE |
||
2916 | ); |
||
2917 | INSERT INTO t$i VALUES('x$i', 'x[expr $i-1]'); |
||
2918 | " |
||
2919 | } |
||
2920 | execsql COMMIT |
||
2921 | catchsql " |
||
2922 | DELETE FROM t0; |
||
2923 | SELECT count(*) FROM t$limit; |
||
2924 | " |
||
2925 | } |
||
2926 | proc test_on_update_recursion {limit} { |
||
2927 | drop_all_tables |
||
2928 | execsql { |
||
2929 | BEGIN; |
||
2930 | CREATE TABLE t0(a PRIMARY KEY); |
||
2931 | INSERT INTO t0 VALUES('xxx'); |
||
2932 | } |
||
2933 | for {set i 1} {$i <= $limit} {incr i} { |
||
2934 | set j [expr $i-1] |
||
2935 | |||
2936 | execsql " |
||
2937 | CREATE TABLE t$i (a PRIMARY KEY REFERENCES t$j ON UPDATE CASCADE); |
||
2938 | INSERT INTO t$i VALUES('xxx'); |
||
2939 | " |
||
2940 | } |
||
2941 | execsql COMMIT |
||
2942 | catchsql " |
||
2943 | UPDATE t0 SET a = 'yyy'; |
||
2944 | SELECT NOT (a='yyy') FROM t$limit; |
||
2945 | " |
||
2946 | } |
||
2947 | |||
2948 | do_test e_fkey-63.1.1 { |
||
2949 | test_on_delete_recursion $SQLITE_MAX_TRIGGER_DEPTH |
||
2950 | } {0 0} |
||
2951 | do_test e_fkey-63.1.2 { |
||
2952 | test_on_delete_recursion [expr $SQLITE_MAX_TRIGGER_DEPTH+1] |
||
2953 | } {1 {too many levels of trigger recursion}} |
||
2954 | do_test e_fkey-63.1.3 { |
||
2955 | sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 5 |
||
2956 | test_on_delete_recursion 5 |
||
2957 | } {0 0} |
||
2958 | do_test e_fkey-63.1.4 { |
||
2959 | test_on_delete_recursion 6 |
||
2960 | } {1 {too many levels of trigger recursion}} |
||
2961 | do_test e_fkey-63.1.5 { |
||
2962 | sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000 |
||
2963 | } {5} |
||
2964 | do_test e_fkey-63.2.1 { |
||
2965 | test_on_update_recursion $SQLITE_MAX_TRIGGER_DEPTH |
||
2966 | } {0 0} |
||
2967 | do_test e_fkey-63.2.2 { |
||
2968 | test_on_update_recursion [expr $SQLITE_MAX_TRIGGER_DEPTH+1] |
||
2969 | } {1 {too many levels of trigger recursion}} |
||
2970 | do_test e_fkey-63.2.3 { |
||
2971 | sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 5 |
||
2972 | test_on_update_recursion 5 |
||
2973 | } {0 0} |
||
2974 | do_test e_fkey-63.2.4 { |
||
2975 | test_on_update_recursion 6 |
||
2976 | } {1 {too many levels of trigger recursion}} |
||
2977 | do_test e_fkey-63.2.5 { |
||
2978 | sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000 |
||
2979 | } {5} |
||
2980 | |||
2981 | #------------------------------------------------------------------------- |
||
2982 | # The setting of the recursive_triggers pragma does not affect foreign |
||
2983 | # key actions. |
||
2984 | # |
||
2985 | # EVIDENCE-OF: R-51769-32730 The PRAGMA recursive_triggers setting does |
||
2986 | # not not affect the operation of foreign key actions. |
||
2987 | # |
||
2988 | foreach recursive_triggers_setting [list 0 1 ON OFF] { |
||
2989 | drop_all_tables |
||
2990 | execsql "PRAGMA recursive_triggers = $recursive_triggers_setting" |
||
2991 | |||
2992 | do_test e_fkey-64.$recursive_triggers_setting.1 { |
||
2993 | execsql { |
||
2994 | CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1 ON DELETE CASCADE); |
||
2995 | INSERT INTO t1 VALUES(1, NULL); |
||
2996 | INSERT INTO t1 VALUES(2, 1); |
||
2997 | INSERT INTO t1 VALUES(3, 2); |
||
2998 | INSERT INTO t1 VALUES(4, 3); |
||
2999 | INSERT INTO t1 VALUES(5, 4); |
||
3000 | SELECT count(*) FROM t1; |
||
3001 | } |
||
3002 | } {5} |
||
3003 | do_test e_fkey-64.$recursive_triggers_setting.2 { |
||
3004 | execsql { SELECT count(*) FROM t1 WHERE a = 1 } |
||
3005 | } {1} |
||
3006 | do_test e_fkey-64.$recursive_triggers_setting.3 { |
||
3007 | execsql { |
||
3008 | DELETE FROM t1 WHERE a = 1; |
||
3009 | SELECT count(*) FROM t1; |
||
3010 | } |
||
3011 | } {0} |
||
3012 | } |
||
3013 | |||
3014 | finish_test |