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