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