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