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