000001 # 2009 October 7
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 #
000012 # This file implements tests to verify the "testable statements" in the
000013 # foreignkeys.in document.
000014 #
000015 # The tests in this file are arranged to mirror the structure of
000016 # foreignkey.in, with one exception: The statements in section 2, which
000017 # deals with enabling/disabling foreign key support, is tested first,
000018 # before section 1. This is because some statements in section 2 deal
000019 # with builds that do not include complete foreign key support (because
000020 # either SQLITE_OMIT_TRIGGER or SQLITE_OMIT_FOREIGN_KEY was defined
000021 # at build time).
000022 #
000023
000024 set testdir [file dirname $argv0]
000025 source $testdir/tester.tcl
000026
000027 proc eqp {sql {db db}} {
000028 uplevel [subst -nocommands {
000029 set eqpres [list]
000030 $db eval "$sql" {
000031 lappend eqpres [set detail]
000032 }
000033 set eqpres
000034 }]
000035 }
000036
000037 proc do_detail_test {tn sql res} {
000038 set normalres [list {*}$res]
000039 uplevel [subst -nocommands {
000040 do_test $tn {
000041 eqp { $sql }
000042 } {$normalres}
000043 }]
000044 }
000045
000046 ###########################################################################
000047 ### SECTION 2: Enabling Foreign Key Support
000048 ###########################################################################
000049
000050 #-------------------------------------------------------------------------
000051 # EVIDENCE-OF: R-37672-59189 In order to use foreign key constraints in
000052 # SQLite, the library must be compiled with neither
000053 # SQLITE_OMIT_FOREIGN_KEY nor SQLITE_OMIT_TRIGGER defined.
000054 #
000055 ifcapable trigger&&foreignkey {
000056 do_test e_fkey-1 {
000057 execsql {
000058 PRAGMA foreign_keys = ON;
000059 CREATE TABLE p(i PRIMARY KEY);
000060 CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);
000061 INSERT INTO p VALUES('hello');
000062 INSERT INTO c VALUES('hello');
000063 UPDATE p SET i = 'world';
000064 SELECT * FROM c;
000065 }
000066 } {world}
000067 }
000068
000069 #-------------------------------------------------------------------------
000070 # Test the effects of defining OMIT_TRIGGER but not OMIT_FOREIGN_KEY.
000071 #
000072 # EVIDENCE-OF: R-10109-20452 If SQLITE_OMIT_TRIGGER is defined but
000073 # SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to
000074 # version 3.6.19 (2009-10-14) - foreign key definitions are parsed and
000075 # may be queried using PRAGMA foreign_key_list, but foreign key
000076 # constraints are not enforced.
000077 #
000078 # Specifically, test that "PRAGMA foreign_keys" is a no-op in this case.
000079 # When using the pragma to query the current setting, 0 rows are returned.
000080 #
000081 # EVIDENCE-OF: R-22567-44039 The PRAGMA foreign_keys command is a no-op
000082 # in this configuration.
000083 #
000084 # EVIDENCE-OF: R-41784-13339 Tip: If the command "PRAGMA foreign_keys"
000085 # returns no data instead of a single row containing "0" or "1", then
000086 # the version of SQLite you are using does not support foreign keys
000087 # (either because it is older than 3.6.19 or because it was compiled
000088 # with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).
000089 #
000090 reset_db
000091 ifcapable !trigger&&foreignkey {
000092 do_test e_fkey-2.1 {
000093 execsql {
000094 PRAGMA foreign_keys = ON;
000095 CREATE TABLE p(i PRIMARY KEY);
000096 CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);
000097 INSERT INTO p VALUES('hello');
000098 INSERT INTO c VALUES('hello');
000099 UPDATE p SET i = 'world';
000100 SELECT * FROM c;
000101 }
000102 } {hello}
000103 do_test e_fkey-2.2 {
000104 execsql { PRAGMA foreign_key_list(c) }
000105 } {0 0 p j {} CASCADE {NO ACTION} NONE}
000106 do_test e_fkey-2.3 {
000107 execsql { PRAGMA foreign_keys }
000108 } {}
000109 }
000110
000111
000112 #-------------------------------------------------------------------------
000113 # Test the effects of defining OMIT_FOREIGN_KEY.
000114 #
000115 # EVIDENCE-OF: R-58428-36660 If OMIT_FOREIGN_KEY is defined, then
000116 # foreign key definitions cannot even be parsed (attempting to specify a
000117 # foreign key definition is a syntax error).
000118 #
000119 # Specifically, test that foreign key constraints cannot even be parsed
000120 # in such a build.
000121 #
000122 reset_db
000123 ifcapable !foreignkey {
000124 do_test e_fkey-3.1 {
000125 execsql { CREATE TABLE p(i PRIMARY KEY) }
000126 catchsql { CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE) }
000127 } {1 {near "ON": syntax error}}
000128 do_test e_fkey-3.2 {
000129 # This is allowed, as in this build, "REFERENCES" is not a keyword.
000130 # The declared datatype of column j is "REFERENCES p".
000131 execsql { CREATE TABLE c(j REFERENCES p) }
000132 } {}
000133 do_test e_fkey-3.3 {
000134 execsql { PRAGMA table_info(c) }
000135 } {0 j {REFERENCES p} 0 {} 0}
000136 do_test e_fkey-3.4 {
000137 execsql { PRAGMA foreign_key_list(c) }
000138 } {}
000139 do_test e_fkey-3.5 {
000140 execsql { PRAGMA foreign_keys }
000141 } {}
000142 }
000143
000144 ifcapable !foreignkey||!trigger { finish_test ; return }
000145 reset_db
000146
000147
000148 #-------------------------------------------------------------------------
000149 # EVIDENCE-OF: R-07280-60510 Assuming the library is compiled with
000150 # foreign key constraints enabled, it must still be enabled by the
000151 # application at runtime, using the PRAGMA foreign_keys command.
000152 #
000153 # This also tests that foreign key constraints are disabled by default.
000154 #
000155 # EVIDENCE-OF: R-44261-39702 Foreign key constraints are disabled by
000156 # default (for backwards compatibility), so must be enabled separately
000157 # for each database connection.
000158 #
000159 drop_all_tables
000160 do_test e_fkey-4.1 {
000161 execsql {
000162 CREATE TABLE p(i PRIMARY KEY);
000163 CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);
000164 INSERT INTO p VALUES('hello');
000165 INSERT INTO c VALUES('hello');
000166 UPDATE p SET i = 'world';
000167 SELECT * FROM c;
000168 }
000169 } {hello}
000170 do_test e_fkey-4.2 {
000171 execsql {
000172 DELETE FROM c;
000173 DELETE FROM p;
000174 PRAGMA foreign_keys = ON;
000175 INSERT INTO p VALUES('hello');
000176 INSERT INTO c VALUES('hello');
000177 UPDATE p SET i = 'world';
000178 SELECT * FROM c;
000179 }
000180 } {world}
000181
000182 #-------------------------------------------------------------------------
000183 # EVIDENCE-OF: R-08013-37737 The application can also use a PRAGMA
000184 # foreign_keys statement to determine if foreign keys are currently
000185 # enabled.
000186
000187 #
000188 # This also tests the example code in section 2 of foreignkeys.in.
000189 #
000190 # EVIDENCE-OF: R-11255-19907
000191 #
000192 reset_db
000193 do_test e_fkey-5.1 {
000194 execsql { PRAGMA foreign_keys }
000195 } {0}
000196 do_test e_fkey-5.2 {
000197 execsql {
000198 PRAGMA foreign_keys = ON;
000199 PRAGMA foreign_keys;
000200 }
000201 } {1}
000202 do_test e_fkey-5.3 {
000203 execsql {
000204 PRAGMA foreign_keys = OFF;
000205 PRAGMA foreign_keys;
000206 }
000207 } {0}
000208
000209 #-------------------------------------------------------------------------
000210 # Test that it is not possible to enable or disable foreign key support
000211 # while not in auto-commit mode.
000212 #
000213 # EVIDENCE-OF: R-46649-58537 It is not possible to enable or disable
000214 # foreign key constraints in the middle of a multi-statement transaction
000215 # (when SQLite is not in autocommit mode). Attempting to do so does not
000216 # return an error; it simply has no effect.
000217 #
000218 reset_db
000219 do_test e_fkey-6.1 {
000220 execsql {
000221 PRAGMA foreign_keys = ON;
000222 CREATE TABLE t1(a UNIQUE, b);
000223 CREATE TABLE t2(c, d REFERENCES t1(a));
000224 INSERT INTO t1 VALUES(1, 2);
000225 INSERT INTO t2 VALUES(2, 1);
000226 BEGIN;
000227 PRAGMA foreign_keys = OFF;
000228 }
000229 catchsql {
000230 DELETE FROM t1
000231 }
000232 } {1 {FOREIGN KEY constraint failed}}
000233 do_test e_fkey-6.2 {
000234 execsql { PRAGMA foreign_keys }
000235 } {1}
000236 do_test e_fkey-6.3 {
000237 execsql {
000238 COMMIT;
000239 PRAGMA foreign_keys = OFF;
000240 BEGIN;
000241 PRAGMA foreign_keys = ON;
000242 DELETE FROM t1;
000243 PRAGMA foreign_keys;
000244 }
000245 } {0}
000246 do_test e_fkey-6.4 {
000247 execsql COMMIT
000248 } {}
000249
000250 ###########################################################################
000251 ### SECTION 1: Introduction to Foreign Key Constraints
000252 ###########################################################################
000253 execsql "PRAGMA foreign_keys = ON"
000254
000255 #-------------------------------------------------------------------------
000256 # Verify that the syntax in the first example in section 1 is valid.
000257 #
000258 # EVIDENCE-OF: R-04042-24825 To do so, a foreign key definition may be
000259 # added by modifying the declaration of the track table to the
000260 # following: CREATE TABLE track( trackid INTEGER, trackname TEXT,
000261 # trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES
000262 # artist(artistid) );
000263 #
000264 do_test e_fkey-7.1 {
000265 execsql {
000266 CREATE TABLE artist(
000267 artistid INTEGER PRIMARY KEY,
000268 artistname TEXT
000269 );
000270 CREATE TABLE track(
000271 trackid INTEGER,
000272 trackname TEXT,
000273 trackartist INTEGER,
000274 FOREIGN KEY(trackartist) REFERENCES artist(artistid)
000275 );
000276 }
000277 } {}
000278
000279 #-------------------------------------------------------------------------
000280 # EVIDENCE-OF: R-61362-32087 Attempting to insert a row into the track
000281 # table that does not correspond to any row in the artist table will
000282 # fail,
000283 #
000284 do_test e_fkey-8.1 {
000285 catchsql { INSERT INTO track VALUES(1, 'track 1', 1) }
000286 } {1 {FOREIGN KEY constraint failed}}
000287 do_test e_fkey-8.2 {
000288 execsql { INSERT INTO artist VALUES(2, 'artist 1') }
000289 catchsql { INSERT INTO track VALUES(1, 'track 1', 1) }
000290 } {1 {FOREIGN KEY constraint failed}}
000291 do_test e_fkey-8.2 {
000292 execsql { INSERT INTO track VALUES(1, 'track 1', 2) }
000293 } {}
000294
000295 #-------------------------------------------------------------------------
000296 # Attempting to delete a row from the 'artist' table while there are
000297 # dependent rows in the track table also fails.
000298 #
000299 # EVIDENCE-OF: R-24401-52400 as will attempting to delete a row from the
000300 # artist table when there exist dependent rows in the track table
000301 #
000302 do_test e_fkey-9.1 {
000303 catchsql { DELETE FROM artist WHERE artistid = 2 }
000304 } {1 {FOREIGN KEY constraint failed}}
000305 do_test e_fkey-9.2 {
000306 execsql {
000307 DELETE FROM track WHERE trackartist = 2;
000308 DELETE FROM artist WHERE artistid = 2;
000309 }
000310 } {}
000311
000312 #-------------------------------------------------------------------------
000313 # If the foreign key column (trackartist) in table 'track' is set to NULL,
000314 # there is no requirement for a matching row in the 'artist' table.
000315 #
000316 # EVIDENCE-OF: R-23980-48859 There is one exception: if the foreign key
000317 # column in the track table is NULL, then no corresponding entry in the
000318 # artist table is required.
000319 #
000320 do_test e_fkey-10.1 {
000321 execsql {
000322 INSERT INTO track VALUES(1, 'track 1', NULL);
000323 INSERT INTO track VALUES(2, 'track 2', NULL);
000324 }
000325 } {}
000326 do_test e_fkey-10.2 {
000327 execsql { SELECT * FROM artist }
000328 } {}
000329 do_test e_fkey-10.3 {
000330 # Setting the trackid to a non-NULL value fails, of course.
000331 catchsql { UPDATE track SET trackartist = 5 WHERE trackid = 1 }
000332 } {1 {FOREIGN KEY constraint failed}}
000333 do_test e_fkey-10.4 {
000334 execsql {
000335 INSERT INTO artist VALUES(5, 'artist 5');
000336 UPDATE track SET trackartist = 5 WHERE trackid = 1;
000337 }
000338 catchsql { DELETE FROM artist WHERE artistid = 5}
000339 } {1 {FOREIGN KEY constraint failed}}
000340 do_test e_fkey-10.5 {
000341 execsql {
000342 UPDATE track SET trackartist = NULL WHERE trackid = 1;
000343 DELETE FROM artist WHERE artistid = 5;
000344 }
000345 } {}
000346
000347 #-------------------------------------------------------------------------
000348 # Test that the following is true fo all rows in the track table:
000349 #
000350 # trackartist IS NULL OR
000351 # EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)
000352 #
000353 # EVIDENCE-OF: R-52486-21352 Expressed in SQL, this means that for every
000354 # row in the track table, the following expression evaluates to true:
000355 # trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE
000356 # artistid=trackartist)
000357
000358 # This procedure executes a test case to check that statement
000359 # R-52486-21352 is true after executing the SQL statement passed.
000360 # as the second argument.
000361 proc test_r52486_21352 {tn sql} {
000362 set res [catchsql $sql]
000363 set results {
000364 {0 {}}
000365 {1 {UNIQUE constraint failed: artist.artistid}}
000366 {1 {FOREIGN KEY constraint failed}}
000367 }
000368 if {[lsearch $results $res]<0} {
000369 error $res
000370 }
000371
000372 do_test e_fkey-11.$tn {
000373 execsql {
000374 SELECT count(*) FROM track WHERE NOT (
000375 trackartist IS NULL OR
000376 EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)
000377 )
000378 }
000379 } {0}
000380 }
000381
000382 # Execute a series of random INSERT, UPDATE and DELETE operations
000383 # (some of which may fail due to FK or PK constraint violations) on
000384 # the two tables in the example schema. Test that R-52486-21352
000385 # is true after executing each operation.
000386 #
000387 set Template {
000388 {INSERT INTO track VALUES($t, 'track $t', $a)}
000389 {DELETE FROM track WHERE trackid = $t}
000390 {UPDATE track SET trackartist = $a WHERE trackid = $t}
000391 {INSERT INTO artist VALUES($a, 'artist $a')}
000392 {DELETE FROM artist WHERE artistid = $a}
000393 {UPDATE artist SET artistid = $a2 WHERE artistid = $a}
000394 }
000395 for {set i 0} {$i < 500} {incr i} {
000396 set a [expr int(rand()*10)]
000397 set a2 [expr int(rand()*10)]
000398 set t [expr int(rand()*50)]
000399 set sql [subst [lindex $Template [expr int(rand()*6)]]]
000400
000401 test_r52486_21352 $i $sql
000402 }
000403
000404 #-------------------------------------------------------------------------
000405 # Check that a NOT NULL constraint can be added to the example schema
000406 # to prohibit NULL child keys from being inserted.
000407 #
000408 # EVIDENCE-OF: R-42412-59321 Tip: If the application requires a stricter
000409 # relationship between artist and track, where NULL values are not
000410 # permitted in the trackartist column, simply add the appropriate "NOT
000411 # NULL" constraint to the schema.
000412 #
000413 drop_all_tables
000414 do_test e_fkey-12.1 {
000415 execsql {
000416 CREATE TABLE artist(
000417 artistid INTEGER PRIMARY KEY,
000418 artistname TEXT
000419 );
000420 CREATE TABLE track(
000421 trackid INTEGER,
000422 trackname TEXT,
000423 trackartist INTEGER NOT NULL,
000424 FOREIGN KEY(trackartist) REFERENCES artist(artistid)
000425 );
000426 }
000427 } {}
000428 do_test e_fkey-12.2 {
000429 catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) }
000430 } {1 {NOT NULL constraint failed: track.trackartist}}
000431
000432 #-------------------------------------------------------------------------
000433 # EVIDENCE-OF: R-16127-35442
000434 #
000435 # Test an example from foreignkeys.html.
000436 #
000437 drop_all_tables
000438 do_test e_fkey-13.1 {
000439 execsql {
000440 CREATE TABLE artist(
000441 artistid INTEGER PRIMARY KEY,
000442 artistname TEXT
000443 );
000444 CREATE TABLE track(
000445 trackid INTEGER,
000446 trackname TEXT,
000447 trackartist INTEGER,
000448 FOREIGN KEY(trackartist) REFERENCES artist(artistid)
000449 );
000450 INSERT INTO artist VALUES(1, 'Dean Martin');
000451 INSERT INTO artist VALUES(2, 'Frank Sinatra');
000452 INSERT INTO track VALUES(11, 'That''s Amore', 1);
000453 INSERT INTO track VALUES(12, 'Christmas Blues', 1);
000454 INSERT INTO track VALUES(13, 'My Way', 2);
000455 }
000456 } {}
000457 do_test e_fkey-13.2 {
000458 catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', 3) }
000459 } {1 {FOREIGN KEY constraint failed}}
000460 do_test e_fkey-13.3 {
000461 execsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) }
000462 } {}
000463 do_test e_fkey-13.4 {
000464 catchsql {
000465 UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';
000466 }
000467 } {1 {FOREIGN KEY constraint failed}}
000468 do_test e_fkey-13.5 {
000469 execsql {
000470 INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
000471 UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';
000472 INSERT INTO track VALUES(15, 'Boogie Woogie', 3);
000473 }
000474 } {}
000475
000476 #-------------------------------------------------------------------------
000477 # EVIDENCE-OF: R-15958-50233
000478 #
000479 # Test the second example from the first section of foreignkeys.html.
000480 #
000481 do_test e_fkey-14.1 {
000482 catchsql {
000483 DELETE FROM artist WHERE artistname = 'Frank Sinatra';
000484 }
000485 } {1 {FOREIGN KEY constraint failed}}
000486 do_test e_fkey-14.2 {
000487 execsql {
000488 DELETE FROM track WHERE trackname = 'My Way';
000489 DELETE FROM artist WHERE artistname = 'Frank Sinatra';
000490 }
000491 } {}
000492 do_test e_fkey-14.3 {
000493 catchsql {
000494 UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
000495 }
000496 } {1 {FOREIGN KEY constraint failed}}
000497 do_test e_fkey-14.4 {
000498 execsql {
000499 DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues');
000500 UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
000501 }
000502 } {}
000503
000504
000505 #-------------------------------------------------------------------------
000506 # EVIDENCE-OF: R-56032-24923 The foreign key constraint is satisfied if
000507 # for each row in the child table either one or more of the child key
000508 # columns are NULL, or there exists a row in the parent table for which
000509 # each parent key column contains a value equal to the value in its
000510 # associated child key column.
000511 #
000512 # Test also that the usual comparison rules are used when testing if there
000513 # is a matching row in the parent table of a foreign key constraint.
000514 #
000515 # EVIDENCE-OF: R-57765-12380 In the above paragraph, the term "equal"
000516 # means equal when values are compared using the rules specified here.
000517 #
000518 drop_all_tables
000519 do_test e_fkey-15.1 {
000520 execsql {
000521 CREATE TABLE par(p PRIMARY KEY);
000522 CREATE TABLE chi(c REFERENCES par);
000523
000524 INSERT INTO par VALUES(1);
000525 INSERT INTO par VALUES('1');
000526 INSERT INTO par VALUES(X'31');
000527 SELECT typeof(p) FROM par;
000528 }
000529 } {integer text blob}
000530
000531 proc test_efkey_45 {tn isError sql} {
000532 do_test e_fkey-15.$tn.1 "
000533 catchsql {$sql}
000534 " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]
000535
000536 do_test e_fkey-15.$tn.2 {
000537 execsql {
000538 SELECT * FROM chi WHERE c IS NOT NULL AND c NOT IN (SELECT p FROM par)
000539 }
000540 } {}
000541 }
000542
000543 test_efkey_45 1 0 "INSERT INTO chi VALUES(1)"
000544 test_efkey_45 2 1 "INSERT INTO chi VALUES('1.0')"
000545 test_efkey_45 3 0 "INSERT INTO chi VALUES('1')"
000546 test_efkey_45 4 1 "DELETE FROM par WHERE p = '1'"
000547 test_efkey_45 5 0 "DELETE FROM chi WHERE c = '1'"
000548 test_efkey_45 6 0 "DELETE FROM par WHERE p = '1'"
000549 test_efkey_45 7 1 "INSERT INTO chi VALUES('1')"
000550 test_efkey_45 8 0 "INSERT INTO chi VALUES(X'31')"
000551 test_efkey_45 9 1 "INSERT INTO chi VALUES(X'32')"
000552
000553 #-------------------------------------------------------------------------
000554 # Specifically, test that when comparing child and parent key values the
000555 # default collation sequence of the parent key column is used.
000556 #
000557 # EVIDENCE-OF: R-15796-47513 When comparing text values, the collating
000558 # sequence associated with the parent key column is always used.
000559 #
000560 drop_all_tables
000561 do_test e_fkey-16.1 {
000562 execsql {
000563 CREATE TABLE t1(a COLLATE nocase PRIMARY KEY);
000564 CREATE TABLE t2(b REFERENCES t1);
000565 }
000566 } {}
000567 do_test e_fkey-16.2 {
000568 execsql {
000569 INSERT INTO t1 VALUES('oNe');
000570 INSERT INTO t2 VALUES('one');
000571 INSERT INTO t2 VALUES('ONE');
000572 UPDATE t2 SET b = 'OnE';
000573 UPDATE t1 SET a = 'ONE';
000574 }
000575 } {}
000576 do_test e_fkey-16.3 {
000577 catchsql { UPDATE t2 SET b = 'two' WHERE rowid = 1 }
000578 } {1 {FOREIGN KEY constraint failed}}
000579 do_test e_fkey-16.4 {
000580 catchsql { DELETE FROM t1 WHERE rowid = 1 }
000581 } {1 {FOREIGN KEY constraint failed}}
000582
000583 #-------------------------------------------------------------------------
000584 # Specifically, test that when comparing child and parent key values the
000585 # affinity of the parent key column is applied to the child key value
000586 # before the comparison takes place.
000587 #
000588 # EVIDENCE-OF: R-04240-13860 When comparing values, if the parent key
000589 # column has an affinity, then that affinity is applied to the child key
000590 # value before the comparison is performed.
000591 #
000592 drop_all_tables
000593 do_test e_fkey-17.1 {
000594 execsql {
000595 CREATE TABLE t1(a NUMERIC PRIMARY KEY);
000596 CREATE TABLE t2(b TEXT REFERENCES t1);
000597 }
000598 } {}
000599 do_test e_fkey-17.2 {
000600 execsql {
000601 INSERT INTO t1 VALUES(1);
000602 INSERT INTO t1 VALUES(2);
000603 INSERT INTO t1 VALUES('three');
000604 INSERT INTO t2 VALUES('2.0');
000605 SELECT b, typeof(b) FROM t2;
000606 }
000607 } {2.0 text}
000608 do_test e_fkey-17.3 {
000609 execsql { SELECT typeof(a) FROM t1 }
000610 } {integer integer text}
000611 do_test e_fkey-17.4 {
000612 catchsql { DELETE FROM t1 WHERE rowid = 2 }
000613 } {1 {FOREIGN KEY constraint failed}}
000614
000615 ###########################################################################
000616 ### SECTION 3: Required and Suggested Database Indexes
000617 ###########################################################################
000618
000619 #-------------------------------------------------------------------------
000620 # A parent key must be either a PRIMARY KEY, subject to a UNIQUE
000621 # constraint, or have a UNIQUE index created on it.
000622 #
000623 # EVIDENCE-OF: R-13435-26311 Usually, the parent key of a foreign key
000624 # constraint is the primary key of the parent table. If they are not the
000625 # primary key, then the parent key columns must be collectively subject
000626 # to a UNIQUE constraint or have a UNIQUE index.
000627 #
000628 # Also test that if a parent key is not subject to a PRIMARY KEY or UNIQUE
000629 # constraint, but does have a UNIQUE index created on it, then the UNIQUE index
000630 # must use the default collation sequences associated with the parent key
000631 # columns.
000632 #
000633 # EVIDENCE-OF: R-00376-39212 If the parent key columns have a UNIQUE
000634 # index, then that index must use the collation sequences that are
000635 # specified in the CREATE TABLE statement for the parent table.
000636 #
000637 drop_all_tables
000638 do_test e_fkey-18.1 {
000639 execsql {
000640 CREATE TABLE t2(a REFERENCES t1(x));
000641 }
000642 } {}
000643 proc test_efkey_57 {tn isError sql} {
000644 catchsql { DROP TABLE t1 }
000645 execsql $sql
000646 do_test e_fkey-18.$tn {
000647 catchsql { INSERT INTO t2 VALUES(NULL) }
000648 } [lindex {{0 {}} {/1 {foreign key mismatch - ".*" referencing ".*"}/}} \
000649 $isError]
000650 }
000651 test_efkey_57 2 0 { CREATE TABLE t1(x PRIMARY KEY) }
000652 test_efkey_57 3 0 { CREATE TABLE t1(x UNIQUE) }
000653 test_efkey_57 4 0 { CREATE TABLE t1(x); CREATE UNIQUE INDEX t1i ON t1(x) }
000654 test_efkey_57 5 1 {
000655 CREATE TABLE t1(x);
000656 CREATE UNIQUE INDEX t1i ON t1(x COLLATE nocase);
000657 }
000658 test_efkey_57 6 1 { CREATE TABLE t1(x) }
000659 test_efkey_57 7 1 { CREATE TABLE t1(x, y, PRIMARY KEY(x, y)) }
000660 test_efkey_57 8 1 { CREATE TABLE t1(x, y, UNIQUE(x, y)) }
000661 test_efkey_57 9 1 {
000662 CREATE TABLE t1(x, y);
000663 CREATE UNIQUE INDEX t1i ON t1(x, y);
000664 }
000665
000666
000667 #-------------------------------------------------------------------------
000668 # This block tests an example in foreignkeys.html. Several testable
000669 # statements refer to this example, as follows
000670 #
000671 # EVIDENCE-OF: R-27484-01467
000672 #
000673 # FK Constraints on child1, child2 and child3 are Ok.
000674 #
000675 # Problem with FK on child4:
000676 #
000677 # EVIDENCE-OF: R-51039-44840 The foreign key declared as part of table
000678 # child4 is an error because even though the parent key column is
000679 # indexed, the index is not UNIQUE.
000680 #
000681 # Problem with FK on child5:
000682 #
000683 # EVIDENCE-OF: R-01060-48788 The foreign key for table child5 is an
000684 # error because even though the parent key column has a unique index,
000685 # the index uses a different collating sequence.
000686 #
000687 # Problem with FK on child6 and child7:
000688 #
000689 # EVIDENCE-OF: R-63088-37469 Tables child6 and child7 are incorrect
000690 # because while both have UNIQUE indices on their parent keys, the keys
000691 # are not an exact match to the columns of a single UNIQUE index.
000692 #
000693 drop_all_tables
000694 do_test e_fkey-19.1 {
000695 execsql {
000696 CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f);
000697 CREATE UNIQUE INDEX i1 ON parent(c, d);
000698 CREATE INDEX i2 ON parent(e);
000699 CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase);
000700
000701 CREATE TABLE child1(f, g REFERENCES parent(a)); -- Ok
000702 CREATE TABLE child2(h, i REFERENCES parent(b)); -- Ok
000703 CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d)); -- Ok
000704 CREATE TABLE child4(l, m REFERENCES parent(e)); -- Err
000705 CREATE TABLE child5(n, o REFERENCES parent(f)); -- Err
000706 CREATE TABLE child6(p, q, FOREIGN KEY(p,q) REFERENCES parent(b, c)); -- Err
000707 CREATE TABLE child7(r REFERENCES parent(c)); -- Err
000708 }
000709 } {}
000710 do_test e_fkey-19.2 {
000711 execsql {
000712 INSERT INTO parent VALUES(1, 2, 3, 4, 5, 6);
000713 INSERT INTO child1 VALUES('xxx', 1);
000714 INSERT INTO child2 VALUES('xxx', 2);
000715 INSERT INTO child3 VALUES(3, 4);
000716 }
000717 } {}
000718 do_test e_fkey-19.2 {
000719 catchsql { INSERT INTO child4 VALUES('xxx', 5) }
000720 } {1 {foreign key mismatch - "child4" referencing "parent"}}
000721 do_test e_fkey-19.3 {
000722 catchsql { INSERT INTO child5 VALUES('xxx', 6) }
000723 } {1 {foreign key mismatch - "child5" referencing "parent"}}
000724 do_test e_fkey-19.4 {
000725 catchsql { INSERT INTO child6 VALUES(2, 3) }
000726 } {1 {foreign key mismatch - "child6" referencing "parent"}}
000727 do_test e_fkey-19.5 {
000728 catchsql { INSERT INTO child7 VALUES(3) }
000729 } {1 {foreign key mismatch - "child7" referencing "parent"}}
000730
000731 #-------------------------------------------------------------------------
000732 # Test errors in the database schema that are detected while preparing
000733 # DML statements. The error text for these messages always matches
000734 # either "foreign key mismatch" or "no such table*" (using [string match]).
000735 #
000736 # EVIDENCE-OF: R-45488-08504 If the database schema contains foreign key
000737 # errors that require looking at more than one table definition to
000738 # identify, then those errors are not detected when the tables are
000739 # created.
000740 #
000741 # EVIDENCE-OF: R-48391-38472 Instead, such errors prevent the
000742 # application from preparing SQL statements that modify the content of
000743 # the child or parent tables in ways that use the foreign keys.
000744 #
000745 # EVIDENCE-OF: R-03108-63659 The English language error message for
000746 # foreign key DML errors is usually "foreign key mismatch" but can also
000747 # be "no such table" if the parent table does not exist.
000748 #
000749 # EVIDENCE-OF: R-35763-48267 Foreign key DML errors are reported if: The
000750 # parent table does not exist, or The parent key columns named in the
000751 # foreign key constraint do not exist, or The parent key columns named
000752 # in the foreign key constraint are not the primary key of the parent
000753 # table and are not subject to a unique constraint using collating
000754 # sequence specified in the CREATE TABLE, or The child table references
000755 # the primary key of the parent without specifying the primary key
000756 # columns and the number of primary key columns in the parent do not
000757 # match the number of child key columns.
000758 #
000759 do_test e_fkey-20.1 {
000760 execsql {
000761 CREATE TABLE c1(c REFERENCES nosuchtable, d);
000762
000763 CREATE TABLE p2(a, b, UNIQUE(a, b));
000764 CREATE TABLE c2(c, d, FOREIGN KEY(c, d) REFERENCES p2(a, x));
000765
000766 CREATE TABLE p3(a PRIMARY KEY, b);
000767 CREATE TABLE c3(c REFERENCES p3(b), d);
000768
000769 CREATE TABLE p4(a PRIMARY KEY, b);
000770 CREATE UNIQUE INDEX p4i ON p4(b COLLATE nocase);
000771 CREATE TABLE c4(c REFERENCES p4(b), d);
000772
000773 CREATE TABLE p5(a PRIMARY KEY, b COLLATE nocase);
000774 CREATE UNIQUE INDEX p5i ON p5(b COLLATE binary);
000775 CREATE TABLE c5(c REFERENCES p5(b), d);
000776
000777 CREATE TABLE p6(a PRIMARY KEY, b);
000778 CREATE TABLE c6(c, d, FOREIGN KEY(c, d) REFERENCES p6);
000779
000780 CREATE TABLE p7(a, b, PRIMARY KEY(a, b));
000781 CREATE TABLE c7(c, d REFERENCES p7);
000782 }
000783 } {}
000784
000785 foreach {tn tbl ptbl err} {
000786 2 c1 {} "no such table: main.nosuchtable"
000787 3 c2 p2 "foreign key mismatch - \"c2\" referencing \"p2\""
000788 4 c3 p3 "foreign key mismatch - \"c3\" referencing \"p3\""
000789 5 c4 p4 "foreign key mismatch - \"c4\" referencing \"p4\""
000790 6 c5 p5 "foreign key mismatch - \"c5\" referencing \"p5\""
000791 7 c6 p6 "foreign key mismatch - \"c6\" referencing \"p6\""
000792 8 c7 p7 "foreign key mismatch - \"c7\" referencing \"p7\""
000793 } {
000794 do_test e_fkey-20.$tn.1 {
000795 catchsql "INSERT INTO $tbl VALUES('a', 'b')"
000796 } [list 1 $err]
000797 do_test e_fkey-20.$tn.2 {
000798 catchsql "UPDATE $tbl SET c = ?, d = ?"
000799 } [list 1 $err]
000800 do_test e_fkey-20.$tn.3 {
000801 catchsql "INSERT INTO $tbl SELECT ?, ?"
000802 } [list 1 $err]
000803
000804 if {$ptbl ne ""} {
000805 do_test e_fkey-20.$tn.4 {
000806 catchsql "DELETE FROM $ptbl"
000807 } [list 1 $err]
000808 do_test e_fkey-20.$tn.5 {
000809 catchsql "UPDATE $ptbl SET a = ?, b = ?"
000810 } [list 1 $err]
000811 do_test e_fkey-20.$tn.6 {
000812 catchsql "INSERT INTO $ptbl SELECT ?, ?"
000813 } [list 1 $err]
000814 }
000815 }
000816
000817 #-------------------------------------------------------------------------
000818 # EVIDENCE-OF: R-19353-43643
000819 #
000820 # Test the example of foreign key mismatch errors caused by implicitly
000821 # mapping a child key to the primary key of the parent table when the
000822 # child key consists of a different number of columns to that primary key.
000823 #
000824 drop_all_tables
000825 do_test e_fkey-21.1 {
000826 execsql {
000827 CREATE TABLE parent2(a, b, PRIMARY KEY(a,b));
000828
000829 CREATE TABLE child8(x, y, FOREIGN KEY(x,y) REFERENCES parent2); -- Ok
000830 CREATE TABLE child9(x REFERENCES parent2); -- Err
000831 CREATE TABLE child10(x,y,z, FOREIGN KEY(x,y,z) REFERENCES parent2); -- Err
000832 }
000833 } {}
000834 do_test e_fkey-21.2 {
000835 execsql {
000836 INSERT INTO parent2 VALUES('I', 'II');
000837 INSERT INTO child8 VALUES('I', 'II');
000838 }
000839 } {}
000840 do_test e_fkey-21.3 {
000841 catchsql { INSERT INTO child9 VALUES('I') }
000842 } {1 {foreign key mismatch - "child9" referencing "parent2"}}
000843 do_test e_fkey-21.4 {
000844 catchsql { INSERT INTO child9 VALUES('II') }
000845 } {1 {foreign key mismatch - "child9" referencing "parent2"}}
000846 do_test e_fkey-21.5 {
000847 catchsql { INSERT INTO child9 VALUES(NULL) }
000848 } {1 {foreign key mismatch - "child9" referencing "parent2"}}
000849 do_test e_fkey-21.6 {
000850 catchsql { INSERT INTO child10 VALUES('I', 'II', 'III') }
000851 } {1 {foreign key mismatch - "child10" referencing "parent2"}}
000852 do_test e_fkey-21.7 {
000853 catchsql { INSERT INTO child10 VALUES(1, 2, 3) }
000854 } {1 {foreign key mismatch - "child10" referencing "parent2"}}
000855 do_test e_fkey-21.8 {
000856 catchsql { INSERT INTO child10 VALUES(NULL, NULL, NULL) }
000857 } {1 {foreign key mismatch - "child10" referencing "parent2"}}
000858
000859 #-------------------------------------------------------------------------
000860 # Test errors that are reported when creating the child table.
000861 # Specifically:
000862 #
000863 # * different number of child and parent key columns, and
000864 # * child columns that do not exist.
000865 #
000866 # EVIDENCE-OF: R-23682-59820 By contrast, if foreign key errors can be
000867 # recognized simply by looking at the definition of the child table and
000868 # without having to consult the parent table definition, then the CREATE
000869 # TABLE statement for the child table fails.
000870 #
000871 # These errors are reported whether or not FK support is enabled.
000872 #
000873 # EVIDENCE-OF: R-33883-28833 Foreign key DDL errors are reported
000874 # regardless of whether or not foreign key constraints are enabled when
000875 # the table is created.
000876 #
000877 drop_all_tables
000878 foreach fk [list OFF ON] {
000879 execsql "PRAGMA foreign_keys = $fk"
000880 set i 0
000881 foreach {sql error} {
000882 "CREATE TABLE child1(a, b, FOREIGN KEY(a, b) REFERENCES p(c))"
000883 {number of columns in foreign key does not match the number of columns in the referenced table}
000884 "CREATE TABLE child2(a, b, FOREIGN KEY(a, b) REFERENCES p(c, d, e))"
000885 {number of columns in foreign key does not match the number of columns in the referenced table}
000886 "CREATE TABLE child2(a, b, FOREIGN KEY(a, c) REFERENCES p(c, d))"
000887 {unknown column "c" in foreign key definition}
000888 "CREATE TABLE child2(a, b, FOREIGN KEY(c, b) REFERENCES p(c, d))"
000889 {unknown column "c" in foreign key definition}
000890 } {
000891 do_test e_fkey-22.$fk.[incr i] {
000892 catchsql $sql
000893 } [list 1 $error]
000894 }
000895 }
000896
000897 #-------------------------------------------------------------------------
000898 # Test that a REFERENCING clause that does not specify parent key columns
000899 # implicitly maps to the primary key of the parent table.
000900 #
000901 # EVIDENCE-OF: R-43879-08025 Attaching a "REFERENCES <parent-table>"
000902 # clause to a column definition creates a foreign
000903 # key constraint that maps the column to the primary key of
000904 # <parent-table>.
000905 #
000906 do_test e_fkey-23.1 {
000907 execsql {
000908 CREATE TABLE p1(a, b, PRIMARY KEY(a, b));
000909 CREATE TABLE p2(a, b PRIMARY KEY);
000910 CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p1);
000911 CREATE TABLE c2(a, b REFERENCES p2);
000912 }
000913 } {}
000914 proc test_efkey_60 {tn isError sql} {
000915 do_test e_fkey-23.$tn "
000916 catchsql {$sql}
000917 " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]
000918 }
000919
000920 test_efkey_60 2 1 "INSERT INTO c1 VALUES(239, 231)"
000921 test_efkey_60 3 0 "INSERT INTO p1 VALUES(239, 231)"
000922 test_efkey_60 4 0 "INSERT INTO c1 VALUES(239, 231)"
000923 test_efkey_60 5 1 "INSERT INTO c2 VALUES(239, 231)"
000924 test_efkey_60 6 0 "INSERT INTO p2 VALUES(239, 231)"
000925 test_efkey_60 7 0 "INSERT INTO c2 VALUES(239, 231)"
000926
000927 #-------------------------------------------------------------------------
000928 # Test that an index on on the child key columns of an FK constraint
000929 # is optional.
000930 #
000931 # EVIDENCE-OF: R-15417-28014 Indices are not required for child key
000932 # columns
000933 #
000934 # Also test that if an index is created on the child key columns, it does
000935 # not make a difference whether or not it is a UNIQUE index.
000936 #
000937 # EVIDENCE-OF: R-15741-50893 The child key index does not have to be
000938 # (and usually will not be) a UNIQUE index.
000939 #
000940 drop_all_tables
000941 do_test e_fkey-24.1 {
000942 execsql {
000943 CREATE TABLE parent(x, y, UNIQUE(y, x));
000944 CREATE TABLE c1(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
000945 CREATE TABLE c2(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
000946 CREATE TABLE c3(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
000947 CREATE INDEX c2i ON c2(a, b);
000948 CREATE UNIQUE INDEX c3i ON c2(b, a);
000949 }
000950 } {}
000951 proc test_efkey_61 {tn isError sql} {
000952 do_test e_fkey-24.$tn "
000953 catchsql {$sql}
000954 " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]
000955 }
000956 foreach {tn c} [list 2 c1 3 c2 4 c3] {
000957 test_efkey_61 $tn.1 1 "INSERT INTO $c VALUES(1, 2)"
000958 test_efkey_61 $tn.2 0 "INSERT INTO parent VALUES(1, 2)"
000959 test_efkey_61 $tn.3 0 "INSERT INTO $c VALUES(1, 2)"
000960
000961 execsql "DELETE FROM $c ; DELETE FROM parent"
000962 }
000963
000964 #-------------------------------------------------------------------------
000965 # EVIDENCE-OF: R-00279-52283
000966 #
000967 # Test an example showing that when a row is deleted from the parent
000968 # table, the child table is queried for orphaned rows as follows:
000969 #
000970 # SELECT rowid FROM track WHERE trackartist = ?
000971 #
000972 # EVIDENCE-OF: R-23302-30956 If this SELECT returns any rows at all,
000973 # then SQLite concludes that deleting the row from the parent table
000974 # would violate the foreign key constraint and returns an error.
000975 #
000976 do_test e_fkey-25.1 {
000977 execsql {
000978 CREATE TABLE artist(
000979 artistid INTEGER PRIMARY KEY,
000980 artistname TEXT
000981 );
000982 CREATE TABLE track(
000983 trackid INTEGER,
000984 trackname TEXT,
000985 trackartist INTEGER,
000986 FOREIGN KEY(trackartist) REFERENCES artist(artistid)
000987 );
000988 }
000989 } {}
000990 do_detail_test e_fkey-25.2 {
000991 PRAGMA foreign_keys = OFF;
000992 EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
000993 EXPLAIN QUERY PLAN SELECT rowid FROM track WHERE trackartist = ?;
000994 } {
000995 {SCAN artist}
000996 {SCAN track}
000997 }
000998 do_detail_test e_fkey-25.3 {
000999 PRAGMA foreign_keys = ON;
001000 EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
001001 } {
001002 {SCAN artist}
001003 {SCAN track}
001004 }
001005 do_test e_fkey-25.4 {
001006 execsql {
001007 INSERT INTO artist VALUES(5, 'artist 5');
001008 INSERT INTO artist VALUES(6, 'artist 6');
001009 INSERT INTO artist VALUES(7, 'artist 7');
001010 INSERT INTO track VALUES(1, 'track 1', 5);
001011 INSERT INTO track VALUES(2, 'track 2', 6);
001012 }
001013 } {}
001014
001015 do_test e_fkey-25.5 {
001016 concat \
001017 [execsql { SELECT rowid FROM track WHERE trackartist = 5 }] \
001018 [catchsql { DELETE FROM artist WHERE artistid = 5 }]
001019 } {1 1 {FOREIGN KEY constraint failed}}
001020
001021 do_test e_fkey-25.6 {
001022 concat \
001023 [execsql { SELECT rowid FROM track WHERE trackartist = 7 }] \
001024 [catchsql { DELETE FROM artist WHERE artistid = 7 }]
001025 } {0 {}}
001026
001027 do_test e_fkey-25.7 {
001028 concat \
001029 [execsql { SELECT rowid FROM track WHERE trackartist = 6 }] \
001030 [catchsql { DELETE FROM artist WHERE artistid = 6 }]
001031 } {2 1 {FOREIGN KEY constraint failed}}
001032
001033 #-------------------------------------------------------------------------
001034 # EVIDENCE-OF: R-47936-10044 Or, more generally:
001035 # SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
001036 #
001037 # Test that when a row is deleted from the parent table of an FK
001038 # constraint, the child table is queried for orphaned rows. The
001039 # query is equivalent to:
001040 #
001041 # SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
001042 #
001043 # Also test that when a row is inserted into the parent table, or when the
001044 # parent key values of an existing row are modified, a query equivalent
001045 # to the following is planned. In some cases it is not executed, but it
001046 # is always planned.
001047 #
001048 # SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
001049 #
001050 # EVIDENCE-OF: R-61616-46700 Similar queries may be run if the content
001051 # of the parent key is modified or a new row is inserted into the parent
001052 # table.
001053 #
001054 #
001055 drop_all_tables
001056 do_test e_fkey-26.1 {
001057 execsql { CREATE TABLE parent(x, y, UNIQUE(y, x)) }
001058 } {}
001059 foreach {tn sql} {
001060 2 {
001061 CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y))
001062 }
001063 3 {
001064 CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
001065 CREATE INDEX childi ON child(a, b);
001066 }
001067 4 {
001068 CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
001069 CREATE UNIQUE INDEX childi ON child(b, a);
001070 }
001071 } {
001072 execsql $sql
001073
001074 execsql {PRAGMA foreign_keys = OFF}
001075 set delete [concat \
001076 [eqp "DELETE FROM parent WHERE 1"] \
001077 [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"]
001078 ]
001079 set update [concat \
001080 [eqp "UPDATE parent SET x=?, y=?"] \
001081 [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"] \
001082 [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"]
001083 ]
001084 execsql {PRAGMA foreign_keys = ON}
001085
001086 do_test e_fkey-26.$tn.1 { eqp "DELETE FROM parent WHERE 1" } $delete
001087 do_test e_fkey-26.$tn.2 { eqp "UPDATE parent set x=?, y=?" } $update
001088
001089 execsql {DROP TABLE child}
001090 }
001091
001092 #-------------------------------------------------------------------------
001093 # EVIDENCE-OF: R-14553-34013
001094 #
001095 # Test the example schema at the end of section 3. Also test that is
001096 # is "efficient". In this case "efficient" means that foreign key
001097 # related operations on the parent table do not provoke linear scans.
001098 #
001099 drop_all_tables
001100 do_test e_fkey-27.1 {
001101 execsql {
001102 CREATE TABLE artist(
001103 artistid INTEGER PRIMARY KEY,
001104 artistname TEXT
001105 );
001106 CREATE TABLE track(
001107 trackid INTEGER,
001108 trackname TEXT,
001109 trackartist INTEGER REFERENCES artist
001110 );
001111 CREATE INDEX trackindex ON track(trackartist);
001112 }
001113 } {}
001114 do_test e_fkey-27.2 {
001115 eqp { INSERT INTO artist VALUES(?, ?) }
001116 } {}
001117 do_detail_test e_fkey-27.3 {
001118 EXPLAIN QUERY PLAN UPDATE artist SET artistid = ?, artistname = ?
001119 } {
001120 {SCAN artist}
001121 {SEARCH track USING COVERING INDEX trackindex (trackartist=?)}
001122 {SEARCH track USING COVERING INDEX trackindex (trackartist=?)}
001123 }
001124 do_detail_test e_fkey-27.4 {
001125 EXPLAIN QUERY PLAN DELETE FROM artist
001126 } {
001127 {SCAN artist}
001128 {SEARCH track USING COVERING INDEX trackindex (trackartist=?)}
001129 }
001130
001131 ###########################################################################
001132 ### SECTION 4.1: Composite Foreign Key Constraints
001133 ###########################################################################
001134
001135 #-------------------------------------------------------------------------
001136 # Check that parent and child keys must have the same number of columns.
001137 #
001138 # EVIDENCE-OF: R-41062-34431 Parent and child keys must have the same
001139 # cardinality.
001140 #
001141 foreach {tn sql err} {
001142 1 "CREATE TABLE c(jj REFERENCES p(x, y))"
001143 {foreign key on jj should reference only one column of table p}
001144
001145 2 "CREATE TABLE c(jj REFERENCES p())" {near ")": syntax error}
001146
001147 3 "CREATE TABLE c(jj, FOREIGN KEY(jj) REFERENCES p(x, y))"
001148 {number of columns in foreign key does not match the number of columns in the referenced table}
001149
001150 4 "CREATE TABLE c(jj, FOREIGN KEY(jj) REFERENCES p())"
001151 {near ")": syntax error}
001152
001153 5 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p())"
001154 {near ")": syntax error}
001155
001156 6 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p(x))"
001157 {number of columns in foreign key does not match the number of columns in the referenced table}
001158
001159 7 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p(x,y,z))"
001160 {number of columns in foreign key does not match the number of columns in the referenced table}
001161 } {
001162 drop_all_tables
001163 do_test e_fkey-28.$tn [list catchsql $sql] [list 1 $err]
001164 }
001165 do_test e_fkey-28.8 {
001166 drop_all_tables
001167 execsql {
001168 CREATE TABLE p(x PRIMARY KEY);
001169 CREATE TABLE c(a, b, FOREIGN KEY(a,b) REFERENCES p);
001170 }
001171 catchsql {DELETE FROM p}
001172 } {1 {foreign key mismatch - "c" referencing "p"}}
001173 do_test e_fkey-28.9 {
001174 drop_all_tables
001175 execsql {
001176 CREATE TABLE p(x, y, PRIMARY KEY(x,y));
001177 CREATE TABLE c(a REFERENCES p);
001178 }
001179 catchsql {DELETE FROM p}
001180 } {1 {foreign key mismatch - "c" referencing "p"}}
001181
001182
001183 #-------------------------------------------------------------------------
001184 # EVIDENCE-OF: R-24676-09859
001185 #
001186 # Test the example schema in the "Composite Foreign Key Constraints"
001187 # section.
001188 #
001189 do_test e_fkey-29.1 {
001190 execsql {
001191 CREATE TABLE album(
001192 albumartist TEXT,
001193 albumname TEXT,
001194 albumcover BINARY,
001195 PRIMARY KEY(albumartist, albumname)
001196 );
001197 CREATE TABLE song(
001198 songid INTEGER,
001199 songartist TEXT,
001200 songalbum TEXT,
001201 songname TEXT,
001202 FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist,albumname)
001203 );
001204 }
001205 } {}
001206
001207 do_test e_fkey-29.2 {
001208 execsql {
001209 INSERT INTO album VALUES('Elvis Presley', 'Elvis'' Christmas Album', NULL);
001210 INSERT INTO song VALUES(
001211 1, 'Elvis Presley', 'Elvis'' Christmas Album', 'Here Comes Santa Clause'
001212 );
001213 }
001214 } {}
001215 do_test e_fkey-29.3 {
001216 catchsql {
001217 INSERT INTO song VALUES(2, 'Elvis Presley', 'Elvis Is Back!', 'Fever');
001218 }
001219 } {1 {FOREIGN KEY constraint failed}}
001220
001221
001222 #-------------------------------------------------------------------------
001223 # EVIDENCE-OF: R-33626-48418 In SQLite, if any of the child key columns
001224 # (in this case songartist and songalbum) are NULL, then there is no
001225 # requirement for a corresponding row in the parent table.
001226 #
001227 do_test e_fkey-30.1 {
001228 execsql {
001229 INSERT INTO song VALUES(2, 'Elvis Presley', NULL, 'Fever');
001230 INSERT INTO song VALUES(3, NULL, 'Elvis Is Back', 'Soldier Boy');
001231 }
001232 } {}
001233
001234 ###########################################################################
001235 ### SECTION 4.2: Deferred Foreign Key Constraints
001236 ###########################################################################
001237
001238 #-------------------------------------------------------------------------
001239 # Test that if a statement violates an immediate FK constraint, and the
001240 # database does not satisfy the FK constraint once all effects of the
001241 # statement have been applied, an error is reported and the effects of
001242 # the statement rolled back.
001243 #
001244 # EVIDENCE-OF: R-09323-30470 If a statement modifies the contents of the
001245 # database so that an immediate foreign key constraint is in violation
001246 # at the conclusion the statement, an exception is thrown and the
001247 # effects of the statement are reverted.
001248 #
001249 drop_all_tables
001250 do_test e_fkey-31.1 {
001251 execsql {
001252 CREATE TABLE king(a, b, PRIMARY KEY(a));
001253 CREATE TABLE prince(c REFERENCES king, d);
001254 }
001255 } {}
001256
001257 do_test e_fkey-31.2 {
001258 # Execute a statement that violates the immediate FK constraint.
001259 catchsql { INSERT INTO prince VALUES(1, 2) }
001260 } {1 {FOREIGN KEY constraint failed}}
001261
001262 do_test e_fkey-31.3 {
001263 # This time, use a trigger to fix the constraint violation before the
001264 # statement has finished executing. Then execute the same statement as
001265 # in the previous test case. This time, no error.
001266 execsql {
001267 CREATE TRIGGER kt AFTER INSERT ON prince WHEN
001268 NOT EXISTS (SELECT a FROM king WHERE a = new.c)
001269 BEGIN
001270 INSERT INTO king VALUES(new.c, NULL);
001271 END
001272 }
001273 execsql { INSERT INTO prince VALUES(1, 2) }
001274 } {}
001275
001276 # Test that operating inside a transaction makes no difference to
001277 # immediate constraint violation handling.
001278 do_test e_fkey-31.4 {
001279 execsql {
001280 BEGIN;
001281 INSERT INTO prince VALUES(2, 3);
001282 DROP TRIGGER kt;
001283 }
001284 catchsql { INSERT INTO prince VALUES(3, 4) }
001285 } {1 {FOREIGN KEY constraint failed}}
001286 do_test e_fkey-31.5 {
001287 execsql {
001288 COMMIT;
001289 SELECT * FROM king;
001290 }
001291 } {1 {} 2 {}}
001292
001293 #-------------------------------------------------------------------------
001294 # Test that if a deferred constraint is violated within a transaction,
001295 # nothing happens immediately and the database is allowed to persist
001296 # in a state that does not satisfy the FK constraint. However attempts
001297 # to COMMIT the transaction fail until the FK constraint is satisfied.
001298 #
001299 # EVIDENCE-OF: R-49178-21358 By contrast, if a statement modifies the
001300 # contents of the database such that a deferred foreign key constraint
001301 # is violated, the violation is not reported immediately.
001302 #
001303 # EVIDENCE-OF: R-39692-12488 Deferred foreign key constraints are not
001304 # checked until the transaction tries to COMMIT.
001305 #
001306 # EVIDENCE-OF: R-55147-47664 For as long as the user has an open
001307 # transaction, the database is allowed to exist in a state that violates
001308 # any number of deferred foreign key constraints.
001309 #
001310 # EVIDENCE-OF: R-29604-30395 However, COMMIT will fail as long as
001311 # foreign key constraints remain in violation.
001312 #
001313 proc test_efkey_34 {tn isError sql} {
001314 do_test e_fkey-32.$tn "
001315 catchsql {$sql}
001316 " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]
001317 }
001318 drop_all_tables
001319
001320 test_efkey_34 1 0 {
001321 CREATE TABLE ll(k PRIMARY KEY);
001322 CREATE TABLE kk(c REFERENCES ll DEFERRABLE INITIALLY DEFERRED);
001323 }
001324 test_efkey_34 2 0 "BEGIN"
001325 test_efkey_34 3 0 "INSERT INTO kk VALUES(5)"
001326 test_efkey_34 4 0 "INSERT INTO kk VALUES(10)"
001327 test_efkey_34 5 1 "COMMIT"
001328 test_efkey_34 6 0 "INSERT INTO ll VALUES(10)"
001329 test_efkey_34 7 1 "COMMIT"
001330 test_efkey_34 8 0 "INSERT INTO ll VALUES(5)"
001331 test_efkey_34 9 0 "COMMIT"
001332
001333 #-------------------------------------------------------------------------
001334 # When not running inside a transaction, a deferred constraint is similar
001335 # to an immediate constraint (violations are reported immediately).
001336 #
001337 # EVIDENCE-OF: R-56844-61705 If the current statement is not inside an
001338 # explicit transaction (a BEGIN/COMMIT/ROLLBACK block), then an implicit
001339 # transaction is committed as soon as the statement has finished
001340 # executing. In this case deferred constraints behave the same as
001341 # immediate constraints.
001342 #
001343 drop_all_tables
001344 proc test_efkey_35 {tn isError sql} {
001345 do_test e_fkey-33.$tn "
001346 catchsql {$sql}
001347 " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]
001348 }
001349 do_test e_fkey-33.1 {
001350 execsql {
001351 CREATE TABLE parent(x, y);
001352 CREATE UNIQUE INDEX pi ON parent(x, y);
001353 CREATE TABLE child(a, b,
001354 FOREIGN KEY(a, b) REFERENCES parent(x, y) DEFERRABLE INITIALLY DEFERRED
001355 );
001356 }
001357 } {}
001358 test_efkey_35 2 1 "INSERT INTO child VALUES('x', 'y')"
001359 test_efkey_35 3 0 "INSERT INTO parent VALUES('x', 'y')"
001360 test_efkey_35 4 0 "INSERT INTO child VALUES('x', 'y')"
001361
001362
001363 #-------------------------------------------------------------------------
001364 # EVIDENCE-OF: R-12782-61841
001365 #
001366 # Test that an FK constraint is made deferred by adding the following
001367 # to the definition:
001368 #
001369 # DEFERRABLE INITIALLY DEFERRED
001370 #
001371 # EVIDENCE-OF: R-09005-28791
001372 #
001373 # Also test that adding any of the following to a foreign key definition
001374 # makes the constraint IMMEDIATE:
001375 #
001376 # NOT DEFERRABLE INITIALLY DEFERRED
001377 # NOT DEFERRABLE INITIALLY IMMEDIATE
001378 # NOT DEFERRABLE
001379 # DEFERRABLE INITIALLY IMMEDIATE
001380 # DEFERRABLE
001381 #
001382 # Foreign keys are IMMEDIATE by default (if there is no DEFERRABLE or NOT
001383 # DEFERRABLE clause).
001384 #
001385 # EVIDENCE-OF: R-35290-16460 Foreign key constraints are immediate by
001386 # default.
001387 #
001388 # EVIDENCE-OF: R-30323-21917 Each foreign key constraint in SQLite is
001389 # classified as either immediate or deferred.
001390 #
001391 drop_all_tables
001392 do_test e_fkey-34.1 {
001393 execsql {
001394 CREATE TABLE parent(x, y, z, PRIMARY KEY(x,y,z));
001395 CREATE TABLE c1(a, b, c,
001396 FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY DEFERRED
001397 );
001398 CREATE TABLE c2(a, b, c,
001399 FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY IMMEDIATE
001400 );
001401 CREATE TABLE c3(a, b, c,
001402 FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE
001403 );
001404 CREATE TABLE c4(a, b, c,
001405 FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE INITIALLY IMMEDIATE
001406 );
001407 CREATE TABLE c5(a, b, c,
001408 FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE
001409 );
001410 CREATE TABLE c6(a, b, c, FOREIGN KEY(a, b, c) REFERENCES parent);
001411
001412 -- This FK constraint is the only deferrable one.
001413 CREATE TABLE c7(a, b, c,
001414 FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE INITIALLY DEFERRED
001415 );
001416
001417 INSERT INTO parent VALUES('a', 'b', 'c');
001418 INSERT INTO parent VALUES('d', 'e', 'f');
001419 INSERT INTO parent VALUES('g', 'h', 'i');
001420 INSERT INTO parent VALUES('j', 'k', 'l');
001421 INSERT INTO parent VALUES('m', 'n', 'o');
001422 INSERT INTO parent VALUES('p', 'q', 'r');
001423 INSERT INTO parent VALUES('s', 't', 'u');
001424
001425 INSERT INTO c1 VALUES('a', 'b', 'c');
001426 INSERT INTO c2 VALUES('d', 'e', 'f');
001427 INSERT INTO c3 VALUES('g', 'h', 'i');
001428 INSERT INTO c4 VALUES('j', 'k', 'l');
001429 INSERT INTO c5 VALUES('m', 'n', 'o');
001430 INSERT INTO c6 VALUES('p', 'q', 'r');
001431 INSERT INTO c7 VALUES('s', 't', 'u');
001432 }
001433 } {}
001434
001435 proc test_efkey_29 {tn sql isError} {
001436 do_test e_fkey-34.$tn "catchsql {$sql}" [
001437 lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError
001438 ]
001439 }
001440 test_efkey_29 2 "BEGIN" 0
001441 test_efkey_29 3 "DELETE FROM parent WHERE x = 'a'" 1
001442 test_efkey_29 4 "DELETE FROM parent WHERE x = 'd'" 1
001443 test_efkey_29 5 "DELETE FROM parent WHERE x = 'g'" 1
001444 test_efkey_29 6 "DELETE FROM parent WHERE x = 'j'" 1
001445 test_efkey_29 7 "DELETE FROM parent WHERE x = 'm'" 1
001446 test_efkey_29 8 "DELETE FROM parent WHERE x = 'p'" 1
001447 test_efkey_29 9 "DELETE FROM parent WHERE x = 's'" 0
001448 test_efkey_29 10 "COMMIT" 1
001449 test_efkey_29 11 "ROLLBACK" 0
001450
001451 test_efkey_29 9 "BEGIN" 0
001452 test_efkey_29 10 "UPDATE parent SET z = 'z' WHERE z = 'c'" 1
001453 test_efkey_29 11 "UPDATE parent SET z = 'z' WHERE z = 'f'" 1
001454 test_efkey_29 12 "UPDATE parent SET z = 'z' WHERE z = 'i'" 1
001455 test_efkey_29 13 "UPDATE parent SET z = 'z' WHERE z = 'l'" 1
001456 test_efkey_29 14 "UPDATE parent SET z = 'z' WHERE z = 'o'" 1
001457 test_efkey_29 15 "UPDATE parent SET z = 'z' WHERE z = 'r'" 1
001458 test_efkey_29 16 "UPDATE parent SET z = 'z' WHERE z = 'u'" 0
001459 test_efkey_29 17 "COMMIT" 1
001460 test_efkey_29 18 "ROLLBACK" 0
001461
001462 test_efkey_29 17 "BEGIN" 0
001463 test_efkey_29 18 "INSERT INTO c1 VALUES(1, 2, 3)" 1
001464 test_efkey_29 19 "INSERT INTO c2 VALUES(1, 2, 3)" 1
001465 test_efkey_29 20 "INSERT INTO c3 VALUES(1, 2, 3)" 1
001466 test_efkey_29 21 "INSERT INTO c4 VALUES(1, 2, 3)" 1
001467 test_efkey_29 22 "INSERT INTO c5 VALUES(1, 2, 3)" 1
001468 test_efkey_29 22 "INSERT INTO c6 VALUES(1, 2, 3)" 1
001469 test_efkey_29 22 "INSERT INTO c7 VALUES(1, 2, 3)" 0
001470 test_efkey_29 23 "COMMIT" 1
001471 test_efkey_29 24 "INSERT INTO parent VALUES(1, 2, 3)" 0
001472 test_efkey_29 25 "COMMIT" 0
001473
001474 test_efkey_29 26 "BEGIN" 0
001475 test_efkey_29 27 "UPDATE c1 SET a = 10" 1
001476 test_efkey_29 28 "UPDATE c2 SET a = 10" 1
001477 test_efkey_29 29 "UPDATE c3 SET a = 10" 1
001478 test_efkey_29 30 "UPDATE c4 SET a = 10" 1
001479 test_efkey_29 31 "UPDATE c5 SET a = 10" 1
001480 test_efkey_29 31 "UPDATE c6 SET a = 10" 1
001481 test_efkey_29 31 "UPDATE c7 SET a = 10" 0
001482 test_efkey_29 32 "COMMIT" 1
001483 test_efkey_29 33 "ROLLBACK" 0
001484
001485 #-------------------------------------------------------------------------
001486 # EVIDENCE-OF: R-24499-57071
001487 #
001488 # Test an example from foreignkeys.html dealing with a deferred foreign
001489 # key constraint.
001490 #
001491 do_test e_fkey-35.1 {
001492 drop_all_tables
001493 execsql {
001494 CREATE TABLE artist(
001495 artistid INTEGER PRIMARY KEY,
001496 artistname TEXT
001497 );
001498 CREATE TABLE track(
001499 trackid INTEGER,
001500 trackname TEXT,
001501 trackartist INTEGER REFERENCES artist(artistid) DEFERRABLE INITIALLY DEFERRED
001502 );
001503 }
001504 } {}
001505 do_test e_fkey-35.2 {
001506 execsql {
001507 BEGIN;
001508 INSERT INTO track VALUES(1, 'White Christmas', 5);
001509 }
001510 catchsql COMMIT
001511 } {1 {FOREIGN KEY constraint failed}}
001512 do_test e_fkey-35.3 {
001513 execsql {
001514 INSERT INTO artist VALUES(5, 'Bing Crosby');
001515 COMMIT;
001516 }
001517 } {}
001518
001519 #-------------------------------------------------------------------------
001520 # Verify that a nested savepoint may be released without satisfying
001521 # deferred foreign key constraints.
001522 #
001523 # EVIDENCE-OF: R-07223-48323 A nested savepoint transaction may be
001524 # RELEASEd while the database is in a state that does not satisfy a
001525 # deferred foreign key constraint.
001526 #
001527 drop_all_tables
001528 do_test e_fkey-36.1 {
001529 execsql {
001530 CREATE TABLE t1(a PRIMARY KEY,
001531 b REFERENCES t1 DEFERRABLE INITIALLY DEFERRED
001532 );
001533 INSERT INTO t1 VALUES(1, 1);
001534 INSERT INTO t1 VALUES(2, 2);
001535 INSERT INTO t1 VALUES(3, 3);
001536 }
001537 } {}
001538 do_test e_fkey-36.2 {
001539 execsql {
001540 BEGIN;
001541 SAVEPOINT one;
001542 INSERT INTO t1 VALUES(4, 5);
001543 RELEASE one;
001544 }
001545 } {}
001546 do_test e_fkey-36.3 {
001547 catchsql COMMIT
001548 } {1 {FOREIGN KEY constraint failed}}
001549 do_test e_fkey-36.4 {
001550 execsql {
001551 UPDATE t1 SET a = 5 WHERE a = 4;
001552 COMMIT;
001553 }
001554 } {}
001555
001556
001557 #-------------------------------------------------------------------------
001558 # Check that a transaction savepoint (an outermost savepoint opened when
001559 # the database was in auto-commit mode) cannot be released without
001560 # satisfying deferred foreign key constraints. It may be rolled back.
001561 #
001562 # EVIDENCE-OF: R-44295-13823 A transaction savepoint (a non-nested
001563 # savepoint that was opened while there was not currently an open
001564 # transaction), on the other hand, is subject to the same restrictions
001565 # as a COMMIT - attempting to RELEASE it while the database is in such a
001566 # state will fail.
001567 #
001568 do_test e_fkey-37.1 {
001569 execsql {
001570 SAVEPOINT one;
001571 SAVEPOINT two;
001572 INSERT INTO t1 VALUES(6, 7);
001573 RELEASE two;
001574 }
001575 } {}
001576 do_test e_fkey-37.2 {
001577 catchsql {RELEASE one}
001578 } {1 {FOREIGN KEY constraint failed}}
001579 do_test e_fkey-37.3 {
001580 execsql {
001581 UPDATE t1 SET a = 7 WHERE a = 6;
001582 RELEASE one;
001583 }
001584 } {}
001585 do_test e_fkey-37.4 {
001586 execsql {
001587 SAVEPOINT one;
001588 SAVEPOINT two;
001589 INSERT INTO t1 VALUES(9, 10);
001590 RELEASE two;
001591 }
001592 } {}
001593 do_test e_fkey-37.5 {
001594 catchsql {RELEASE one}
001595 } {1 {FOREIGN KEY constraint failed}}
001596 do_test e_fkey-37.6 {
001597 execsql {ROLLBACK TO one ; RELEASE one}
001598 } {}
001599
001600 #-------------------------------------------------------------------------
001601 # Test that if a COMMIT operation fails due to deferred foreign key
001602 # constraints, any nested savepoints remain open.
001603 #
001604 # EVIDENCE-OF: R-37736-42616 If a COMMIT statement (or the RELEASE of a
001605 # transaction SAVEPOINT) fails because the database is currently in a
001606 # state that violates a deferred foreign key constraint and there are
001607 # currently nested savepoints, the nested savepoints remain open.
001608 #
001609 do_test e_fkey-38.1 {
001610 execsql {
001611 DELETE FROM t1 WHERE a>3;
001612 SELECT * FROM t1;
001613 }
001614 } {1 1 2 2 3 3}
001615 do_test e_fkey-38.2 {
001616 execsql {
001617 BEGIN;
001618 INSERT INTO t1 VALUES(4, 4);
001619 SAVEPOINT one;
001620 INSERT INTO t1 VALUES(5, 6);
001621 SELECT * FROM t1;
001622 }
001623 } {1 1 2 2 3 3 4 4 5 6}
001624 do_test e_fkey-38.3 {
001625 catchsql COMMIT
001626 } {1 {FOREIGN KEY constraint failed}}
001627 do_test e_fkey-38.4 {
001628 execsql {
001629 ROLLBACK TO one;
001630 COMMIT;
001631 SELECT * FROM t1;
001632 }
001633 } {1 1 2 2 3 3 4 4}
001634
001635 do_test e_fkey-38.5 {
001636 execsql {
001637 SAVEPOINT a;
001638 INSERT INTO t1 VALUES(5, 5);
001639 SAVEPOINT b;
001640 INSERT INTO t1 VALUES(6, 7);
001641 SAVEPOINT c;
001642 INSERT INTO t1 VALUES(7, 8);
001643 }
001644 } {}
001645 do_test e_fkey-38.6 {
001646 catchsql {RELEASE a}
001647 } {1 {FOREIGN KEY constraint failed}}
001648 do_test e_fkey-38.7 {
001649 execsql {ROLLBACK TO c}
001650 catchsql {RELEASE a}
001651 } {1 {FOREIGN KEY constraint failed}}
001652 do_test e_fkey-38.8 {
001653 execsql {
001654 ROLLBACK TO b;
001655 RELEASE a;
001656 SELECT * FROM t1;
001657 }
001658 } {1 1 2 2 3 3 4 4 5 5}
001659
001660 ###########################################################################
001661 ### SECTION 4.3: ON DELETE and ON UPDATE Actions
001662 ###########################################################################
001663
001664 #-------------------------------------------------------------------------
001665 # Test that configured ON DELETE and ON UPDATE actions take place when
001666 # deleting or modifying rows of the parent table, respectively.
001667 #
001668 # EVIDENCE-OF: R-48270-44282 Foreign key ON DELETE and ON UPDATE clauses
001669 # are used to configure actions that take place when deleting rows from
001670 # the parent table (ON DELETE), or modifying the parent key values of
001671 # existing rows (ON UPDATE).
001672 #
001673 # Test that a single FK constraint may have different actions configured
001674 # for ON DELETE and ON UPDATE.
001675 #
001676 # EVIDENCE-OF: R-48124-63225 A single foreign key constraint may have
001677 # different actions configured for ON DELETE and ON UPDATE.
001678 #
001679 do_test e_fkey-39.1 {
001680 execsql {
001681 CREATE TABLE p(a, b PRIMARY KEY, c);
001682 CREATE TABLE c1(d, e, f DEFAULT 'k0' REFERENCES p
001683 ON UPDATE SET DEFAULT
001684 ON DELETE SET NULL
001685 );
001686
001687 INSERT INTO p VALUES(0, 'k0', '');
001688 INSERT INTO p VALUES(1, 'k1', 'I');
001689 INSERT INTO p VALUES(2, 'k2', 'II');
001690 INSERT INTO p VALUES(3, 'k3', 'III');
001691
001692 INSERT INTO c1 VALUES(1, 'xx', 'k1');
001693 INSERT INTO c1 VALUES(2, 'xx', 'k2');
001694 INSERT INTO c1 VALUES(3, 'xx', 'k3');
001695 }
001696 } {}
001697 do_test e_fkey-39.2 {
001698 execsql {
001699 UPDATE p SET b = 'k4' WHERE a = 1;
001700 SELECT * FROM c1;
001701 }
001702 } {1 xx k0 2 xx k2 3 xx k3}
001703 do_test e_fkey-39.3 {
001704 execsql {
001705 DELETE FROM p WHERE a = 2;
001706 SELECT * FROM c1;
001707 }
001708 } {1 xx k0 2 xx {} 3 xx k3}
001709 do_test e_fkey-39.4 {
001710 execsql {
001711 CREATE UNIQUE INDEX pi ON p(c);
001712 REPLACE INTO p VALUES(5, 'k5', 'III');
001713 SELECT * FROM c1;
001714 }
001715 } {1 xx k0 2 xx {} 3 xx {}}
001716
001717 #-------------------------------------------------------------------------
001718 # Each foreign key in the system has an ON UPDATE and ON DELETE action,
001719 # either "NO ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE".
001720 #
001721 # EVIDENCE-OF: R-33326-45252 The ON DELETE and ON UPDATE action
001722 # associated with each foreign key in an SQLite database is one of "NO
001723 # ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE".
001724 #
001725 # If none is specified explicitly, "NO ACTION" is the default.
001726 #
001727 # EVIDENCE-OF: R-19803-45884 If an action is not explicitly specified,
001728 # it defaults to "NO ACTION".
001729 #
001730 drop_all_tables
001731 do_test e_fkey-40.1 {
001732 execsql {
001733 CREATE TABLE parent(x PRIMARY KEY, y);
001734 CREATE TABLE child1(a,
001735 b REFERENCES parent ON UPDATE NO ACTION ON DELETE RESTRICT
001736 );
001737 CREATE TABLE child2(a,
001738 b REFERENCES parent ON UPDATE RESTRICT ON DELETE SET NULL
001739 );
001740 CREATE TABLE child3(a,
001741 b REFERENCES parent ON UPDATE SET NULL ON DELETE SET DEFAULT
001742 );
001743 CREATE TABLE child4(a,
001744 b REFERENCES parent ON UPDATE SET DEFAULT ON DELETE CASCADE
001745 );
001746
001747 -- Create some foreign keys that use the default action - "NO ACTION"
001748 CREATE TABLE child5(a, b REFERENCES parent ON UPDATE CASCADE);
001749 CREATE TABLE child6(a, b REFERENCES parent ON DELETE RESTRICT);
001750 CREATE TABLE child7(a, b REFERENCES parent ON DELETE NO ACTION);
001751 CREATE TABLE child8(a, b REFERENCES parent ON UPDATE NO ACTION);
001752 }
001753 } {}
001754
001755 foreach {tn zTab lRes} {
001756 2 child1 {0 0 parent b {} {NO ACTION} RESTRICT NONE}
001757 3 child2 {0 0 parent b {} RESTRICT {SET NULL} NONE}
001758 4 child3 {0 0 parent b {} {SET NULL} {SET DEFAULT} NONE}
001759 5 child4 {0 0 parent b {} {SET DEFAULT} CASCADE NONE}
001760 6 child5 {0 0 parent b {} CASCADE {NO ACTION} NONE}
001761 7 child6 {0 0 parent b {} {NO ACTION} RESTRICT NONE}
001762 8 child7 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE}
001763 9 child8 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE}
001764 } {
001765 do_test e_fkey-40.$tn { execsql "PRAGMA foreign_key_list($zTab)" } $lRes
001766 }
001767
001768 #-------------------------------------------------------------------------
001769 # Test that "NO ACTION" means that nothing happens to a child row when
001770 # it's parent row is updated or deleted.
001771 #
001772 # EVIDENCE-OF: R-19971-54976 Configuring "NO ACTION" means just that:
001773 # when a parent key is modified or deleted from the database, no special
001774 # action is taken.
001775 #
001776 drop_all_tables
001777 do_test e_fkey-41.1 {
001778 execsql {
001779 CREATE TABLE parent(p1, p2, PRIMARY KEY(p1, p2));
001780 CREATE TABLE child(c1, c2,
001781 FOREIGN KEY(c1, c2) REFERENCES parent
001782 ON UPDATE NO ACTION
001783 ON DELETE NO ACTION
001784 DEFERRABLE INITIALLY DEFERRED
001785 );
001786 INSERT INTO parent VALUES('j', 'k');
001787 INSERT INTO parent VALUES('l', 'm');
001788 INSERT INTO child VALUES('j', 'k');
001789 INSERT INTO child VALUES('l', 'm');
001790 }
001791 } {}
001792 do_test e_fkey-41.2 {
001793 execsql {
001794 BEGIN;
001795 UPDATE parent SET p1='k' WHERE p1='j';
001796 DELETE FROM parent WHERE p1='l';
001797 SELECT * FROM child;
001798 }
001799 } {j k l m}
001800 do_test e_fkey-41.3 {
001801 catchsql COMMIT
001802 } {1 {FOREIGN KEY constraint failed}}
001803 do_test e_fkey-41.4 {
001804 execsql ROLLBACK
001805 } {}
001806
001807 #-------------------------------------------------------------------------
001808 # Test that "RESTRICT" means the application is prohibited from deleting
001809 # or updating a parent table row when there exists one or more child keys
001810 # mapped to it.
001811 #
001812 # EVIDENCE-OF: R-04272-38653 The "RESTRICT" action means that the
001813 # application is prohibited from deleting (for ON DELETE RESTRICT) or
001814 # modifying (for ON UPDATE RESTRICT) a parent key when there exists one
001815 # or more child keys mapped to it.
001816 #
001817 drop_all_tables
001818 do_test e_fkey-41.1 {
001819 execsql {
001820 CREATE TABLE parent(p1, p2);
001821 CREATE UNIQUE INDEX parent_i ON parent(p1, p2);
001822 CREATE TABLE child1(c1, c2,
001823 FOREIGN KEY(c2, c1) REFERENCES parent(p1, p2) ON DELETE RESTRICT
001824 );
001825 CREATE TABLE child2(c1, c2,
001826 FOREIGN KEY(c2, c1) REFERENCES parent(p1, p2) ON UPDATE RESTRICT
001827 );
001828 }
001829 } {}
001830 do_test e_fkey-41.2 {
001831 execsql {
001832 INSERT INTO parent VALUES('a', 'b');
001833 INSERT INTO parent VALUES('c', 'd');
001834 INSERT INTO child1 VALUES('b', 'a');
001835 INSERT INTO child2 VALUES('d', 'c');
001836 }
001837 } {}
001838 do_test e_fkey-41.3 {
001839 catchsql { DELETE FROM parent WHERE p1 = 'a' }
001840 } {1 {FOREIGN KEY constraint failed}}
001841 do_test e_fkey-41.4 {
001842 catchsql { UPDATE parent SET p2 = 'e' WHERE p1 = 'c' }
001843 } {1 {FOREIGN KEY constraint failed}}
001844
001845 #-------------------------------------------------------------------------
001846 # Test that RESTRICT is slightly different from NO ACTION for IMMEDIATE
001847 # constraints, in that it is enforced immediately, not at the end of the
001848 # statement.
001849 #
001850 # EVIDENCE-OF: R-37997-42187 The difference between the effect of a
001851 # RESTRICT action and normal foreign key constraint enforcement is that
001852 # the RESTRICT action processing happens as soon as the field is updated
001853 # - not at the end of the current statement as it would with an
001854 # immediate constraint, or at the end of the current transaction as it
001855 # would with a deferred constraint.
001856 #
001857 drop_all_tables
001858 do_test e_fkey-42.1 {
001859 execsql {
001860 CREATE TABLE parent(x PRIMARY KEY);
001861 CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT);
001862 CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION);
001863
001864 INSERT INTO parent VALUES('key1');
001865 INSERT INTO parent VALUES('key2');
001866 INSERT INTO child1 VALUES('key1');
001867 INSERT INTO child2 VALUES('key2');
001868
001869 CREATE TRIGGER parent_t AFTER UPDATE ON parent BEGIN
001870 UPDATE child1 set c = new.x WHERE c = old.x;
001871 UPDATE child2 set c = new.x WHERE c = old.x;
001872 END;
001873 }
001874 } {}
001875 do_test e_fkey-42.2 {
001876 catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' }
001877 } {1 {FOREIGN KEY constraint failed}}
001878 do_test e_fkey-42.3 {
001879 execsql {
001880 UPDATE parent SET x = 'key two' WHERE x = 'key2';
001881 SELECT * FROM child2;
001882 }
001883 } {{key two}}
001884
001885 drop_all_tables
001886 do_test e_fkey-42.4 {
001887 execsql {
001888 CREATE TABLE parent(x PRIMARY KEY);
001889 CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT);
001890 CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION);
001891
001892 INSERT INTO parent VALUES('key1');
001893 INSERT INTO parent VALUES('key2');
001894 INSERT INTO child1 VALUES('key1');
001895 INSERT INTO child2 VALUES('key2');
001896
001897 CREATE TRIGGER parent_t AFTER DELETE ON parent BEGIN
001898 UPDATE child1 SET c = NULL WHERE c = old.x;
001899 UPDATE child2 SET c = NULL WHERE c = old.x;
001900 END;
001901 }
001902 } {}
001903 do_test e_fkey-42.5 {
001904 catchsql { DELETE FROM parent WHERE x = 'key1' }
001905 } {1 {FOREIGN KEY constraint failed}}
001906 do_test e_fkey-42.6 {
001907 execsql {
001908 DELETE FROM parent WHERE x = 'key2';
001909 SELECT * FROM child2;
001910 }
001911 } {{}}
001912
001913 drop_all_tables
001914 do_test e_fkey-42.7 {
001915 execsql {
001916 CREATE TABLE parent(x PRIMARY KEY);
001917 CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT);
001918 CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION);
001919
001920 INSERT INTO parent VALUES('key1');
001921 INSERT INTO parent VALUES('key2');
001922 INSERT INTO child1 VALUES('key1');
001923 INSERT INTO child2 VALUES('key2');
001924 }
001925 } {}
001926 do_test e_fkey-42.8 {
001927 catchsql { REPLACE INTO parent VALUES('key1') }
001928 } {1 {FOREIGN KEY constraint failed}}
001929 do_test e_fkey-42.9 {
001930 execsql {
001931 REPLACE INTO parent VALUES('key2');
001932 SELECT * FROM child2;
001933 }
001934 } {key2}
001935
001936 #-------------------------------------------------------------------------
001937 # Test that RESTRICT is enforced immediately, even for a DEFERRED constraint.
001938 #
001939 # EVIDENCE-OF: R-24179-60523 Even if the foreign key constraint it is
001940 # attached to is deferred, configuring a RESTRICT action causes SQLite
001941 # to return an error immediately if a parent key with dependent child
001942 # keys is deleted or modified.
001943 #
001944 drop_all_tables
001945 do_test e_fkey-43.1 {
001946 execsql {
001947 CREATE TABLE parent(x PRIMARY KEY);
001948 CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT
001949 DEFERRABLE INITIALLY DEFERRED
001950 );
001951 CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION
001952 DEFERRABLE INITIALLY DEFERRED
001953 );
001954
001955 INSERT INTO parent VALUES('key1');
001956 INSERT INTO parent VALUES('key2');
001957 INSERT INTO child1 VALUES('key1');
001958 INSERT INTO child2 VALUES('key2');
001959 BEGIN;
001960 }
001961 } {}
001962 do_test e_fkey-43.2 {
001963 catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' }
001964 } {1 {FOREIGN KEY constraint failed}}
001965 do_test e_fkey-43.3 {
001966 execsql { UPDATE parent SET x = 'key two' WHERE x = 'key2' }
001967 } {}
001968 do_test e_fkey-43.4 {
001969 catchsql COMMIT
001970 } {1 {FOREIGN KEY constraint failed}}
001971 do_test e_fkey-43.5 {
001972 execsql {
001973 UPDATE child2 SET c = 'key two';
001974 COMMIT;
001975 }
001976 } {}
001977
001978 drop_all_tables
001979 do_test e_fkey-43.6 {
001980 execsql {
001981 CREATE TABLE parent(x PRIMARY KEY);
001982 CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT
001983 DEFERRABLE INITIALLY DEFERRED
001984 );
001985 CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION
001986 DEFERRABLE INITIALLY DEFERRED
001987 );
001988
001989 INSERT INTO parent VALUES('key1');
001990 INSERT INTO parent VALUES('key2');
001991 INSERT INTO child1 VALUES('key1');
001992 INSERT INTO child2 VALUES('key2');
001993 BEGIN;
001994 }
001995 } {}
001996 do_test e_fkey-43.7 {
001997 catchsql { DELETE FROM parent WHERE x = 'key1' }
001998 } {1 {FOREIGN KEY constraint failed}}
001999 do_test e_fkey-43.8 {
002000 execsql { DELETE FROM parent WHERE x = 'key2' }
002001 } {}
002002 do_test e_fkey-43.9 {
002003 catchsql COMMIT
002004 } {1 {FOREIGN KEY constraint failed}}
002005 do_test e_fkey-43.10 {
002006 execsql {
002007 UPDATE child2 SET c = NULL;
002008 COMMIT;
002009 }
002010 } {}
002011
002012 #-------------------------------------------------------------------------
002013 # Test SET NULL actions.
002014 #
002015 # EVIDENCE-OF: R-03353-05327 If the configured action is "SET NULL",
002016 # then when a parent key is deleted (for ON DELETE SET NULL) or modified
002017 # (for ON UPDATE SET NULL), the child key columns of all rows in the
002018 # child table that mapped to the parent key are set to contain SQL NULL
002019 # values.
002020 #
002021 drop_all_tables
002022 do_test e_fkey-44.1 {
002023 execsql {
002024 CREATE TABLE pA(x PRIMARY KEY);
002025 CREATE TABLE cA(c REFERENCES pA ON DELETE SET NULL);
002026 CREATE TABLE cB(c REFERENCES pA ON UPDATE SET NULL);
002027
002028 INSERT INTO pA VALUES(X'ABCD');
002029 INSERT INTO pA VALUES(X'1234');
002030 INSERT INTO cA VALUES(X'ABCD');
002031 INSERT INTO cB VALUES(X'1234');
002032 }
002033 } {}
002034 do_test e_fkey-44.2 {
002035 execsql {
002036 DELETE FROM pA WHERE rowid = 1;
002037 SELECT quote(x) FROM pA;
002038 }
002039 } {X'1234'}
002040 do_test e_fkey-44.3 {
002041 execsql {
002042 SELECT quote(c) FROM cA;
002043 }
002044 } {NULL}
002045 do_test e_fkey-44.4 {
002046 execsql {
002047 UPDATE pA SET x = X'8765' WHERE rowid = 2;
002048 SELECT quote(x) FROM pA;
002049 }
002050 } {X'8765'}
002051 do_test e_fkey-44.5 {
002052 execsql { SELECT quote(c) FROM cB }
002053 } {NULL}
002054
002055 #-------------------------------------------------------------------------
002056 # Test SET DEFAULT actions.
002057 #
002058 # EVIDENCE-OF: R-55814-22637 The "SET DEFAULT" actions are similar to
002059 # "SET NULL", except that each of the child key columns is set to
002060 # contain the column's default value instead of NULL.
002061 #
002062 drop_all_tables
002063 do_test e_fkey-45.1 {
002064 execsql {
002065 CREATE TABLE pA(x PRIMARY KEY);
002066 CREATE TABLE cA(c DEFAULT X'0000' REFERENCES pA ON DELETE SET DEFAULT);
002067 CREATE TABLE cB(c DEFAULT X'9999' REFERENCES pA ON UPDATE SET DEFAULT);
002068
002069 INSERT INTO pA(rowid, x) VALUES(1, X'0000');
002070 INSERT INTO pA(rowid, x) VALUES(2, X'9999');
002071 INSERT INTO pA(rowid, x) VALUES(3, X'ABCD');
002072 INSERT INTO pA(rowid, x) VALUES(4, X'1234');
002073
002074 INSERT INTO cA VALUES(X'ABCD');
002075 INSERT INTO cB VALUES(X'1234');
002076 }
002077 } {}
002078 do_test e_fkey-45.2 {
002079 execsql {
002080 DELETE FROM pA WHERE rowid = 3;
002081 SELECT quote(x) FROM pA ORDER BY rowid;
002082 }
002083 } {X'0000' X'9999' X'1234'}
002084 do_test e_fkey-45.3 {
002085 execsql { SELECT quote(c) FROM cA }
002086 } {X'0000'}
002087 do_test e_fkey-45.4 {
002088 execsql {
002089 UPDATE pA SET x = X'8765' WHERE rowid = 4;
002090 SELECT quote(x) FROM pA ORDER BY rowid;
002091 }
002092 } {X'0000' X'9999' X'8765'}
002093 do_test e_fkey-45.5 {
002094 execsql { SELECT quote(c) FROM cB }
002095 } {X'9999'}
002096
002097 #-------------------------------------------------------------------------
002098 # Test ON DELETE CASCADE actions.
002099 #
002100 # EVIDENCE-OF: R-61376-57267 A "CASCADE" action propagates the delete or
002101 # update operation on the parent key to each dependent child key.
002102 #
002103 # EVIDENCE-OF: R-61809-62207 For an "ON DELETE CASCADE" action, this
002104 # means that each row in the child table that was associated with the
002105 # deleted parent row is also deleted.
002106 #
002107 drop_all_tables
002108 do_test e_fkey-46.1 {
002109 execsql {
002110 CREATE TABLE p1(a, b UNIQUE);
002111 CREATE TABLE c1(c REFERENCES p1(b) ON DELETE CASCADE, d);
002112 INSERT INTO p1 VALUES(NULL, NULL);
002113 INSERT INTO p1 VALUES(4, 4);
002114 INSERT INTO p1 VALUES(5, 5);
002115 INSERT INTO c1 VALUES(NULL, NULL);
002116 INSERT INTO c1 VALUES(4, 4);
002117 INSERT INTO c1 VALUES(5, 5);
002118 SELECT count(*) FROM c1;
002119 }
002120 } {3}
002121 do_test e_fkey-46.2 {
002122 execsql {
002123 DELETE FROM p1 WHERE a = 4;
002124 SELECT d, c FROM c1;
002125 }
002126 } {{} {} 5 5}
002127 do_test e_fkey-46.3 {
002128 execsql {
002129 DELETE FROM p1;
002130 SELECT d, c FROM c1;
002131 }
002132 } {{} {}}
002133 do_test e_fkey-46.4 {
002134 execsql { SELECT * FROM p1 }
002135 } {}
002136
002137
002138 #-------------------------------------------------------------------------
002139 # Test ON UPDATE CASCADE actions.
002140 #
002141 # EVIDENCE-OF: R-13877-64542 For an "ON UPDATE CASCADE" action, it means
002142 # that the values stored in each dependent child key are modified to
002143 # match the new parent key values.
002144 #
002145 # EVIDENCE-OF: R-61376-57267 A "CASCADE" action propagates the delete or
002146 # update operation on the parent key to each dependent child key.
002147 #
002148 drop_all_tables
002149 do_test e_fkey-47.1 {
002150 execsql {
002151 CREATE TABLE p1(a, b UNIQUE);
002152 CREATE TABLE c1(c REFERENCES p1(b) ON UPDATE CASCADE, d);
002153 INSERT INTO p1 VALUES(NULL, NULL);
002154 INSERT INTO p1 VALUES(4, 4);
002155 INSERT INTO p1 VALUES(5, 5);
002156 INSERT INTO c1 VALUES(NULL, NULL);
002157 INSERT INTO c1 VALUES(4, 4);
002158 INSERT INTO c1 VALUES(5, 5);
002159 SELECT count(*) FROM c1;
002160 }
002161 } {3}
002162 do_test e_fkey-47.2 {
002163 execsql {
002164 UPDATE p1 SET b = 10 WHERE b = 5;
002165 SELECT d, c FROM c1;
002166 }
002167 } {{} {} 4 4 5 10}
002168 do_test e_fkey-47.3 {
002169 execsql {
002170 UPDATE p1 SET b = 11 WHERE b = 4;
002171 SELECT d, c FROM c1;
002172 }
002173 } {{} {} 4 11 5 10}
002174 do_test e_fkey-47.4 {
002175 execsql {
002176 UPDATE p1 SET b = 6 WHERE b IS NULL;
002177 SELECT d, c FROM c1;
002178 }
002179 } {{} {} 4 11 5 10}
002180 do_test e_fkey-46.5 {
002181 execsql { SELECT * FROM p1 }
002182 } {{} 6 4 11 5 10}
002183
002184 #-------------------------------------------------------------------------
002185 # EVIDENCE-OF: R-65058-57158
002186 #
002187 # Test an example from the "ON DELETE and ON UPDATE Actions" section
002188 # of foreignkeys.html.
002189 #
002190 drop_all_tables
002191 do_test e_fkey-48.1 {
002192 execsql {
002193 CREATE TABLE artist(
002194 artistid INTEGER PRIMARY KEY,
002195 artistname TEXT
002196 );
002197 CREATE TABLE track(
002198 trackid INTEGER,
002199 trackname TEXT,
002200 trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE
002201 );
002202
002203 INSERT INTO artist VALUES(1, 'Dean Martin');
002204 INSERT INTO artist VALUES(2, 'Frank Sinatra');
002205 INSERT INTO track VALUES(11, 'That''s Amore', 1);
002206 INSERT INTO track VALUES(12, 'Christmas Blues', 1);
002207 INSERT INTO track VALUES(13, 'My Way', 2);
002208 }
002209 } {}
002210 do_test e_fkey-48.2 {
002211 execsql {
002212 UPDATE artist SET artistid = 100 WHERE artistname = 'Dean Martin';
002213 }
002214 } {}
002215 do_test e_fkey-48.3 {
002216 execsql { SELECT * FROM artist }
002217 } {2 {Frank Sinatra} 100 {Dean Martin}}
002218 do_test e_fkey-48.4 {
002219 execsql { SELECT * FROM track }
002220 } {11 {That's Amore} 100 12 {Christmas Blues} 100 13 {My Way} 2}
002221
002222
002223 #-------------------------------------------------------------------------
002224 # Verify that adding an FK action does not absolve the user of the
002225 # requirement not to violate the foreign key constraint.
002226 #
002227 # EVIDENCE-OF: R-53968-51642 Configuring an ON UPDATE or ON DELETE
002228 # action does not mean that the foreign key constraint does not need to
002229 # be satisfied.
002230 #
002231 drop_all_tables
002232 do_test e_fkey-49.1 {
002233 execsql {
002234 CREATE TABLE parent(a COLLATE nocase, b, c, PRIMARY KEY(c, a));
002235 CREATE TABLE child(d DEFAULT 'a', e, f DEFAULT 'c',
002236 FOREIGN KEY(f, d) REFERENCES parent ON UPDATE SET DEFAULT
002237 );
002238
002239 INSERT INTO parent VALUES('A', 'b', 'c');
002240 INSERT INTO parent VALUES('ONE', 'two', 'three');
002241 INSERT INTO child VALUES('one', 'two', 'three');
002242 }
002243 } {}
002244 do_test e_fkey-49.2 {
002245 execsql {
002246 BEGIN;
002247 UPDATE parent SET a = '' WHERE a = 'oNe';
002248 SELECT * FROM child;
002249 }
002250 } {a two c}
002251 do_test e_fkey-49.3 {
002252 execsql {
002253 ROLLBACK;
002254 DELETE FROM parent WHERE a = 'A';
002255 SELECT * FROM parent;
002256 }
002257 } {ONE two three}
002258 do_test e_fkey-49.4 {
002259 catchsql { UPDATE parent SET a = '' WHERE a = 'oNe' }
002260 } {1 {FOREIGN KEY constraint failed}}
002261
002262
002263 #-------------------------------------------------------------------------
002264 # EVIDENCE-OF: R-11856-19836
002265 #
002266 # Test an example from the "ON DELETE and ON UPDATE Actions" section
002267 # of foreignkeys.html. This example shows that adding an "ON DELETE DEFAULT"
002268 # clause does not abrogate the need to satisfy the foreign key constraint
002269 # (R-28220-46694).
002270 #
002271 # EVIDENCE-OF: R-28220-46694 For example, if an "ON DELETE SET DEFAULT"
002272 # action is configured, but there is no row in the parent table that
002273 # corresponds to the default values of the child key columns, deleting a
002274 # parent key while dependent child keys exist still causes a foreign key
002275 # violation.
002276 #
002277 drop_all_tables
002278 do_test e_fkey-50.1 {
002279 execsql {
002280 CREATE TABLE artist(
002281 artistid INTEGER PRIMARY KEY,
002282 artistname TEXT
002283 );
002284 CREATE TABLE track(
002285 trackid INTEGER,
002286 trackname TEXT,
002287 trackartist INTEGER DEFAULT 0 REFERENCES artist(artistid) ON DELETE SET DEFAULT
002288 );
002289 INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
002290 INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);
002291 }
002292 } {}
002293 do_test e_fkey-50.2 {
002294 catchsql { DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.' }
002295 } {1 {FOREIGN KEY constraint failed}}
002296 do_test e_fkey-50.3 {
002297 execsql {
002298 INSERT INTO artist VALUES(0, 'Unknown Artist');
002299 DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';
002300 }
002301 } {}
002302 do_test e_fkey-50.4 {
002303 execsql { SELECT * FROM artist }
002304 } {0 {Unknown Artist}}
002305 do_test e_fkey-50.5 {
002306 execsql { SELECT * FROM track }
002307 } {14 {Mr. Bojangles} 0}
002308
002309 #-------------------------------------------------------------------------
002310 # EVIDENCE-OF: R-09564-22170
002311 #
002312 # Check that the order of steps in an UPDATE or DELETE on a parent
002313 # table is as follows:
002314 #
002315 # 1. Execute applicable BEFORE trigger programs,
002316 # 2. Check local (non foreign key) constraints,
002317 # 3. Update or delete the row in the parent table,
002318 # 4. Perform any required foreign key actions,
002319 # 5. Execute applicable AFTER trigger programs.
002320 #
002321 drop_all_tables
002322 do_test e_fkey-51.1 {
002323 proc maxparent {args} { db one {SELECT max(x) FROM parent} }
002324 db func maxparent maxparent
002325
002326 execsql {
002327 CREATE TABLE parent(x PRIMARY KEY);
002328
002329 CREATE TRIGGER bu BEFORE UPDATE ON parent BEGIN
002330 INSERT INTO parent VALUES(new.x-old.x);
002331 END;
002332 CREATE TABLE child(
002333 a DEFAULT (maxparent()) REFERENCES parent ON UPDATE SET DEFAULT
002334 );
002335 CREATE TRIGGER au AFTER UPDATE ON parent BEGIN
002336 INSERT INTO parent VALUES(new.x+old.x);
002337 END;
002338
002339 INSERT INTO parent VALUES(1);
002340 INSERT INTO child VALUES(1);
002341 }
002342 } {}
002343 do_test e_fkey-51.2 {
002344 execsql {
002345 UPDATE parent SET x = 22;
002346 SELECT * FROM parent ORDER BY rowid; SELECT 'xxx' ; SELECT a FROM child;
002347 }
002348 } {22 21 23 xxx 22}
002349 do_test e_fkey-51.3 {
002350 execsql {
002351 DELETE FROM child;
002352 DELETE FROM parent;
002353 INSERT INTO parent VALUES(-1);
002354 INSERT INTO child VALUES(-1);
002355 UPDATE parent SET x = 22;
002356 SELECT * FROM parent ORDER BY rowid; SELECT 'xxx' ; SELECT a FROM child;
002357 }
002358 } {22 23 21 xxx 23}
002359
002360
002361 #-------------------------------------------------------------------------
002362 # Verify that ON UPDATE actions only actually take place if the parent key
002363 # is set to a new value that is distinct from the old value. The default
002364 # collation sequence and affinity are used to determine if the new value
002365 # is 'distinct' from the old or not.
002366 #
002367 # EVIDENCE-OF: R-27383-10246 An ON UPDATE action is only taken if the
002368 # values of the parent key are modified so that the new parent key
002369 # values are not equal to the old.
002370 #
002371 drop_all_tables
002372 do_test e_fkey-52.1 {
002373 execsql {
002374 CREATE TABLE zeus(a INTEGER COLLATE NOCASE, b, PRIMARY KEY(a, b));
002375 CREATE TABLE apollo(c, d,
002376 FOREIGN KEY(c, d) REFERENCES zeus ON UPDATE CASCADE
002377 );
002378 INSERT INTO zeus VALUES('abc', 'xyz');
002379 INSERT INTO apollo VALUES('ABC', 'xyz');
002380 }
002381 execsql {
002382 UPDATE zeus SET a = 'aBc';
002383 SELECT * FROM apollo;
002384 }
002385 } {ABC xyz}
002386 do_test e_fkey-52.2 {
002387 execsql {
002388 UPDATE zeus SET a = 1, b = 1;
002389 SELECT * FROM apollo;
002390 }
002391 } {1 1}
002392 do_test e_fkey-52.3 {
002393 execsql {
002394 UPDATE zeus SET a = 1, b = 1;
002395 SELECT typeof(c), c, typeof(d), d FROM apollo;
002396 }
002397 } {integer 1 integer 1}
002398 do_test e_fkey-52.4 {
002399 execsql {
002400 UPDATE zeus SET a = '1';
002401 SELECT typeof(c), c, typeof(d), d FROM apollo;
002402 }
002403 } {integer 1 integer 1}
002404 do_test e_fkey-52.5 {
002405 execsql {
002406 UPDATE zeus SET b = '1';
002407 SELECT typeof(c), c, typeof(d), d FROM apollo;
002408 }
002409 } {integer 1 text 1}
002410 do_test e_fkey-52.6 {
002411 execsql {
002412 UPDATE zeus SET b = NULL;
002413 SELECT typeof(c), c, typeof(d), d FROM apollo;
002414 }
002415 } {integer 1 null {}}
002416
002417 #-------------------------------------------------------------------------
002418 # EVIDENCE-OF: R-35129-58141
002419 #
002420 # Test an example from the "ON DELETE and ON UPDATE Actions" section
002421 # of foreignkeys.html. This example demonstrates that ON UPDATE actions
002422 # only take place if at least one parent key column is set to a value
002423 # that is distinct from its previous value.
002424 #
002425 drop_all_tables
002426 do_test e_fkey-53.1 {
002427 execsql {
002428 CREATE TABLE parent(x PRIMARY KEY);
002429 CREATE TABLE child(y REFERENCES parent ON UPDATE SET NULL);
002430 INSERT INTO parent VALUES('key');
002431 INSERT INTO child VALUES('key');
002432 }
002433 } {}
002434 do_test e_fkey-53.2 {
002435 execsql {
002436 UPDATE parent SET x = 'key';
002437 SELECT IFNULL(y, 'null') FROM child;
002438 }
002439 } {key}
002440 do_test e_fkey-53.3 {
002441 execsql {
002442 UPDATE parent SET x = 'key2';
002443 SELECT IFNULL(y, 'null') FROM child;
002444 }
002445 } {null}
002446
002447 ###########################################################################
002448 ### SECTION 5: CREATE, ALTER and DROP TABLE commands
002449 ###########################################################################
002450
002451 #-------------------------------------------------------------------------
002452 # Test that parent keys are not checked when tables are created.
002453 #
002454 # EVIDENCE-OF: R-36018-21755 The parent key definitions of foreign key
002455 # constraints are not checked when a table is created.
002456 #
002457 # EVIDENCE-OF: R-25384-39337 There is nothing stopping the user from
002458 # creating a foreign key definition that refers to a parent table that
002459 # does not exist, or to parent key columns that do not exist or are not
002460 # collectively bound by a PRIMARY KEY or UNIQUE constraint.
002461 #
002462 # Child keys are checked to ensure all component columns exist. If parent
002463 # key columns are explicitly specified, SQLite checks to make sure there
002464 # are the same number of columns in the child and parent keys. (TODO: This
002465 # is tested but does not correspond to any testable statement.)
002466 #
002467 # Also test that the above statements are true regardless of whether or not
002468 # foreign keys are enabled: "A CREATE TABLE command operates the same whether
002469 # or not foreign key constraints are enabled."
002470 #
002471 # EVIDENCE-OF: R-08908-23439 A CREATE TABLE command operates the same
002472 # whether or not foreign key constraints are enabled.
002473 #
002474 foreach {tn zCreateTbl lRes} {
002475 1 "CREATE TABLE t1(a, b REFERENCES t1)" {0 {}}
002476 2 "CREATE TABLE t1(a, b REFERENCES t2)" {0 {}}
002477 3 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t1)" {0 {}}
002478 4 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2)" {0 {}}
002479 5 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2)" {0 {}}
002480 6 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2(n,d))" {0 {}}
002481 7 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t1(a,b))" {0 {}}
002482
002483 A "CREATE TABLE t1(a, b, FOREIGN KEY(c,b) REFERENCES t2)"
002484 {1 {unknown column "c" in foreign key definition}}
002485 B "CREATE TABLE t1(a, b, FOREIGN KEY(c,b) REFERENCES t2(d))"
002486 {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
002487 } {
002488 do_test e_fkey-54.$tn.off {
002489 drop_all_tables
002490 execsql {PRAGMA foreign_keys = OFF}
002491 catchsql $zCreateTbl
002492 } $lRes
002493 do_test e_fkey-54.$tn.on {
002494 drop_all_tables
002495 execsql {PRAGMA foreign_keys = ON}
002496 catchsql $zCreateTbl
002497 } $lRes
002498 }
002499
002500 #-------------------------------------------------------------------------
002501 # EVIDENCE-OF: R-47952-62498 It is not possible to use the "ALTER TABLE
002502 # ... ADD COLUMN" syntax to add a column that includes a REFERENCES
002503 # clause, unless the default value of the new column is NULL. Attempting
002504 # to do so returns an error.
002505 #
002506 proc test_efkey_6 {tn zAlter isError} {
002507 drop_all_tables
002508
002509 do_test e_fkey-56.$tn.1 "
002510 execsql { CREATE TABLE tbl(a, b); INSERT INTO tbl VALUES(1, 2); }
002511 [list catchsql $zAlter]
002512 " [lindex {{0 {}} {1 {Cannot add a REFERENCES column with non-NULL default value}}} $isError]
002513
002514 }
002515
002516 ifcapable altertable {
002517 test_efkey_6 1 "ALTER TABLE tbl ADD COLUMN c REFERENCES xx" 0
002518 test_efkey_6 2 "ALTER TABLE tbl ADD COLUMN c DEFAULT NULL REFERENCES xx" 0
002519 test_efkey_6 3 "ALTER TABLE tbl ADD COLUMN c DEFAULT 0 REFERENCES xx" 1
002520 }
002521
002522 #-------------------------------------------------------------------------
002523 # Test that ALTER TABLE adjusts REFERENCES clauses when the parent table
002524 # is RENAMED.
002525 #
002526 # EVIDENCE-OF: R-47080-02069 If an "ALTER TABLE ... RENAME TO" command
002527 # is used to rename a table that is the parent table of one or more
002528 # foreign key constraints, the definitions of the foreign key
002529 # constraints are modified to refer to the parent table by its new name
002530 #
002531 # Test that these adjustments are visible in the sqlite_master table.
002532 #
002533 # EVIDENCE-OF: R-43040-62530 The text of the child CREATE TABLE
002534 # statement or statements stored in the sqlite_schema table are modified
002535 # to reflect the new parent table name.
002536 #
002537 ifcapable altertable {
002538 do_test e_fkey-56.1 {
002539 drop_all_tables
002540 execsql {
002541 CREATE TABLE 'p 1 "parent one"'(a REFERENCES 'p 1 "parent one"', b, PRIMARY KEY(b));
002542
002543 CREATE TABLE c1(c, d REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE);
002544 CREATE TABLE c2(e, f, FOREIGN KEY(f) REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE);
002545 CREATE TABLE c3(e, 'f col 2', FOREIGN KEY('f col 2') REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE);
002546
002547 INSERT INTO 'p 1 "parent one"' VALUES(1, 1);
002548 INSERT INTO c1 VALUES(1, 1);
002549 INSERT INTO c2 VALUES(1, 1);
002550 INSERT INTO c3 VALUES(1, 1);
002551
002552 -- CREATE TABLE q(a, b, PRIMARY KEY(b));
002553 }
002554 } {}
002555 do_test e_fkey-56.2 {
002556 execsql { ALTER TABLE 'p 1 "parent one"' RENAME TO p }
002557 } {}
002558 do_test e_fkey-56.3 {
002559 execsql {
002560 UPDATE p SET a = 'xxx', b = 'xxx';
002561 SELECT * FROM p;
002562 SELECT * FROM c1;
002563 SELECT * FROM c2;
002564 SELECT * FROM c3;
002565 }
002566 } {xxx xxx 1 xxx 1 xxx 1 xxx}
002567 do_test e_fkey-56.4 {
002568 execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
002569 } [list \
002570 {CREATE TABLE "p"(a REFERENCES "p", b, PRIMARY KEY(b))} \
002571 {CREATE TABLE c1(c, d REFERENCES "p" ON UPDATE CASCADE)} \
002572 {CREATE TABLE c2(e, f, FOREIGN KEY(f) REFERENCES "p" ON UPDATE CASCADE)} \
002573 {CREATE TABLE c3(e, 'f col 2', FOREIGN KEY('f col 2') REFERENCES "p" ON UPDATE CASCADE)} \
002574 ]
002575 }
002576
002577 #-------------------------------------------------------------------------
002578 # Check that a DROP TABLE does an implicit DELETE FROM. Which does not
002579 # cause any triggers to fire, but does fire foreign key actions.
002580 #
002581 # EVIDENCE-OF: R-14208-23986 If foreign key constraints are enabled when
002582 # it is prepared, the DROP TABLE command performs an implicit DELETE to
002583 # remove all rows from the table before dropping it.
002584 #
002585 # EVIDENCE-OF: R-11078-03945 The implicit DELETE does not cause any SQL
002586 # triggers to fire, but may invoke foreign key actions or constraint
002587 # violations.
002588 #
002589 do_test e_fkey-57.1 {
002590 drop_all_tables
002591 execsql {
002592 CREATE TABLE p(a, b, PRIMARY KEY(a, b));
002593
002594 CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE SET NULL);
002595 CREATE TABLE c2(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE SET DEFAULT);
002596 CREATE TABLE c3(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE CASCADE);
002597 CREATE TABLE c4(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE RESTRICT);
002598 CREATE TABLE c5(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE NO ACTION);
002599
002600 CREATE TABLE c6(c, d,
002601 FOREIGN KEY(c, d) REFERENCES p ON DELETE RESTRICT
002602 DEFERRABLE INITIALLY DEFERRED
002603 );
002604 CREATE TABLE c7(c, d,
002605 FOREIGN KEY(c, d) REFERENCES p ON DELETE NO ACTION
002606 DEFERRABLE INITIALLY DEFERRED
002607 );
002608
002609 CREATE TABLE log(msg);
002610 CREATE TRIGGER tt AFTER DELETE ON p BEGIN
002611 INSERT INTO log VALUES('delete ' || old.rowid);
002612 END;
002613 }
002614 } {}
002615
002616 do_test e_fkey-57.2 {
002617 execsql {
002618 INSERT INTO p VALUES('a', 'b');
002619 INSERT INTO c1 VALUES('a', 'b');
002620 INSERT INTO c2 VALUES('a', 'b');
002621 INSERT INTO c3 VALUES('a', 'b');
002622 BEGIN;
002623 DROP TABLE p;
002624 SELECT * FROM c1;
002625 }
002626 } {{} {}}
002627 do_test e_fkey-57.3 {
002628 execsql { SELECT * FROM c2 }
002629 } {{} {}}
002630 do_test e_fkey-57.4 {
002631 execsql { SELECT * FROM c3 }
002632 } {}
002633 do_test e_fkey-57.5 {
002634 execsql { SELECT * FROM log }
002635 } {}
002636 do_test e_fkey-57.6 {
002637 execsql ROLLBACK
002638 } {}
002639 do_test e_fkey-57.7 {
002640 execsql {
002641 BEGIN;
002642 DELETE FROM p;
002643 SELECT * FROM log;
002644 ROLLBACK;
002645 }
002646 } {{delete 1}}
002647
002648 #-------------------------------------------------------------------------
002649 # If an IMMEDIATE foreign key fails as a result of a DROP TABLE, the
002650 # DROP TABLE command fails.
002651 #
002652 # EVIDENCE-OF: R-32768-47925 If an immediate foreign key constraint is
002653 # violated, the DROP TABLE statement fails and the table is not dropped.
002654 #
002655 do_test e_fkey-58.1 {
002656 execsql {
002657 DELETE FROM c1;
002658 DELETE FROM c2;
002659 DELETE FROM c3;
002660 }
002661 execsql { INSERT INTO c5 VALUES('a', 'b') }
002662 catchsql { DROP TABLE p }
002663 } {1 {FOREIGN KEY constraint failed}}
002664 do_test e_fkey-58.2 {
002665 execsql { SELECT * FROM p }
002666 } {a b}
002667 do_test e_fkey-58.3 {
002668 catchsql {
002669 BEGIN;
002670 DROP TABLE p;
002671 }
002672 } {1 {FOREIGN KEY constraint failed}}
002673 do_test e_fkey-58.4 {
002674 execsql {
002675 SELECT * FROM p;
002676 SELECT * FROM c5;
002677 ROLLBACK;
002678 }
002679 } {a b a b}
002680
002681 #-------------------------------------------------------------------------
002682 # If a DEFERRED foreign key fails as a result of a DROP TABLE, attempting
002683 # to commit the transaction fails unless the violation is fixed.
002684 #
002685 # EVIDENCE-OF: R-05903-08460 If a deferred foreign key constraint is
002686 # violated, then an error is reported when the user attempts to commit
002687 # the transaction if the foreign key constraint violations still exist
002688 # at that point.
002689 #
002690 do_test e_fkey-59.1 {
002691 execsql {
002692 DELETE FROM c1 ; DELETE FROM c2 ; DELETE FROM c3 ;
002693 DELETE FROM c4 ; DELETE FROM c5 ; DELETE FROM c6 ;
002694 DELETE FROM c7
002695 }
002696 } {}
002697 do_test e_fkey-59.2 {
002698 execsql { INSERT INTO c7 VALUES('a', 'b') }
002699 execsql {
002700 BEGIN;
002701 DROP TABLE p;
002702 }
002703 } {}
002704 do_test e_fkey-59.3 {
002705 catchsql COMMIT
002706 } {1 {FOREIGN KEY constraint failed}}
002707 do_test e_fkey-59.4 {
002708 execsql { CREATE TABLE p(a, b, PRIMARY KEY(a, b)) }
002709 catchsql COMMIT
002710 } {1 {FOREIGN KEY constraint failed}}
002711 do_test e_fkey-59.5 {
002712 execsql { INSERT INTO p VALUES('a', 'b') }
002713 execsql COMMIT
002714 } {}
002715
002716 #-------------------------------------------------------------------------
002717 # Any "foreign key mismatch" errors encountered while running an implicit
002718 # "DELETE FROM tbl" are ignored.
002719 #
002720 # EVIDENCE-OF: R-57242-37005 Any "foreign key mismatch" errors
002721 # encountered as part of an implicit DELETE are ignored.
002722 #
002723 drop_all_tables
002724 do_test e_fkey-60.1 {
002725 execsql {
002726 PRAGMA foreign_keys = OFF;
002727
002728 CREATE TABLE p(a PRIMARY KEY, b REFERENCES nosuchtable);
002729 CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES a);
002730 CREATE TABLE c2(c REFERENCES p(b), d);
002731 CREATE TABLE c3(c REFERENCES p ON DELETE SET NULL, d);
002732
002733 INSERT INTO p VALUES(1, 2);
002734 INSERT INTO c1 VALUES(1, 2);
002735 INSERT INTO c2 VALUES(1, 2);
002736 INSERT INTO c3 VALUES(1, 2);
002737 }
002738 } {}
002739 do_test e_fkey-60.2 {
002740 execsql { PRAGMA foreign_keys = ON }
002741 catchsql { DELETE FROM p }
002742 } {1 {no such table: main.nosuchtable}}
002743 do_test e_fkey-60.3 {
002744 execsql {
002745 BEGIN;
002746 DROP TABLE p;
002747 SELECT * FROM c3;
002748 ROLLBACK;
002749 }
002750 } {{} 2}
002751 do_test e_fkey-60.4 {
002752 execsql { CREATE TABLE nosuchtable(x PRIMARY KEY) }
002753 catchsql { DELETE FROM p }
002754 } {1 {foreign key mismatch - "c2" referencing "p"}}
002755 do_test e_fkey-60.5 {
002756 execsql { DROP TABLE c1 }
002757 catchsql { DELETE FROM p }
002758 } {1 {foreign key mismatch - "c2" referencing "p"}}
002759 do_test e_fkey-60.6 {
002760 execsql { DROP TABLE c2 }
002761 execsql { DELETE FROM p }
002762 } {}
002763
002764 #-------------------------------------------------------------------------
002765 # Test that the special behaviors of ALTER and DROP TABLE are only
002766 # activated when foreign keys are enabled. Special behaviors are:
002767 #
002768 # 1. ADD COLUMN not allowing a REFERENCES clause with a non-NULL
002769 # default value.
002770 # 2. Modifying foreign key definitions when a parent table is RENAMEd.
002771 # 3. Running an implicit DELETE FROM command as part of DROP TABLE.
002772 #
002773 # EVIDENCE-OF: R-54142-41346 The properties of the DROP TABLE and ALTER
002774 # TABLE commands described above only apply if foreign keys are enabled.
002775 #
002776 ifcapable altertable {
002777 do_test e_fkey-61.1.1 {
002778 drop_all_tables
002779 execsql { CREATE TABLE t1(a, b) ; INSERT INTO t1 VALUES(1, 2) }
002780 catchsql { ALTER TABLE t1 ADD COLUMN c DEFAULT 'xxx' REFERENCES t2 }
002781 } {1 {Cannot add a REFERENCES column with non-NULL default value}}
002782 do_test e_fkey-61.1.2 {
002783 execsql { PRAGMA foreign_keys = OFF }
002784 execsql { ALTER TABLE t1 ADD COLUMN c DEFAULT 'xxx' REFERENCES t2 }
002785 execsql { SELECT sql FROM sqlite_master WHERE name = 't1' }
002786 } {{CREATE TABLE t1(a, b, c DEFAULT 'xxx' REFERENCES t2)}}
002787 do_test e_fkey-61.1.3 {
002788 execsql { PRAGMA foreign_keys = ON }
002789 } {}
002790
002791 do_test e_fkey-61.2.1 {
002792 drop_all_tables
002793 execsql {
002794 CREATE TABLE p(a UNIQUE);
002795 CREATE TABLE c(b REFERENCES p(a));
002796 BEGIN;
002797 ALTER TABLE p RENAME TO parent;
002798 SELECT sql FROM sqlite_master WHERE name = 'c';
002799 ROLLBACK;
002800 }
002801 } {{CREATE TABLE c(b REFERENCES "parent"(a))}}
002802 do_test e_fkey-61.2.2 {
002803 execsql {
002804 PRAGMA foreign_keys = OFF;
002805 PRAGMA legacy_alter_table = ON;
002806 ALTER TABLE p RENAME TO parent;
002807 SELECT sql FROM sqlite_master WHERE name = 'c';
002808 }
002809 } {{CREATE TABLE c(b REFERENCES p(a))}}
002810 do_test e_fkey-61.2.3 {
002811 execsql { PRAGMA foreign_keys = ON }
002812 execsql { PRAGMA legacy_alter_table = OFF }
002813 } {}
002814
002815 do_test e_fkey-61.3.1 {
002816 drop_all_tables
002817 execsql {
002818 CREATE TABLE p(a UNIQUE);
002819 CREATE TABLE c(b REFERENCES p(a) ON DELETE SET NULL);
002820 INSERT INTO p VALUES('x');
002821 INSERT INTO c VALUES('x');
002822 BEGIN;
002823 DROP TABLE p;
002824 SELECT * FROM c;
002825 ROLLBACK;
002826 }
002827 } {{}}
002828 do_test e_fkey-61.3.2 {
002829 execsql {
002830 PRAGMA foreign_keys = OFF;
002831 DROP TABLE p;
002832 SELECT * FROM c;
002833 }
002834 } {x}
002835 do_test e_fkey-61.3.3 {
002836 execsql { PRAGMA foreign_keys = ON }
002837 } {}
002838 }
002839
002840 ###########################################################################
002841 ### SECTION 6: Limits and Unsupported Features
002842 ###########################################################################
002843
002844 #-------------------------------------------------------------------------
002845 # Test that MATCH clauses are parsed, but SQLite treats every foreign key
002846 # constraint as if it were "MATCH SIMPLE".
002847 #
002848 # EVIDENCE-OF: R-24728-13230 SQLite parses MATCH clauses (i.e. does not
002849 # report a syntax error if you specify one), but does not enforce them.
002850 #
002851 # EVIDENCE-OF: R-24450-46174 All foreign key constraints in SQLite are
002852 # handled as if MATCH SIMPLE were specified.
002853 #
002854 foreach zMatch [list SIMPLE PARTIAL FULL Simple parTIAL FuLL ] {
002855 drop_all_tables
002856 do_test e_fkey-62.$zMatch.1 {
002857 execsql "
002858 CREATE TABLE p(a, b, c, PRIMARY KEY(b, c));
002859 CREATE TABLE c(d, e, f, FOREIGN KEY(e, f) REFERENCES p MATCH $zMatch);
002860 "
002861 } {}
002862 do_test e_fkey-62.$zMatch.2 {
002863 execsql { INSERT INTO p VALUES(1, 2, 3) }
002864
002865 # MATCH SIMPLE behavior: Allow any child key that contains one or more
002866 # NULL value to be inserted. Non-NULL values do not have to map to any
002867 # parent key values, so long as at least one field of the child key is
002868 # NULL.
002869 execsql { INSERT INTO c VALUES('w', 2, 3) }
002870 execsql { INSERT INTO c VALUES('x', 'x', NULL) }
002871 execsql { INSERT INTO c VALUES('y', NULL, 'x') }
002872 execsql { INSERT INTO c VALUES('z', NULL, NULL) }
002873
002874 # Check that the FK is enforced properly if there are no NULL values
002875 # in the child key columns.
002876 catchsql { INSERT INTO c VALUES('a', 2, 4) }
002877 } {1 {FOREIGN KEY constraint failed}}
002878 }
002879
002880 #-------------------------------------------------------------------------
002881 # Test that SQLite does not support the SET CONSTRAINT statement. And
002882 # that it is possible to create both immediate and deferred constraints.
002883 #
002884 # EVIDENCE-OF: R-21599-16038 In SQLite, a foreign key constraint is
002885 # permanently marked as deferred or immediate when it is created.
002886 #
002887 drop_all_tables
002888 do_test e_fkey-62.1 {
002889 catchsql { SET CONSTRAINTS ALL IMMEDIATE }
002890 } {1 {near "SET": syntax error}}
002891 do_test e_fkey-62.2 {
002892 catchsql { SET CONSTRAINTS ALL DEFERRED }
002893 } {1 {near "SET": syntax error}}
002894
002895 do_test e_fkey-62.3 {
002896 execsql {
002897 CREATE TABLE p(a, b, PRIMARY KEY(a, b));
002898 CREATE TABLE cd(c, d,
002899 FOREIGN KEY(c, d) REFERENCES p DEFERRABLE INITIALLY DEFERRED);
002900 CREATE TABLE ci(c, d,
002901 FOREIGN KEY(c, d) REFERENCES p DEFERRABLE INITIALLY IMMEDIATE);
002902 BEGIN;
002903 }
002904 } {}
002905 do_test e_fkey-62.4 {
002906 catchsql { INSERT INTO ci VALUES('x', 'y') }
002907 } {1 {FOREIGN KEY constraint failed}}
002908 do_test e_fkey-62.5 {
002909 catchsql { INSERT INTO cd VALUES('x', 'y') }
002910 } {0 {}}
002911 do_test e_fkey-62.6 {
002912 catchsql { COMMIT }
002913 } {1 {FOREIGN KEY constraint failed}}
002914 do_test e_fkey-62.7 {
002915 execsql {
002916 DELETE FROM cd;
002917 COMMIT;
002918 }
002919 } {}
002920
002921 #-------------------------------------------------------------------------
002922 # Test that the maximum recursion depth of foreign key action programs is
002923 # governed by the SQLITE_MAX_TRIGGER_DEPTH and SQLITE_LIMIT_TRIGGER_DEPTH
002924 # settings.
002925 #
002926 # EVIDENCE-OF: R-42264-30503 The SQLITE_MAX_TRIGGER_DEPTH and
002927 # SQLITE_LIMIT_TRIGGER_DEPTH settings determine the maximum allowable
002928 # depth of trigger program recursion. For the purposes of these limits,
002929 # foreign key actions are considered trigger programs.
002930 #
002931 proc test_on_delete_recursion {limit} {
002932 drop_all_tables
002933 execsql {
002934 BEGIN;
002935 CREATE TABLE t0(a PRIMARY KEY, b);
002936 INSERT INTO t0 VALUES('x0', NULL);
002937 }
002938 for {set i 1} {$i <= $limit} {incr i} {
002939 execsql "
002940 CREATE TABLE t$i (
002941 a PRIMARY KEY, b REFERENCES t[expr $i-1] ON DELETE CASCADE
002942 );
002943 INSERT INTO t$i VALUES('x$i', 'x[expr $i-1]');
002944 "
002945 }
002946 execsql COMMIT
002947 catchsql "
002948 DELETE FROM t0;
002949 SELECT count(*) FROM t$limit;
002950 "
002951 }
002952 proc test_on_update_recursion {limit} {
002953 drop_all_tables
002954 execsql {
002955 BEGIN;
002956 CREATE TABLE t0(a PRIMARY KEY);
002957 INSERT INTO t0 VALUES('xxx');
002958 }
002959 for {set i 1} {$i <= $limit} {incr i} {
002960 set j [expr $i-1]
002961
002962 execsql "
002963 CREATE TABLE t$i (a PRIMARY KEY REFERENCES t$j ON UPDATE CASCADE);
002964 INSERT INTO t$i VALUES('xxx');
002965 "
002966 }
002967 execsql COMMIT
002968 catchsql "
002969 UPDATE t0 SET a = 'yyy';
002970 SELECT NOT (a='yyy') FROM t$limit;
002971 "
002972 }
002973
002974 # If the current build was created using clang with the -fsanitize=address
002975 # switch, then the library uses considerably more stack space than usual.
002976 # So much more, that some of the following tests cause stack overflows
002977 # if they are run under this configuration.
002978 #
002979 if {[clang_sanitize_address]==0} {
002980 do_test e_fkey-63.1.1 {
002981 test_on_delete_recursion $SQLITE_MAX_TRIGGER_DEPTH
002982 } {0 0}
002983 do_test e_fkey-63.1.2 {
002984 test_on_delete_recursion [expr $SQLITE_MAX_TRIGGER_DEPTH+1]
002985 } {1 {too many levels of trigger recursion}}
002986 do_test e_fkey-63.1.3 {
002987 sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 5
002988 test_on_delete_recursion 5
002989 } {0 0}
002990 do_test e_fkey-63.1.4 {
002991 test_on_delete_recursion 6
002992 } {1 {too many levels of trigger recursion}}
002993 do_test e_fkey-63.1.5 {
002994 sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000
002995 } {5}
002996 do_test e_fkey-63.2.1 {
002997 test_on_update_recursion $SQLITE_MAX_TRIGGER_DEPTH
002998 } {0 0}
002999 do_test e_fkey-63.2.2 {
003000 test_on_update_recursion [expr $SQLITE_MAX_TRIGGER_DEPTH+1]
003001 } {1 {too many levels of trigger recursion}}
003002 do_test e_fkey-63.2.3 {
003003 sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 5
003004 test_on_update_recursion 5
003005 } {0 0}
003006 do_test e_fkey-63.2.4 {
003007 test_on_update_recursion 6
003008 } {1 {too many levels of trigger recursion}}
003009 do_test e_fkey-63.2.5 {
003010 sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000
003011 } {5}
003012 }
003013
003014 #-------------------------------------------------------------------------
003015 # The setting of the recursive_triggers pragma does not affect foreign
003016 # key actions.
003017 #
003018 # EVIDENCE-OF: R-44355-00270 The PRAGMA recursive_triggers setting does
003019 # not affect the operation of foreign key actions.
003020 #
003021 foreach recursive_triggers_setting [list 0 1 ON OFF] {
003022 drop_all_tables
003023 execsql "PRAGMA recursive_triggers = $recursive_triggers_setting"
003024
003025 do_test e_fkey-64.$recursive_triggers_setting.1 {
003026 execsql {
003027 CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1 ON DELETE CASCADE);
003028 INSERT INTO t1 VALUES(1, NULL);
003029 INSERT INTO t1 VALUES(2, 1);
003030 INSERT INTO t1 VALUES(3, 2);
003031 INSERT INTO t1 VALUES(4, 3);
003032 INSERT INTO t1 VALUES(5, 4);
003033 SELECT count(*) FROM t1;
003034 }
003035 } {5}
003036 do_test e_fkey-64.$recursive_triggers_setting.2 {
003037 execsql { SELECT count(*) FROM t1 WHERE a = 1 }
003038 } {1}
003039 do_test e_fkey-64.$recursive_triggers_setting.3 {
003040 execsql {
003041 DELETE FROM t1 WHERE a = 1;
003042 SELECT count(*) FROM t1;
003043 }
003044 } {0}
003045 }
003046
003047 finish_test