wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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