Debian Codegraph @

test/e_fkey.test

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