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