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