Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add a test case for creating an FTS3 table with no module arguments or opening/closing brackets in the CREATE VIRTUAL TABLE statement. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
a9cba7ea0a06efa7a63a3069b219cc30 |
User & Date: | dan 2009-11-28 15:35:17.000 |
Context
2009-11-28
| ||
17:07 | Remove all benign OOM failure opportunities from the FTS3 hash table implementation. All OOM faults cause SQLITE_NOMEM to be returned. (check-in: 80754d383a user: drh tags: trunk) | |
15:35 | Add a test case for creating an FTS3 table with no module arguments or opening/closing brackets in the CREATE VIRTUAL TABLE statement. (check-in: a9cba7ea0a user: dan tags: trunk) | |
13:46 | Initialize a variable (unnecessarily) to avoid a compiler warning. (check-in: db65fd5913 user: drh tags: trunk) | |
Changes
Changes to ext/fts3/fts3.c.
︙ | ︙ | |||
653 654 655 656 657 658 659 | int iCol; int nString = 0; int nCol = 0; char *zCsr; int nDb; int nName; | | | | 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 | int iCol; int nString = 0; int nCol = 0; char *zCsr; int nDb; int nName; const char *zTokenizer = 0; /* Name of tokenizer to use */ sqlite3_tokenizer *pTokenizer = 0; /* Tokenizer for this table */ nDb = strlen(argv[1]) + 1; nName = strlen(argv[2]) + 1; for(i=3; i<argc; i++){ char const *z = argv[i]; rc = sqlite3Fts3InitTokenizer(pHash, z, &pTokenizer, &zTokenizer, pzErr); if( rc!=SQLITE_OK ){ |
︙ | ︙ | |||
745 746 747 748 749 750 751 752 | rc = fts3DeclareVtab(p); if( rc!=SQLITE_OK ) goto fts3_init_out; *ppVTab = &p->base; fts3_init_out: if( rc!=SQLITE_OK ){ | > > | > | > | 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 | rc = fts3DeclareVtab(p); if( rc!=SQLITE_OK ) goto fts3_init_out; *ppVTab = &p->base; fts3_init_out: assert( p || (pTokenizer && rc!=SQLITE_OK) ); if( rc!=SQLITE_OK ){ if( p ){ fts3DisconnectMethod((sqlite3_vtab *)p); }else{ pTokenizer->pModule->xDestroy(pTokenizer); } } return rc; } /* ** The xConnect() and xCreate() methods for the virtual table. All the ** work is done in function fts3InitVtab(). |
︙ | ︙ |
Changes to test/e_fkey.test.
︙ | ︙ | |||
59 60 61 62 63 64 65 | # /* EV: R-41784-13339 */ # # Specifically, test that "PRAGMA foreign_keys" is a no-op in this case. # When using the pragma to query the current setting, 0 rows are returned. # reset_db ifcapable !trigger&&foreignkey { | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 | # /* EV: R-41784-13339 */ # # Specifically, test that "PRAGMA foreign_keys" is a no-op in this case. # When using the pragma to query the current setting, 0 rows are returned. # reset_db ifcapable !trigger&&foreignkey { do_test e_fkey-2.1 { execsql { PRAGMA foreign_keys = ON; CREATE TABLE p(i PRIMARY KEY); CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE); INSERT INTO p VALUES('hello'); INSERT INTO c VALUES('hello'); UPDATE p SET i = 'world'; SELECT * FROM c; } } {hello} do_test e_fkey-2.2 { execsql { PRAGMA foreign_key_list(c) } } {0 0 p j {} CASCADE {NO ACTION} NONE} do_test e_fkey-2.3 { execsql { PRAGMA foreign_keys } } {} } #------------------------------------------------------------------------- # /* EV: R-58428-36660 */ # # Test the effects of defining OMIT_FOREIGN_KEY. # # /* EV: R-58428-36660 */ # # Specifically, test that foreign key constraints cannot even be parsed # in such a build. # reset_db ifcapable !foreignkey { do_test e_fkey-3.1 { execsql { CREATE TABLE p(i PRIMARY KEY) } catchsql { CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE) } } {1 {near "ON": syntax error}} do_test e_fkey-3.2 { # This is allowed, as in this build, "REFERENCES" is not a keyword. # The declared datatype of column j is "REFERENCES p". execsql { CREATE TABLE c(j REFERENCES p) } } {} do_test e_fkey-3.3 { execsql { PRAGMA table_info(c) } } {0 j {REFERENCES p} 0 {} 0} do_test e_fkey-3.4 { execsql { PRAGMA foreign_key_list(c) } } {} do_test e_fkey-3.5 { execsql { PRAGMA foreign_keys } } {} } ifcapable !foreignkey||!trigger { finish_test ; return } reset_db #------------------------------------------------------------------------- # /* EV: R-07280-60510 */ # # Test that even if foreign keys are supported by the build, they must # be enabled using "PRAGMA foreign_keys = ON" (or similar). # # /* EV: R-59578-04990 */ # # This also tests that foreign key constraints are disabled by default. # drop_all_tables do_test e_fkey-4.1 { execsql { CREATE TABLE p(i PRIMARY KEY); CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE); INSERT INTO p VALUES('hello'); INSERT INTO c VALUES('hello'); UPDATE p SET i = 'world'; SELECT * FROM c; } } {hello} do_test e_fkey-4.2 { execsql { DELETE FROM c; DELETE FROM p; PRAGMA foreign_keys = ON; INSERT INTO p VALUES('hello'); INSERT INTO c VALUES('hello'); UPDATE p SET i = 'world'; SELECT * FROM c; } } {world} #------------------------------------------------------------------------- # /* EV: R-15278-54456 */ # /* EV: R-11255-19907 */ # # Test that the application can use "PRAGMA foreign_keys" to query for # whether or not foreign keys are currently enabled. This also tests # the example code in section 2 of foreignkeys.in. # reset_db do_test e_fkey-5.1 { execsql { PRAGMA foreign_keys } } {0} do_test e_fkey-5.2 { execsql { PRAGMA foreign_keys = ON; PRAGMA foreign_keys; } } {1} do_test e_fkey-5.3 { execsql { PRAGMA foreign_keys = OFF; PRAGMA foreign_keys; } } {0} #------------------------------------------------------------------------- # /* EV: R-46649-58537 */ # # Test that it is not possible to enable or disable foreign key support # while not in auto-commit mode. # reset_db do_test e_fkey-6.1 { execsql { PRAGMA foreign_keys = ON; CREATE TABLE t1(a UNIQUE, b); CREATE TABLE t2(c, d REFERENCES t1(a)); INSERT INTO t1 VALUES(1, 2); INSERT INTO t2 VALUES(2, 1); BEGIN; PRAGMA foreign_keys = OFF; } catchsql { DELETE FROM t1 } } {1 {foreign key constraint failed}} do_test e_fkey-6.2 { execsql { PRAGMA foreign_keys } } {1} do_test e_fkey-6.3 { execsql { COMMIT; PRAGMA foreign_keys = OFF; BEGIN; PRAGMA foreign_keys = ON; DELETE FROM t1; PRAGMA foreign_keys; } } {0} do_test e_fkey-6.4 { execsql COMMIT } {} ########################################################################### ### SECTION 1: Introduction to Foreign Key Constraints ########################################################################### execsql "PRAGMA foreign_keys = ON" #------------------------------------------------------------------------- # /* EV: R-04042-24825 */ # # Verify that the syntax in the first example in section 1 is valid. # do_test e_fkey-7.1 { execsql { CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES artist(artistid) ); } } {} #------------------------------------------------------------------------- # /* EV: R-61362-32087 */ # # Attempting to insert a row into the 'track' table that corresponds # to no row in the 'artist' table fails. # do_test e_fkey-8.1 { catchsql { INSERT INTO track VALUES(1, 'track 1', 1) } } {1 {foreign key constraint failed}} do_test e_fkey-8.2 { execsql { INSERT INTO artist VALUES(2, 'artist 1') } catchsql { INSERT INTO track VALUES(1, 'track 1', 1) } } {1 {foreign key constraint failed}} do_test e_fkey-8.2 { execsql { INSERT INTO track VALUES(1, 'track 1', 2) } } {} #------------------------------------------------------------------------- # /* EV: R-24401-52400 */ # # Attempting to delete a row from the 'artist' table while there are # dependent rows in the track table also fails. # do_test e_fkey-9.1 { catchsql { DELETE FROM artist WHERE artistid = 2 } } {1 {foreign key constraint failed}} do_test e_fkey-9.2 { execsql { DELETE FROM track WHERE trackartist = 2; DELETE FROM artist WHERE artistid = 2; } } {} #------------------------------------------------------------------------- # /* EV: R-23980-48859 */ # # If the foreign key column (trackartist) in table 'track' is set to NULL, # there is no requirement for a matching row in the 'artist' table. # do_test e_fkey-10.1 { execsql { INSERT INTO track VALUES(1, 'track 1', NULL); INSERT INTO track VALUES(2, 'track 2', NULL); } } {} do_test e_fkey-10.2 { execsql { SELECT * FROM artist } } {} do_test e_fkey-10.3 { # Setting the trackid to a non-NULL value fails, of course. catchsql { UPDATE track SET trackartist = 5 WHERE trackid = 1 } } {1 {foreign key constraint failed}} do_test e_fkey-10.4 { execsql { INSERT INTO artist VALUES(5, 'artist 5'); UPDATE track SET trackartist = 5 WHERE trackid = 1; } catchsql { DELETE FROM artist WHERE artistid = 5} } {1 {foreign key constraint failed}} do_test e_fkey-10.5 { execsql { UPDATE track SET trackartist = NULL WHERE trackid = 1; DELETE FROM artist WHERE artistid = 5; } } {} #------------------------------------------------------------------------- |
︙ | ︙ | |||
325 326 327 328 329 330 331 | {1 {PRIMARY KEY must be unique}} {1 {foreign key constraint failed}} } if {[lsearch $results $res]<0} { error $res } | | | 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 | {1 {PRIMARY KEY must be unique}} {1 {foreign key constraint failed}} } if {[lsearch $results $res]<0} { error $res } do_test e_fkey-11.$tn { execsql { SELECT count(*) FROM track WHERE NOT ( trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist) ) } } {0} |
︙ | ︙ | |||
364 365 366 367 368 369 370 | #------------------------------------------------------------------------- # /* EV: R-42412-59321 */ # # Check that a NOT NULL constraint can be added to the example schema # to prohibit NULL child keys from being inserted. # drop_all_tables | | | | | | | | | | | | | 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 | #------------------------------------------------------------------------- # /* EV: R-42412-59321 */ # # Check that a NOT NULL constraint can be added to the example schema # to prohibit NULL child keys from being inserted. # drop_all_tables do_test e_fkey-12.1 { execsql { CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER NOT NULL, FOREIGN KEY(trackartist) REFERENCES artist(artistid) ); } } {} do_test e_fkey-12.2 { catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) } } {1 {track.trackartist may not be NULL}} #------------------------------------------------------------------------- # /* EV: R-17902-59250 */ # # Test an example from foreignkeys.html. # drop_all_tables do_test e_fkey-13.1 { execsql { CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES artist(artistid) ); INSERT INTO artist VALUES(1, 'Dean Martin'); INSERT INTO artist VALUES(2, 'Frank Sinatra'); INSERT INTO track VALUES(11, 'That''s Amore', 1); INSERT INTO track VALUES(12, 'Christmas Blues', 1); INSERT INTO track VALUES(13, 'My Way', 2); } } {} do_test e_fkey-13.2 { catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', 3) } } {1 {foreign key constraint failed}} do_test e_fkey-13.3 { execsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) } } {} do_test e_fkey-13.4 { catchsql { UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; } } {1 {foreign key constraint failed}} do_test e_fkey-13.5 { execsql { INSERT INTO artist VALUES(3, 'Sammy Davis Jr.'); UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; INSERT INTO track VALUES(15, 'Boogie Woogie', 3); } } {} #------------------------------------------------------------------------- # /* EV: R-15034-64331 */ # # Test the second example from the first section of foreignkeys.html. # do_test e_fkey-14.1 { catchsql { DELETE FROM artist WHERE artistname = 'Frank Sinatra'; } } {1 {foreign key constraint failed}} do_test e_fkey-14.2 { execsql { DELETE FROM track WHERE trackname = 'My Way'; DELETE FROM artist WHERE artistname = 'Frank Sinatra'; } } {} do_test e_fkey-14.3 { catchsql { UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; } } {1 {foreign key constraint failed}} do_test e_fkey-14.4 { execsql { DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues'); UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; } } {} |
︙ | ︙ | |||
469 470 471 472 473 474 475 | # # /* EV: R-57765-12380 */ # # Test also that the comparison rules are used when testing if there # is a matching row in the parent table of a foreign key constraint. # drop_all_tables | | | | | 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 | # # /* EV: R-57765-12380 */ # # Test also that the comparison rules are used when testing if there # is a matching row in the parent table of a foreign key constraint. # drop_all_tables do_test e_fkey-15.1 { execsql { CREATE TABLE par(p PRIMARY KEY); CREATE TABLE chi(c REFERENCES par); INSERT INTO par VALUES(1); INSERT INTO par VALUES('1'); INSERT INTO par VALUES(X'31'); SELECT typeof(p) FROM par; } } {integer text blob} proc test_efkey_45 {tn isError sql} { do_test e_fkey-15.$tn.1 " catchsql {$sql} " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError] do_test e_fkey-15.$tn.2 { execsql { SELECT * FROM chi WHERE c IS NOT NULL AND c NOT IN (SELECT p FROM par) } } {} } test_efkey_45 1 0 "INSERT INTO chi VALUES(1)" |
︙ | ︙ | |||
510 511 512 513 514 515 516 | #------------------------------------------------------------------------- # /* EV: R-15796-47513 */ # # Specifically, test that when comparing child and parent key values the # default collation sequence of the parent key column is used. # drop_all_tables | | | | | | | | | | 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 | #------------------------------------------------------------------------- # /* EV: R-15796-47513 */ # # Specifically, test that when comparing child and parent key values the # default collation sequence of the parent key column is used. # drop_all_tables do_test e_fkey-16.1 { execsql { CREATE TABLE t1(a COLLATE nocase PRIMARY KEY); CREATE TABLE t2(b REFERENCES t1); } } {} do_test e_fkey-16.2 { execsql { INSERT INTO t1 VALUES('oNe'); INSERT INTO t2 VALUES('one'); INSERT INTO t2 VALUES('ONE'); UPDATE t2 SET b = 'OnE'; UPDATE t1 SET a = 'ONE'; } } {} do_test e_fkey-16.3 { catchsql { UPDATE t2 SET b = 'two' WHERE rowid = 1 } } {1 {foreign key constraint failed}} do_test e_fkey-16.4 { catchsql { DELETE FROM t1 WHERE rowid = 1 } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- # /* EV: R-04240-13860 */ # # Specifically, test that when comparing child and parent key values the # affinity of the parent key column is applied to the child key value # before the comparison takes place. # drop_all_tables do_test e_fkey-17.1 { execsql { CREATE TABLE t1(a NUMERIC PRIMARY KEY); CREATE TABLE t2(b TEXT REFERENCES t1); } } {} do_test e_fkey-17.2 { execsql { INSERT INTO t1 VALUES(1); INSERT INTO t1 VALUES(2); INSERT INTO t1 VALUES('three'); INSERT INTO t2 VALUES('2.0'); SELECT b, typeof(b) FROM t2; } } {2.0 text} do_test e_fkey-17.3 { execsql { SELECT typeof(a) FROM t1 } } {integer integer text} do_test e_fkey-17.4 { catchsql { DELETE FROM t1 WHERE rowid = 2 } } {1 {foreign key constraint failed}} ########################################################################### ### SECTION 3: Required and Suggested Database Indexes ########################################################################### |
︙ | ︙ | |||
580 581 582 583 584 585 586 | # # Also test that if a parent key is not subject to a PRIMARY KEY or UNIQUE # constraint, but does have a UNIQUE index created on it, then the UNIQUE index # must use the default collation sequences associated with the parent key # columns. # drop_all_tables | | | | 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 | # # Also test that if a parent key is not subject to a PRIMARY KEY or UNIQUE # constraint, but does have a UNIQUE index created on it, then the UNIQUE index # must use the default collation sequences associated with the parent key # columns. # drop_all_tables do_test e_fkey-18.1 { execsql { CREATE TABLE t2(a REFERENCES t1(x)); } } {} proc test_efkey_57 {tn isError sql} { catchsql { DROP TABLE t1 } execsql $sql do_test e_fkey-18.$tn { catchsql { INSERT INTO t2 VALUES(NULL) } } [lindex {{0 {}} {1 {foreign key mismatch}}} $isError] } test_efkey_57 2 0 { CREATE TABLE t1(x PRIMARY KEY) } test_efkey_57 3 0 { CREATE TABLE t1(x UNIQUE) } test_efkey_57 4 0 { CREATE TABLE t1(x); CREATE UNIQUE INDEX t1i ON t1(x) } test_efkey_57 5 1 { |
︙ | ︙ | |||
629 630 631 632 633 634 635 | # Problem with FK on child5. # # /* EV: R-63088-37469 */ # # Problem with FK on child6 and child7. # drop_all_tables | | | | | | | | | 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 | # Problem with FK on child5. # # /* EV: R-63088-37469 */ # # Problem with FK on child6 and child7. # drop_all_tables do_test e_fkey-19.1 { execsql { CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f); CREATE UNIQUE INDEX i1 ON parent(c, d); CREATE INDEX i2 ON parent(e); CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase); CREATE TABLE child1(f, g REFERENCES parent(a)); -- Ok CREATE TABLE child2(h, i REFERENCES parent(b)); -- Ok CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d)); -- Ok CREATE TABLE child4(l, m REFERENCES parent(e)); -- Err CREATE TABLE child5(n, o REFERENCES parent(f)); -- Err CREATE TABLE child6(p, q, FOREIGN KEY(p,q) REFERENCES parent(b, c)); -- Err CREATE TABLE child7(r REFERENCES parent(c)); -- Err } } {} do_test e_fkey-19.2 { execsql { INSERT INTO parent VALUES(1, 2, 3, 4, 5, 6); INSERT INTO child1 VALUES('xxx', 1); INSERT INTO child2 VALUES('xxx', 2); INSERT INTO child3 VALUES(3, 4); } } {} do_test e_fkey-19.2 { catchsql { INSERT INTO child4 VALUES('xxx', 5) } } {1 {foreign key mismatch}} do_test e_fkey-19.3 { catchsql { INSERT INTO child5 VALUES('xxx', 6) } } {1 {foreign key mismatch}} do_test e_fkey-19.4 { catchsql { INSERT INTO child6 VALUES(2, 3) } } {1 {foreign key mismatch}} do_test e_fkey-19.5 { catchsql { INSERT INTO child7 VALUES(3) } } {1 {foreign key mismatch}} #------------------------------------------------------------------------- # /* EV: R-45488-08504 */ # /* EV: R-48391-38472 */ # /* EV: R-03108-63659 */ # /* EV: R-60781-26576 */ # # Test errors in the database schema that are detected while preparing # DML statements. The error text for these messages always matches # either "foreign key mismatch" or "no such table*" (using [string match]). # do_test e_fkey-20.1 { execsql { CREATE TABLE c1(c REFERENCES nosuchtable, d); CREATE TABLE p2(a, b, UNIQUE(a, b)); CREATE TABLE c2(c, d, FOREIGN KEY(c, d) REFERENCES p2(a, x)); CREATE TABLE p3(a PRIMARY KEY, b); |
︙ | ︙ | |||
711 712 713 714 715 716 717 | 3 c2 p2 "foreign key mismatch" 4 c3 p3 "foreign key mismatch" 5 c4 p4 "foreign key mismatch" 6 c5 p5 "foreign key mismatch" 7 c6 p6 "foreign key mismatch" 8 c7 p7 "foreign key mismatch" } { | | | | | | | | | | | | | | | | 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 | 3 c2 p2 "foreign key mismatch" 4 c3 p3 "foreign key mismatch" 5 c4 p4 "foreign key mismatch" 6 c5 p5 "foreign key mismatch" 7 c6 p6 "foreign key mismatch" 8 c7 p7 "foreign key mismatch" } { do_test e_fkey-20.$tn.1 { catchsql "INSERT INTO $tbl VALUES('a', 'b')" } [list 1 $err] do_test e_fkey-20.$tn.2 { catchsql "UPDATE $tbl SET c = ?, d = ?" } [list 1 $err] do_test e_fkey-20.$tn.3 { catchsql "INSERT INTO $tbl SELECT ?, ?" } [list 1 $err] if {$ptbl ne ""} { do_test e_fkey-20.$tn.4 { catchsql "DELETE FROM $ptbl" } [list 1 $err] do_test e_fkey-20.$tn.5 { catchsql "UPDATE $ptbl SET a = ?, b = ?" } [list 1 $err] do_test e_fkey-20.$tn.6 { catchsql "INSERT INTO $ptbl SELECT ?, ?" } [list 1 $err] } } #------------------------------------------------------------------------- # /* EV: R-19353-43643 */ # # Test the example of foreign key mismatch errors caused by implicitly # mapping a child key to the primary key of the parent table when the # child key consists of a different number of columns to that primary key. # drop_all_tables do_test e_fkey-21.1 { execsql { CREATE TABLE parent2(a, b, PRIMARY KEY(a,b)); CREATE TABLE child8(x, y, FOREIGN KEY(x,y) REFERENCES parent2); -- Ok CREATE TABLE child9(x REFERENCES parent2); -- Err CREATE TABLE child10(x,y,z, FOREIGN KEY(x,y,z) REFERENCES parent2); -- Err } } {} do_test e_fkey-21.2 { execsql { INSERT INTO parent2 VALUES('I', 'II'); INSERT INTO child8 VALUES('I', 'II'); } } {} do_test e_fkey-21.3 { catchsql { INSERT INTO child9 VALUES('I') } } {1 {foreign key mismatch}} do_test e_fkey-21.4 { catchsql { INSERT INTO child9 VALUES('II') } } {1 {foreign key mismatch}} do_test e_fkey-21.5 { catchsql { INSERT INTO child9 VALUES(NULL) } } {1 {foreign key mismatch}} do_test e_fkey-21.6 { catchsql { INSERT INTO child10 VALUES('I', 'II', 'III') } } {1 {foreign key mismatch}} do_test e_fkey-21.7 { catchsql { INSERT INTO child10 VALUES(1, 2, 3) } } {1 {foreign key mismatch}} do_test e_fkey-21.8 { catchsql { INSERT INTO child10 VALUES(NULL, NULL, NULL) } } {1 {foreign key mismatch}} #------------------------------------------------------------------------- # /* EV: R-23682-59820 */ # # Test errors that are reported when creating the child table. |
︙ | ︙ | |||
803 804 805 806 807 808 809 | "CREATE TABLE child2(a, b, FOREIGN KEY(a, b) REFERENCES p(c, d, e))" {number of columns in foreign key does not match the number of columns in the referenced table} "CREATE TABLE child2(a, b, FOREIGN KEY(a, c) REFERENCES p(c, d))" {unknown column "c" in foreign key definition} "CREATE TABLE child2(a, b, FOREIGN KEY(c, b) REFERENCES p(c, d))" {unknown column "c" in foreign key definition} } { | | | | | 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 | "CREATE TABLE child2(a, b, FOREIGN KEY(a, b) REFERENCES p(c, d, e))" {number of columns in foreign key does not match the number of columns in the referenced table} "CREATE TABLE child2(a, b, FOREIGN KEY(a, c) REFERENCES p(c, d))" {unknown column "c" in foreign key definition} "CREATE TABLE child2(a, b, FOREIGN KEY(c, b) REFERENCES p(c, d))" {unknown column "c" in foreign key definition} } { do_test e_fkey-22.$fk.[incr i] { catchsql $sql } [list 1 $error] } } #------------------------------------------------------------------------- # /* EV: R-47109-40581 */ # # Test that a REFERENCING clause that does not specify parent key columns # implicitly maps to the primary key of the parent table. # do_test e_fkey-23.1 { execsql { CREATE TABLE p1(a, b, PRIMARY KEY(a, b)); CREATE TABLE p2(a, b PRIMARY KEY); CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p1); CREATE TABLE c2(a, b REFERENCES p2); } } {} proc test_efkey_60 {tn isError sql} { do_test e_fkey-23.$tn " catchsql {$sql} " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError] } test_efkey_60 2 1 "INSERT INTO c1 VALUES(239, 231)" test_efkey_60 3 0 "INSERT INTO p1 VALUES(239, 231)" test_efkey_60 4 0 "INSERT INTO c1 VALUES(239, 231)" |
︙ | ︙ | |||
848 849 850 851 852 853 854 | # # /* EV: R-15741-50893 */ # # Also test that if an index is created on the child key columns, it does # not make a difference whether or not it is a UNIQUE index. # drop_all_tables | | | | 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 | # # /* EV: R-15741-50893 */ # # Also test that if an index is created on the child key columns, it does # not make a difference whether or not it is a UNIQUE index. # drop_all_tables do_test e_fkey-24.1 { execsql { CREATE TABLE parent(x, y, UNIQUE(y, x)); CREATE TABLE c1(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y)); CREATE TABLE c2(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y)); CREATE TABLE c3(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y)); CREATE INDEX c2i ON c2(a, b); CREATE UNIQUE INDEX c3i ON c2(b, a); } } {} proc test_efkey_61 {tn isError sql} { do_test e_fkey-24.$tn " catchsql {$sql} " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError] } foreach {tn c} [list 2 c1 3 c2 4 c3] { test_efkey_61 $tn.1 1 "INSERT INTO $c VALUES(1, 2)" test_efkey_61 $tn.2 0 "INSERT INTO parent VALUES(1, 2)" test_efkey_61 $tn.3 0 "INSERT INTO $c VALUES(1, 2)" |
︙ | ︙ | |||
884 885 886 887 888 889 890 | # SELECT rowid FROM track WHERE trackartist = ? # # /* EV: R-23302-30956 */ # # Also test that if the SELECT above would return any rows, a foreign # key constraint is violated. # | | | | | | | | | 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 | # SELECT rowid FROM track WHERE trackartist = ? # # /* EV: R-23302-30956 */ # # Also test that if the SELECT above would return any rows, a foreign # key constraint is violated. # do_test e_fkey-25.1 { execsql { CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES artist(artistid) ); } } {} do_test e_fkey-25.2 { execsql { PRAGMA foreign_keys = OFF; EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1; EXPLAIN QUERY PLAN SELECT rowid FROM track WHERE trackartist = ?; } } {0 0 {TABLE artist} 0 0 {TABLE track}} do_test e_fkey-25.3 { execsql { PRAGMA foreign_keys = ON; EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1; } } {0 0 {TABLE artist} 0 0 {TABLE track}} do_test e_fkey-25.4 { execsql { INSERT INTO artist VALUES(5, 'artist 5'); INSERT INTO artist VALUES(6, 'artist 6'); INSERT INTO artist VALUES(7, 'artist 7'); INSERT INTO track VALUES(1, 'track 1', 5); INSERT INTO track VALUES(2, 'track 2', 6); } } {} do_test e_fkey-25.5 { concat \ [execsql { SELECT rowid FROM track WHERE trackartist = 5 }] \ [catchsql { DELETE FROM artist WHERE artistid = 5 }] } {1 1 {foreign key constraint failed}} do_test e_fkey-25.6 { concat \ [execsql { SELECT rowid FROM track WHERE trackartist = 7 }] \ [catchsql { DELETE FROM artist WHERE artistid = 7 }] } {0 {}} do_test e_fkey-25.7 { concat \ [execsql { SELECT rowid FROM track WHERE trackartist = 6 }] \ [catchsql { DELETE FROM artist WHERE artistid = 6 }] } {2 1 {foreign key constraint failed}} #------------------------------------------------------------------------- # /* EV: R-54172-55848 */ |
︙ | ︙ | |||
959 960 961 962 963 964 965 | # to the following is planned. In some cases it is not executed, but it # is always planned. # # SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value # # drop_all_tables | | | 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 | # to the following is planned. In some cases it is not executed, but it # is always planned. # # SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value # # drop_all_tables do_test e_fkey-26.1 { execsql { CREATE TABLE parent(x, y, UNIQUE(y, x)) } } {} foreach {tn sql} { 2 { CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y)) } 3 { |
︙ | ︙ | |||
989 990 991 992 993 994 995 | set update [concat \ [eqp "UPDATE parent SET x=?, y=?"] \ [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"] \ [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"] ] execsql {PRAGMA foreign_keys = ON} | | | | | | | | 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 | set update [concat \ [eqp "UPDATE parent SET x=?, y=?"] \ [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"] \ [eqp "SELECT rowid FROM child WHERE a = ? AND b = ?"] ] execsql {PRAGMA foreign_keys = ON} do_test e_fkey-26.$tn.1 { eqp "DELETE FROM parent WHERE 1" } $delete do_test e_fkey-26.$tn.2 { eqp "UPDATE parent set x=?, y=?" } $update execsql {DROP TABLE child} } #------------------------------------------------------------------------- # /* EV: R-14553-34013 */ # # Test the example schema at the end of section 3. Also test that is # is "efficient". In this case "efficient" means that foreign key # related operations on the parent table do not provoke linear scans. # drop_all_tables do_test e_fkey-27.1 { execsql { CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER REFERENCES artist ); CREATE INDEX trackindex ON track(trackartist); } } {} do_test e_fkey-27.2 { eqp { INSERT INTO artist VALUES(?, ?) } } {} do_test e_fkey-27.3 { eqp { UPDATE artist SET artistid = ?, artistname = ? } } [list \ 0 0 {TABLE artist} \ 0 0 {TABLE track WITH INDEX trackindex} \ 0 0 {TABLE track WITH INDEX trackindex} ] do_test e_fkey-27.4 { eqp { DELETE FROM artist } } [list \ 0 0 {TABLE artist} \ 0 0 {TABLE track WITH INDEX trackindex} ] |
︙ | ︙ | |||
1066 1067 1068 1069 1070 1071 1072 | 6 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p(x))" {number of columns in foreign key does not match the number of columns in the referenced table} 7 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p(x,y,z))" {number of columns in foreign key does not match the number of columns in the referenced table} } { drop_all_tables | | | | | | | | | 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 | 6 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p(x))" {number of columns in foreign key does not match the number of columns in the referenced table} 7 "CREATE TABLE c(ii, jj, FOREIGN KEY(jj, ii) REFERENCES p(x,y,z))" {number of columns in foreign key does not match the number of columns in the referenced table} } { drop_all_tables do_test e_fkey-28.$tn [list catchsql $sql] [list 1 $err] } do_test e_fkey-28.8 { drop_all_tables execsql { CREATE TABLE p(x PRIMARY KEY); CREATE TABLE c(a, b, FOREIGN KEY(a,b) REFERENCES p); } catchsql {DELETE FROM p} } {1 {foreign key mismatch}} do_test e_fkey-28.9 { drop_all_tables execsql { CREATE TABLE p(x, y, PRIMARY KEY(x,y)); CREATE TABLE c(a REFERENCES p); } catchsql {DELETE FROM p} } {1 {foreign key mismatch}} #------------------------------------------------------------------------- # /* EV: R-24676-09859 */ # # Test the example schema in the "Composite Foreign Key Constraints" # section. # do_test e_fkey-29.1 { execsql { CREATE TABLE album( albumartist TEXT, albumname TEXT, albumcover BINARY, PRIMARY KEY(albumartist, albumname) ); CREATE TABLE song( songid INTEGER, songartist TEXT, songalbum TEXT, songname TEXT, FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist,albumname) ); } } {} do_test e_fkey-29.2 { execsql { INSERT INTO album VALUES('Elvis Presley', 'Elvis'' Christmas Album', NULL); INSERT INTO song VALUES( 1, 'Elvis Presley', 'Elvis'' Christmas Album', 'Here Comes Santa Clause' ); } } {} do_test e_fkey-29.3 { catchsql { INSERT INTO song VALUES(2, 'Elvis Presley', 'Elvis Is Back!', 'Fever'); } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- # /* EV: R-33626-48418 */ # # Check that if any of the child key columns in the above schema are NULL, # there is no requirement for a corresponding parent key. # do_test e_fkey-30.1 { execsql { INSERT INTO song VALUES(2, 'Elvis Presley', NULL, 'Fever'); INSERT INTO song VALUES(3, NULL, 'Elvis Is Back', 'Soldier Boy'); } } {} ########################################################################### |
︙ | ︙ | |||
1156 1157 1158 1159 1160 1161 1162 | # # Test that if a statement violates an immediate FK constraint, and the # database does not satisfy the FK constraint once all effects of the # statement have been applied, an error is reported and the effects of # the statement rolled back. # drop_all_tables | | | | | | | | 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 | # # Test that if a statement violates an immediate FK constraint, and the # database does not satisfy the FK constraint once all effects of the # statement have been applied, an error is reported and the effects of # the statement rolled back. # drop_all_tables do_test e_fkey-31.1 { execsql { CREATE TABLE king(a, b, PRIMARY KEY(a)); CREATE TABLE prince(c REFERENCES king, d); } } {} do_test e_fkey-31.2 { # Execute a statement that violates the immediate FK constraint. catchsql { INSERT INTO prince VALUES(1, 2) } } {1 {foreign key constraint failed}} do_test e_fkey-31.3 { # This time, use a trigger to fix the constraint violation before the # statement has finished executing. Then execute the same statement as # in the previous test case. This time, no error. execsql { CREATE TRIGGER kt AFTER INSERT ON prince WHEN NOT EXISTS (SELECT a FROM king WHERE a = new.c) BEGIN INSERT INTO king VALUES(new.c, NULL); END } execsql { INSERT INTO prince VALUES(1, 2) } } {} # Test that operating inside a transaction makes no difference to # immediate constraint violation handling. do_test e_fkey-31.4 { execsql { BEGIN; INSERT INTO prince VALUES(2, 3); DROP TRIGGER kt; } catchsql { INSERT INTO prince VALUES(3, 4) } } {1 {foreign key constraint failed}} do_test e_fkey-31.5 { execsql { COMMIT; SELECT * FROM king; } } {1 {} 2 {}} #------------------------------------------------------------------------- # /* EV: R-49178-21358 */ # /* EV: R-39692-12488 */ # /* EV: R-55147-47664 */ # /* EV: R-29604-30395 */ # # Test that if a deferred constraint is violated within a transaction, # nothing happens immediately and the database is allowed to persist # in a state that does not satisfy the FK constraint. However attempts # to COMMIT the transaction fail until the FK constraint is satisfied. # proc test_efkey_34 {tn isError sql} { do_test e_fkey-32.$tn " catchsql {$sql} " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError] } drop_all_tables test_efkey_34 1 0 { CREATE TABLE ll(k PRIMARY KEY); |
︙ | ︙ | |||
1238 1239 1240 1241 1242 1243 1244 | # /* EV: R-56844-61705 */ # # When not running inside a transaction, a deferred constraint is similar # to an immediate constraint (violations are reported immediately). # drop_all_tables proc test_efkey_35 {tn isError sql} { | | | | 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 | # /* EV: R-56844-61705 */ # # When not running inside a transaction, a deferred constraint is similar # to an immediate constraint (violations are reported immediately). # drop_all_tables proc test_efkey_35 {tn isError sql} { do_test e_fkey-33.$tn " catchsql {$sql} " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError] } do_test e_fkey-33.1 { execsql { CREATE TABLE parent(x, y); CREATE UNIQUE INDEX pi ON parent(x, y); CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y) DEFERRABLE INITIALLY DEFERRED ); } |
︙ | ︙ | |||
1283 1284 1285 1286 1287 1288 1289 | # # Foreign keys are IMMEDIATE by default (if there is no DEFERRABLE or NOT # DEFERRABLE clause). # # /* EV: R-30323-21917 */ FKs are either IMMEDIATE or DEFERRED. # drop_all_tables | | | 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 | # # Foreign keys are IMMEDIATE by default (if there is no DEFERRABLE or NOT # DEFERRABLE clause). # # /* EV: R-30323-21917 */ FKs are either IMMEDIATE or DEFERRED. # drop_all_tables do_test e_fkey-34.1 { execsql { CREATE TABLE parent(x, y, z, PRIMARY KEY(x,y,z)); CREATE TABLE c1(a, b, c, FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE c2(a, b, c, FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY IMMEDIATE |
︙ | ︙ | |||
1327 1328 1329 1330 1331 1332 1333 | INSERT INTO c5 VALUES('m', 'n', 'o'); INSERT INTO c6 VALUES('p', 'q', 'r'); INSERT INTO c7 VALUES('s', 't', 'u'); } } {} proc test_efkey_29 {tn sql isError} { | | | 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 | INSERT INTO c5 VALUES('m', 'n', 'o'); INSERT INTO c6 VALUES('p', 'q', 'r'); INSERT INTO c7 VALUES('s', 't', 'u'); } } {} proc test_efkey_29 {tn sql isError} { do_test e_fkey-34.$tn "catchsql {$sql}" [ lindex {{0 {}} {1 {foreign key constraint failed}}} $isError ] } test_efkey_29 2 "BEGIN" 0 test_efkey_29 3 "DELETE FROM parent WHERE x = 'a'" 1 test_efkey_29 4 "DELETE FROM parent WHERE x = 'd'" 1 test_efkey_29 5 "DELETE FROM parent WHERE x = 'g'" 1 |
︙ | ︙ | |||
1382 1383 1384 1385 1386 1387 1388 | #------------------------------------------------------------------------- # /* EV: R-35043-01546 */ # # Test an example from foreignkeys.html dealing with a deferred foreign # key constraint. # | | | | | | | | | | | | | | | | | | | | | | | 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 | #------------------------------------------------------------------------- # /* EV: R-35043-01546 */ # # Test an example from foreignkeys.html dealing with a deferred foreign # key constraint. # do_test e_fkey-35.1 { drop_all_tables execsql { CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER REFERENCES artist(artistid) DEFERRABLE INITIALLY DEFERRED ); } } {} do_test e_fkey-35.2 { execsql { BEGIN; INSERT INTO track VALUES(1, 'White Christmas', 5); } catchsql COMMIT } {1 {foreign key constraint failed}} do_test e_fkey-35.3 { execsql { INSERT INTO artist VALUES(5, 'Bing Crosby'); COMMIT; } } {} #------------------------------------------------------------------------- # /* EV: R-07223-48323 */ # # Verify that a nested savepoint may be released without satisfying # deferred foreign key constraints. # drop_all_tables do_test e_fkey-36.1 { execsql { CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1 DEFERRABLE INITIALLY DEFERRED ); INSERT INTO t1 VALUES(1, 1); INSERT INTO t1 VALUES(2, 2); INSERT INTO t1 VALUES(3, 3); } } {} do_test e_fkey-36.2 { execsql { BEGIN; SAVEPOINT one; INSERT INTO t1 VALUES(4, 5); RELEASE one; } } {} do_test e_fkey-36.3 { catchsql COMMIT } {1 {foreign key constraint failed}} do_test e_fkey-36.4 { execsql { UPDATE t1 SET a = 5 WHERE a = 4; COMMIT; } } {} #------------------------------------------------------------------------- # /* EV: R-44295-13823 */ # # Check that a transaction savepoint (an outermost savepoint opened when # the database was in auto-commit mode) cannot be released without # satisfying deferred foreign key constraints. It may be rolled back. # do_test e_fkey-37.1 { execsql { SAVEPOINT one; SAVEPOINT two; INSERT INTO t1 VALUES(6, 7); RELEASE two; } } {} do_test e_fkey-37.2 { catchsql {RELEASE one} } {1 {foreign key constraint failed}} do_test e_fkey-37.3 { execsql { UPDATE t1 SET a = 7 WHERE a = 6; RELEASE one; } } {} do_test e_fkey-37.4 { execsql { SAVEPOINT one; SAVEPOINT two; INSERT INTO t1 VALUES(9, 10); RELEASE two; } } {} do_test e_fkey-37.5 { catchsql {RELEASE one} } {1 {foreign key constraint failed}} do_test e_fkey-37.6 { execsql {ROLLBACK TO one ; RELEASE one} } {} #------------------------------------------------------------------------- # /* EV: R-37736-42616 */ # # Test that if a COMMIT operation fails due to deferred foreign key # constraints, any nested savepoints remain open. # do_test e_fkey-38.1 { execsql { DELETE FROM t1 WHERE a>3; SELECT * FROM t1; } } {1 1 2 2 3 3} do_test e_fkey-38.2 { execsql { BEGIN; INSERT INTO t1 VALUES(4, 4); SAVEPOINT one; INSERT INTO t1 VALUES(5, 6); SELECT * FROM t1; } } {1 1 2 2 3 3 4 4 5 6} do_test e_fkey-38.3 { catchsql COMMIT } {1 {foreign key constraint failed}} do_test e_fkey-38.4 { execsql { ROLLBACK TO one; COMMIT; SELECT * FROM t1; } } {1 1 2 2 3 3 4 4} do_test e_fkey-38.5 { execsql { SAVEPOINT a; INSERT INTO t1 VALUES(5, 5); SAVEPOINT b; INSERT INTO t1 VALUES(6, 7); SAVEPOINT c; INSERT INTO t1 VALUES(7, 8); } } {} do_test e_fkey-38.6 { catchsql {RELEASE a} } {1 {foreign key constraint failed}} do_test e_fkey-38.7 { execsql {ROLLBACK TO c} catchsql {RELEASE a} } {1 {foreign key constraint failed}} do_test e_fkey-38.8 { execsql { ROLLBACK TO b; RELEASE a; SELECT * FROM t1; } } {1 1 2 2 3 3 4 4 5 5} |
︙ | ︙ | |||
1557 1558 1559 1560 1561 1562 1563 | # deleting or modifying rows of the parent table, respectively. # # /* EV: R-48124-63225 */ # # Test that a single FK constraint may have different actions configured # for ON DELETE and ON UPDATE. # | | | | | | | 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 | # deleting or modifying rows of the parent table, respectively. # # /* EV: R-48124-63225 */ # # Test that a single FK constraint may have different actions configured # for ON DELETE and ON UPDATE. # do_test e_fkey-39.1 { execsql { CREATE TABLE p(a, b PRIMARY KEY, c); CREATE TABLE c1(d, e, f DEFAULT 'k0' REFERENCES p ON UPDATE SET DEFAULT ON DELETE SET NULL ); INSERT INTO p VALUES(0, 'k0', ''); INSERT INTO p VALUES(1, 'k1', 'I'); INSERT INTO p VALUES(2, 'k2', 'II'); INSERT INTO p VALUES(3, 'k3', 'III'); INSERT INTO c1 VALUES(1, 'xx', 'k1'); INSERT INTO c1 VALUES(2, 'xx', 'k2'); INSERT INTO c1 VALUES(3, 'xx', 'k3'); } } {} do_test e_fkey-39.2 { execsql { UPDATE p SET b = 'k4' WHERE a = 1; SELECT * FROM c1; } } {1 xx k0 2 xx k2 3 xx k3} do_test e_fkey-39.3 { execsql { DELETE FROM p WHERE a = 2; SELECT * FROM c1; } } {1 xx k0 2 xx {} 3 xx k3} do_test e_fkey-39.4 { execsql { CREATE UNIQUE INDEX pi ON p(c); REPLACE INTO p VALUES(5, 'k5', 'III'); SELECT * FROM c1; } } {1 xx k0 2 xx {} 3 xx {}} #------------------------------------------------------------------------- # /* EV: R-33326-45252 */ # # Each foreign key in the system has an ON UPDATE and ON DELETE action, # either "NO ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE". # # /* EV: R-19803-45884 */ # # If none is specified explicitly, "NO ACTION" is the default. # drop_all_tables do_test e_fkey-40.1 { execsql { CREATE TABLE parent(x PRIMARY KEY, y); CREATE TABLE child1(a, b REFERENCES parent ON UPDATE NO ACTION ON DELETE RESTRICT ); CREATE TABLE child2(a, b REFERENCES parent ON UPDATE RESTRICT ON DELETE SET NULL |
︙ | ︙ | |||
1640 1641 1642 1643 1644 1645 1646 | 4 child3 {0 0 parent b {} {SET NULL} {SET DEFAULT} NONE} 5 child4 {0 0 parent b {} {SET DEFAULT} CASCADE NONE} 6 child5 {0 0 parent b {} CASCADE {NO ACTION} NONE} 7 child6 {0 0 parent b {} {NO ACTION} RESTRICT NONE} 8 child7 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE} 9 child8 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE} } { | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 2061 2062 2063 2064 2065 2066 2067 2068 2069 2070 2071 2072 2073 2074 2075 2076 2077 2078 2079 2080 2081 2082 2083 2084 2085 2086 2087 2088 2089 2090 2091 2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115 2116 2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 2127 2128 2129 2130 2131 2132 2133 2134 2135 2136 2137 2138 2139 2140 2141 2142 2143 2144 2145 2146 2147 2148 2149 2150 2151 2152 2153 2154 2155 2156 2157 2158 2159 2160 2161 2162 2163 2164 2165 2166 2167 2168 2169 2170 2171 2172 2173 2174 2175 2176 2177 2178 2179 2180 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 2215 2216 2217 2218 2219 2220 2221 2222 2223 2224 2225 2226 2227 2228 2229 2230 2231 2232 2233 2234 2235 2236 2237 2238 2239 2240 2241 2242 2243 2244 2245 2246 2247 2248 2249 2250 2251 2252 2253 2254 2255 2256 2257 2258 2259 2260 2261 2262 2263 2264 2265 2266 2267 2268 2269 2270 2271 2272 2273 2274 2275 2276 2277 2278 2279 2280 2281 2282 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292 2293 | 4 child3 {0 0 parent b {} {SET NULL} {SET DEFAULT} NONE} 5 child4 {0 0 parent b {} {SET DEFAULT} CASCADE NONE} 6 child5 {0 0 parent b {} CASCADE {NO ACTION} NONE} 7 child6 {0 0 parent b {} {NO ACTION} RESTRICT NONE} 8 child7 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE} 9 child8 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE} } { do_test e_fkey-40.$tn { execsql "PRAGMA foreign_key_list($zTab)" } $lRes } #------------------------------------------------------------------------- # /* EV: R-19971-54976 */ # # Test that "NO ACTION" means that nothing happens to a child row when # it's parent row is updated or deleted. # drop_all_tables do_test e_fkey-41.1 { execsql { CREATE TABLE parent(p1, p2, PRIMARY KEY(p1, p2)); CREATE TABLE child(c1, c2, FOREIGN KEY(c1, c2) REFERENCES parent ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ); INSERT INTO parent VALUES('j', 'k'); INSERT INTO parent VALUES('l', 'm'); INSERT INTO child VALUES('j', 'k'); INSERT INTO child VALUES('l', 'm'); } } {} do_test e_fkey-41.2 { execsql { BEGIN; UPDATE parent SET p1='k' WHERE p1='j'; DELETE FROM parent WHERE p1='l'; SELECT * FROM child; } } {j k l m} do_test e_fkey-41.3 { catchsql COMMIT } {1 {foreign key constraint failed}} do_test e_fkey-41.4 { execsql ROLLBACK } {} #------------------------------------------------------------------------- # /* EV: R-04272-38653 */ # # Test that "RESTRICT" means the application is prohibited from deleting # or updating a parent table row when there exists one or more child keys # mapped to it. # drop_all_tables do_test e_fkey-41.1 { execsql { CREATE TABLE parent(p1, p2); CREATE UNIQUE INDEX parent_i ON parent(p1, p2); CREATE TABLE child1(c1, c2, FOREIGN KEY(c2, c1) REFERENCES parent(p1, p2) ON DELETE RESTRICT ); CREATE TABLE child2(c1, c2, FOREIGN KEY(c2, c1) REFERENCES parent(p1, p2) ON UPDATE RESTRICT ); } } {} do_test e_fkey-41.2 { execsql { INSERT INTO parent VALUES('a', 'b'); INSERT INTO parent VALUES('c', 'd'); INSERT INTO child1 VALUES('b', 'a'); INSERT INTO child2 VALUES('d', 'c'); } } {} do_test e_fkey-41.3 { catchsql { DELETE FROM parent WHERE p1 = 'a' } } {1 {foreign key constraint failed}} do_test e_fkey-41.4 { catchsql { UPDATE parent SET p2 = 'e' WHERE p1 = 'c' } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- # /* EV: R-37997-42187 */ # # Test that RESTRICT is slightly different from NO ACTION for IMMEDIATE # constraints, in that it is enforced immediately, not at the end of the # statement. # drop_all_tables do_test e_fkey-42.1 { execsql { CREATE TABLE parent(x PRIMARY KEY); CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT); CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION); INSERT INTO parent VALUES('key1'); INSERT INTO parent VALUES('key2'); INSERT INTO child1 VALUES('key1'); INSERT INTO child2 VALUES('key2'); CREATE TRIGGER parent_t AFTER UPDATE ON parent BEGIN UPDATE child1 set c = new.x WHERE c = old.x; UPDATE child2 set c = new.x WHERE c = old.x; END; } } {} do_test e_fkey-42.2 { catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' } } {1 {foreign key constraint failed}} do_test e_fkey-42.3 { execsql { UPDATE parent SET x = 'key two' WHERE x = 'key2'; SELECT * FROM child2; } } {{key two}} drop_all_tables do_test e_fkey-42.4 { execsql { CREATE TABLE parent(x PRIMARY KEY); CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT); CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION); INSERT INTO parent VALUES('key1'); INSERT INTO parent VALUES('key2'); INSERT INTO child1 VALUES('key1'); INSERT INTO child2 VALUES('key2'); CREATE TRIGGER parent_t AFTER DELETE ON parent BEGIN UPDATE child1 SET c = NULL WHERE c = old.x; UPDATE child2 SET c = NULL WHERE c = old.x; END; } } {} do_test e_fkey-42.5 { catchsql { DELETE FROM parent WHERE x = 'key1' } } {1 {foreign key constraint failed}} do_test e_fkey-42.6 { execsql { DELETE FROM parent WHERE x = 'key2'; SELECT * FROM child2; } } {{}} drop_all_tables do_test e_fkey-42.7 { execsql { CREATE TABLE parent(x PRIMARY KEY); CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT); CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION); INSERT INTO parent VALUES('key1'); INSERT INTO parent VALUES('key2'); INSERT INTO child1 VALUES('key1'); INSERT INTO child2 VALUES('key2'); } } {} do_test e_fkey-42.8 { catchsql { REPLACE INTO parent VALUES('key1') } } {1 {foreign key constraint failed}} do_test e_fkey-42.9 { execsql { REPLACE INTO parent VALUES('key2'); SELECT * FROM child2; } } {key2} #------------------------------------------------------------------------- # /* EV: R-24179-60523 */ # # Test that RESTRICT is enforced immediately, even for a DEFERRED constraint. # drop_all_tables do_test e_fkey-43.1 { execsql { CREATE TABLE parent(x PRIMARY KEY); CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED ); INSERT INTO parent VALUES('key1'); INSERT INTO parent VALUES('key2'); INSERT INTO child1 VALUES('key1'); INSERT INTO child2 VALUES('key2'); BEGIN; } } {} do_test e_fkey-43.2 { catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' } } {1 {foreign key constraint failed}} do_test e_fkey-43.3 { execsql { UPDATE parent SET x = 'key two' WHERE x = 'key2' } } {} do_test e_fkey-43.4 { catchsql COMMIT } {1 {foreign key constraint failed}} do_test e_fkey-43.5 { execsql { UPDATE child2 SET c = 'key two'; COMMIT; } } {} drop_all_tables do_test e_fkey-43.6 { execsql { CREATE TABLE parent(x PRIMARY KEY); CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ); INSERT INTO parent VALUES('key1'); INSERT INTO parent VALUES('key2'); INSERT INTO child1 VALUES('key1'); INSERT INTO child2 VALUES('key2'); BEGIN; } } {} do_test e_fkey-43.7 { catchsql { DELETE FROM parent WHERE x = 'key1' } } {1 {foreign key constraint failed}} do_test e_fkey-43.8 { execsql { DELETE FROM parent WHERE x = 'key2' } } {} do_test e_fkey-43.9 { catchsql COMMIT } {1 {foreign key constraint failed}} do_test e_fkey-43.10 { execsql { UPDATE child2 SET c = NULL; COMMIT; } } {} #------------------------------------------------------------------------- # /* EV: R-03353-05327 */ # # Test SET NULL actions. # drop_all_tables do_test e_fkey-44.1 { execsql { CREATE TABLE pA(x PRIMARY KEY); CREATE TABLE cA(c REFERENCES pA ON DELETE SET NULL); CREATE TABLE cB(c REFERENCES pA ON UPDATE SET NULL); INSERT INTO pA VALUES(X'ABCD'); INSERT INTO pA VALUES(X'1234'); INSERT INTO cA VALUES(X'ABCD'); INSERT INTO cB VALUES(X'1234'); } } {} do_test e_fkey-44.2 { execsql { DELETE FROM pA WHERE rowid = 1; SELECT quote(x) FROM pA; } } {X'1234'} do_test e_fkey-44.3 { execsql { SELECT quote(c) FROM cA; } } {NULL} do_test e_fkey-44.4 { execsql { UPDATE pA SET x = X'8765' WHERE rowid = 2; SELECT quote(x) FROM pA; } } {X'8765'} do_test e_fkey-44.5 { execsql { SELECT quote(c) FROM cB } } {NULL} #------------------------------------------------------------------------- # /* EV: R-43054-54832 */ # # Test SET DEFAULT actions. # drop_all_tables do_test e_fkey-45.1 { execsql { CREATE TABLE pA(x PRIMARY KEY); CREATE TABLE cA(c DEFAULT X'0000' REFERENCES pA ON DELETE SET DEFAULT); CREATE TABLE cB(c DEFAULT X'9999' REFERENCES pA ON UPDATE SET DEFAULT); INSERT INTO pA(rowid, x) VALUES(1, X'0000'); INSERT INTO pA(rowid, x) VALUES(2, X'9999'); INSERT INTO pA(rowid, x) VALUES(3, X'ABCD'); INSERT INTO pA(rowid, x) VALUES(4, X'1234'); INSERT INTO cA VALUES(X'ABCD'); INSERT INTO cB VALUES(X'1234'); } } {} do_test e_fkey-45.2 { execsql { DELETE FROM pA WHERE rowid = 3; SELECT quote(x) FROM pA; } } {X'0000' X'9999' X'1234'} do_test e_fkey-45.3 { execsql { SELECT quote(c) FROM cA } } {X'0000'} do_test e_fkey-45.4 { execsql { UPDATE pA SET x = X'8765' WHERE rowid = 4; SELECT quote(x) FROM pA; } } {X'0000' X'9999' X'8765'} do_test e_fkey-45.5 { execsql { SELECT quote(c) FROM cB } } {X'9999'} #------------------------------------------------------------------------- # /* EV: R-61376-57267 */ # /* EV: R-61809-62207 */ # # Test ON DELETE CASCADE actions. # drop_all_tables do_test e_fkey-46.1 { execsql { CREATE TABLE p1(a, b UNIQUE); CREATE TABLE c1(c REFERENCES p1(b) ON DELETE CASCADE, d); INSERT INTO p1 VALUES(NULL, NULL); INSERT INTO p1 VALUES(4, 4); INSERT INTO p1 VALUES(5, 5); INSERT INTO c1 VALUES(NULL, NULL); INSERT INTO c1 VALUES(4, 4); INSERT INTO c1 VALUES(5, 5); SELECT count(*) FROM c1; } } {3} do_test e_fkey-46.2 { execsql { DELETE FROM p1 WHERE a = 4; SELECT d, c FROM c1; } } {{} {} 5 5} do_test e_fkey-46.3 { execsql { DELETE FROM p1; SELECT d, c FROM c1; } } {{} {}} do_test e_fkey-46.4 { execsql { SELECT * FROM p1 } } {} #------------------------------------------------------------------------- # /* EV: R-61376-57267 */ # /* EV: R-13877-64542 */ # # Test ON UPDATE CASCADE actions. # drop_all_tables do_test e_fkey-47.1 { execsql { CREATE TABLE p1(a, b UNIQUE); CREATE TABLE c1(c REFERENCES p1(b) ON UPDATE CASCADE, d); INSERT INTO p1 VALUES(NULL, NULL); INSERT INTO p1 VALUES(4, 4); INSERT INTO p1 VALUES(5, 5); INSERT INTO c1 VALUES(NULL, NULL); INSERT INTO c1 VALUES(4, 4); INSERT INTO c1 VALUES(5, 5); SELECT count(*) FROM c1; } } {3} do_test e_fkey-47.2 { execsql { UPDATE p1 SET b = 10 WHERE b = 5; SELECT d, c FROM c1; } } {{} {} 4 4 5 10} do_test e_fkey-47.3 { execsql { UPDATE p1 SET b = 11 WHERE b = 4; SELECT d, c FROM c1; } } {{} {} 4 11 5 10} do_test e_fkey-47.4 { execsql { UPDATE p1 SET b = 6 WHERE b IS NULL; SELECT d, c FROM c1; } } {{} {} 4 11 5 10} do_test e_fkey-46.5 { execsql { SELECT * FROM p1 } } {{} 6 4 11 5 10} #------------------------------------------------------------------------- # /* EV: R-51329-33438 */ # # Test an example from the "ON DELETE and ON UPDATE Actions" section # of foreignkeys.html. # drop_all_tables do_test e_fkey-48.1 { execsql { CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE ); INSERT INTO artist VALUES(1, 'Dean Martin'); INSERT INTO artist VALUES(2, 'Frank Sinatra'); INSERT INTO track VALUES(11, 'That''s Amore', 1); INSERT INTO track VALUES(12, 'Christmas Blues', 1); INSERT INTO track VALUES(13, 'My Way', 2); } } {} do_test e_fkey-48.2 { execsql { UPDATE artist SET artistid = 100 WHERE artistname = 'Dean Martin'; } } {} do_test e_fkey-48.3 { execsql { SELECT * FROM artist } } {2 {Frank Sinatra} 100 {Dean Martin}} do_test e_fkey-48.4 { execsql { SELECT * FROM track } } {11 {That's Amore} 100 12 {Christmas Blues} 100 13 {My Way} 2} #------------------------------------------------------------------------- # /* EV: R-53968-51642 */ # # Verify that adding an FK action does not absolve the user of the # requirement not to violate the foreign key constraint. # drop_all_tables do_test e_fkey-49.1 { execsql { CREATE TABLE parent(a COLLATE nocase, b, c, PRIMARY KEY(c, a)); CREATE TABLE child(d DEFAULT 'a', e, f DEFAULT 'c', FOREIGN KEY(f, d) REFERENCES parent ON UPDATE SET DEFAULT ); INSERT INTO parent VALUES('A', 'b', 'c'); INSERT INTO parent VALUES('ONE', 'two', 'three'); INSERT INTO child VALUES('one', 'two', 'three'); } } {} do_test e_fkey-49.2 { execsql { BEGIN; UPDATE parent SET a = '' WHERE a = 'oNe'; SELECT * FROM child; } } {a two c} do_test e_fkey-49.3 { execsql { ROLLBACK; DELETE FROM parent WHERE a = 'A'; SELECT * FROM parent; } } {ONE two three} do_test e_fkey-49.4 { catchsql { UPDATE parent SET a = '' WHERE a = 'oNe' } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- # /* EV: R-07065-59588 */ # /* EV: R-28220-46694 */ # # Test an example from the "ON DELETE and ON UPDATE Actions" section # of foreignkeys.html. This example shows that adding an "ON DELETE DEFAULT" # clause does not abrogate the need to satisfy the foreign key constraint # (R-28220-46694). # drop_all_tables do_test e_fkey-50.1 { execsql { CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER DEFAULT 0 REFERENCES artist(artistid) ON DELETE SET DEFAULT ); INSERT INTO artist VALUES(3, 'Sammy Davis Jr.'); INSERT INTO track VALUES(14, 'Mr. Bojangles', 3); } } {} do_test e_fkey-50.2 { catchsql { DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.' } } {1 {foreign key constraint failed}} do_test e_fkey-50.3 { execsql { INSERT INTO artist VALUES(0, 'Unknown Artist'); DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.'; } } {} do_test e_fkey-50.4 { execsql { SELECT * FROM artist } } {0 {Unknown Artist}} do_test e_fkey-50.5 { execsql { SELECT * FROM track } } {14 {Mr. Bojangles} 0} #------------------------------------------------------------------------- # /* EV: R-09564-22170 */ # # Check that the order of steps in an UPDATE or DELETE on a parent # table is as follows: # # 1. Execute applicable BEFORE trigger programs, # 2. Check local (non foreign key) constraints, # 3. Update or delete the row in the parent table, # 4. Perform any required foreign key actions, # 5. Execute applicable AFTER trigger programs. # drop_all_tables do_test e_fkey-51.1 { proc maxparent {args} { db one {SELECT max(x) FROM parent} } db func maxparent maxparent execsql { CREATE TABLE parent(x PRIMARY KEY); CREATE TRIGGER bu BEFORE UPDATE ON parent BEGIN INSERT INTO parent VALUES(new.x-old.x); END; CREATE TABLE child( a DEFAULT (maxparent()) REFERENCES parent ON UPDATE SET DEFAULT ); CREATE TRIGGER au AFTER UPDATE ON parent BEGIN INSERT INTO parent VALUES(new.x+old.x); END; INSERT INTO parent VALUES(1); INSERT INTO child VALUES(1); } } {} do_test e_fkey-51.2 { execsql { UPDATE parent SET x = 22; SELECT * FROM parent UNION ALL SELECT 'xxx' UNION ALL SELECT a FROM child; } } {22 21 23 xxx 22} do_test e_fkey-51.3 { execsql { DELETE FROM child; DELETE FROM parent; INSERT INTO parent VALUES(-1); INSERT INTO child VALUES(-1); UPDATE parent SET x = 22; SELECT * FROM parent UNION ALL SELECT 'xxx' UNION ALL SELECT a FROM child; } } {22 23 21 xxx 23} #------------------------------------------------------------------------- # /* EV: R-27383-10246 */ # # Verify that ON UPDATE actions only actually take place if the parent key # is set to a new value that is distinct from the old value. The default # collation sequence and affinity are used to determine if the new value # is 'distinct' from the old or not. # drop_all_tables do_test e_fkey-52.1 { execsql { CREATE TABLE zeus(a INTEGER COLLATE NOCASE, b, PRIMARY KEY(a, b)); CREATE TABLE apollo(c, d, FOREIGN KEY(c, d) REFERENCES zeus ON UPDATE CASCADE ); INSERT INTO zeus VALUES('abc', 'xyz'); INSERT INTO apollo VALUES('ABC', 'xyz'); } execsql { UPDATE zeus SET a = 'aBc'; SELECT * FROM apollo; } } {ABC xyz} do_test e_fkey-52.2 { execsql { UPDATE zeus SET a = 1, b = 1; SELECT * FROM apollo; } } {1 1} do_test e_fkey-52.3 { execsql { UPDATE zeus SET a = 1, b = 1; SELECT typeof(c), c, typeof(d), d FROM apollo; } } {integer 1 integer 1} do_test e_fkey-52.4 { execsql { UPDATE zeus SET a = '1'; SELECT typeof(c), c, typeof(d), d FROM apollo; } } {integer 1 integer 1} do_test e_fkey-52.5 { execsql { UPDATE zeus SET b = '1'; SELECT typeof(c), c, typeof(d), d FROM apollo; } } {integer 1 text 1} do_test e_fkey-52.6 { execsql { UPDATE zeus SET b = NULL; SELECT typeof(c), c, typeof(d), d FROM apollo; } } {integer 1 null {}} #------------------------------------------------------------------------- # /* EV: R-58589-50781 */ # # Test an example from the "ON DELETE and ON UPDATE Actions" section # of foreignkeys.html. This example demonstrates that ON UPDATE actions # only take place if at least one parent key column is set to a value # that is distinct from its previous value. # drop_all_tables do_test e_fkey-53.1 { execsql { CREATE TABLE parent(x PRIMARY KEY); CREATE TABLE child(y REFERENCES parent ON UPDATE SET NULL); INSERT INTO parent VALUES('key'); INSERT INTO child VALUES('key'); } } {} do_test e_fkey-53.2 { execsql { UPDATE parent SET x = 'key'; SELECT IFNULL(y, 'null') FROM child; } } {key} do_test e_fkey-53.3 { execsql { UPDATE parent SET x = 'key2'; SELECT IFNULL(y, 'null') FROM child; } } {null} ########################################################################### |
︙ | ︙ | |||
2321 2322 2323 2324 2325 2326 2327 | 7 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t1(a,b))" {0 {}} A "CREATE TABLE t1(a, b, FOREIGN KEY(c,b) REFERENCES t2)" {1 {unknown column "c" in foreign key definition}} B "CREATE TABLE t1(a, b, FOREIGN KEY(c,b) REFERENCES t2(d))" {1 {number of columns in foreign key does not match the number of columns in the referenced table}} } { | | | | | | | | | | 2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 2346 2347 2348 2349 2350 2351 2352 2353 2354 2355 2356 2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 2399 2400 2401 2402 2403 2404 2405 2406 2407 2408 2409 2410 2411 2412 2413 2414 2415 2416 2417 2418 2419 | 7 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t1(a,b))" {0 {}} A "CREATE TABLE t1(a, b, FOREIGN KEY(c,b) REFERENCES t2)" {1 {unknown column "c" in foreign key definition}} B "CREATE TABLE t1(a, b, FOREIGN KEY(c,b) REFERENCES t2(d))" {1 {number of columns in foreign key does not match the number of columns in the referenced table}} } { do_test e_fkey-54.$tn.off { drop_all_tables execsql {PRAGMA foreign_keys = OFF} catchsql $zCreateTbl } $lRes do_test e_fkey-54.$tn.on { drop_all_tables execsql {PRAGMA foreign_keys = ON} catchsql $zCreateTbl } $lRes } #------------------------------------------------------------------------- # /* EV: R-47952-62498 */ # proc test_efkey_6 {tn zAlter isError} { drop_all_tables do_test e_fkey-56.$tn.1 " execsql { CREATE TABLE tbl(a, b) } [list catchsql $zAlter] " [lindex {{0 {}} {1 {Cannot add a REFERENCES column with non-NULL default value}}} $isError] } test_efkey_6 1 "ALTER TABLE tbl ADD COLUMN c REFERENCES xx" 0 test_efkey_6 2 "ALTER TABLE tbl ADD COLUMN c DEFAULT NULL REFERENCES xx" 0 test_efkey_6 3 "ALTER TABLE tbl ADD COLUMN c DEFAULT 0 REFERENCES xx" 1 #------------------------------------------------------------------------- # /* EV: R-47080-02069 */ # # Test that ALTER TABLE adjusts REFERENCES clauses when the parent table # is RENAMED. # # /* EV: R-63827-54774 */ # # Test that these adjustments are visible in the sqlite_master table. # do_test e_fkey-56.1 { drop_all_tables execsql { CREATE TABLE 'p 1 "parent one"'(a REFERENCES 'p 1 "parent one"', b, PRIMARY KEY(b)); CREATE TABLE c1(c, d REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE); CREATE TABLE c2(e, f, FOREIGN KEY(f) REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE); CREATE TABLE c3(e, 'f col 2', FOREIGN KEY('f col 2') REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE); INSERT INTO 'p 1 "parent one"' VALUES(1, 1); INSERT INTO c1 VALUES(1, 1); INSERT INTO c2 VALUES(1, 1); INSERT INTO c3 VALUES(1, 1); -- CREATE TABLE q(a, b, PRIMARY KEY(b)); } } {} do_test e_fkey-56.2 { execsql { ALTER TABLE 'p 1 "parent one"' RENAME TO p } } {} do_test e_fkey-56.3 { execsql { UPDATE p SET a = 'xxx', b = 'xxx'; SELECT * FROM p; SELECT * FROM c1; SELECT * FROM c2; SELECT * FROM c3; } } {xxx xxx 1 xxx 1 xxx 1 xxx} do_test e_fkey-56.4 { execsql { SELECT sql FROM sqlite_master WHERE type = 'table'} } [list \ {CREATE TABLE "p"(a REFERENCES "p", b, PRIMARY KEY(b))} \ {CREATE TABLE c1(c, d REFERENCES "p" ON UPDATE CASCADE)} \ {CREATE TABLE c2(e, f, FOREIGN KEY(f) REFERENCES "p" ON UPDATE CASCADE)} \ {CREATE TABLE c3(e, 'f col 2', FOREIGN KEY('f col 2') REFERENCES "p" ON UPDATE CASCADE)} \ ] #------------------------------------------------------------------------- # /* EV: R-14208-23986 */ # /* EV: R-11078-03945 */ # # Check that a DROP TABLE does an implicit DELETE FROM. Which does not # cause any triggers to fire, but does fire foreign key actions. # do_test e_fkey-57.1 { drop_all_tables execsql { CREATE TABLE p(a, b, PRIMARY KEY(a, b)); CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE SET NULL); CREATE TABLE c2(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE SET DEFAULT); CREATE TABLE c3(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE CASCADE); |
︙ | ︙ | |||
2432 2433 2434 2435 2436 2437 2438 | CREATE TABLE log(msg); CREATE TRIGGER tt AFTER DELETE ON p BEGIN INSERT INTO log VALUES('delete ' || old.rowid); END; } } {} | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 2432 2433 2434 2435 2436 2437 2438 2439 2440 2441 2442 2443 2444 2445 2446 2447 2448 2449 2450 2451 2452 2453 2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 2521 2522 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 2534 2535 2536 2537 2538 2539 2540 2541 2542 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 2564 2565 2566 2567 2568 2569 2570 2571 2572 2573 2574 2575 2576 2577 2578 2579 2580 2581 2582 2583 2584 2585 2586 2587 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 2606 2607 2608 2609 2610 2611 2612 2613 2614 2615 2616 2617 2618 2619 2620 2621 2622 2623 2624 2625 2626 2627 2628 2629 2630 2631 2632 2633 2634 2635 2636 2637 2638 2639 2640 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 2656 2657 2658 2659 2660 2661 2662 2663 2664 2665 2666 2667 2668 2669 2670 2671 2672 2673 2674 2675 2676 2677 2678 2679 | CREATE TABLE log(msg); CREATE TRIGGER tt AFTER DELETE ON p BEGIN INSERT INTO log VALUES('delete ' || old.rowid); END; } } {} do_test e_fkey-57.2 { execsql { INSERT INTO p VALUES('a', 'b'); INSERT INTO c1 VALUES('a', 'b'); INSERT INTO c2 VALUES('a', 'b'); INSERT INTO c3 VALUES('a', 'b'); BEGIN; DROP TABLE p; SELECT * FROM c1; } } {{} {}} do_test e_fkey-57.3 { execsql { SELECT * FROM c2 } } {{} {}} do_test e_fkey-57.4 { execsql { SELECT * FROM c3 } } {} do_test e_fkey-57.5 { execsql { SELECT * FROM log } } {} do_test e_fkey-57.6 { execsql ROLLBACK } {} do_test e_fkey-57.7 { execsql { BEGIN; DELETE FROM p; SELECT * FROM log; ROLLBACK; } } {{delete 1}} #------------------------------------------------------------------------- # /* EV: R-32768-47925 */ # # If an IMMEDIATE foreign key fails as a result of a DROP TABLE, the # DROP TABLE command fails. # do_test e_fkey-58.1 { execsql { DELETE FROM c1; DELETE FROM c2; DELETE FROM c3; } execsql { INSERT INTO c5 VALUES('a', 'b') } catchsql { DROP TABLE p } } {1 {foreign key constraint failed}} do_test e_fkey-58.2 { execsql { SELECT * FROM p } } {a b} do_test e_fkey-58.3 { catchsql { BEGIN; DROP TABLE p; } } {1 {foreign key constraint failed}} do_test e_fkey-58.4 { execsql { SELECT * FROM p; SELECT * FROM c5; ROLLBACK; } } {a b a b} #------------------------------------------------------------------------- # /* EV: R-05903-08460 */ # # If a DEFERRED foreign key fails as a result of a DROP TABLE, attempting # to commit the transaction fails unless the violation is fixed. # do_test e_fkey-59.1 { execsql { DELETE FROM c1 ; DELETE FROM c2 ; DELETE FROM c3 ; DELETE FROM c4 ; DELETE FROM c5 ; DELETE FROM c6 ; DELETE FROM c7 } } {} do_test e_fkey-59.2 { execsql { INSERT INTO c7 VALUES('a', 'b') } execsql { BEGIN; DROP TABLE p; } } {} do_test e_fkey-59.3 { catchsql COMMIT } {1 {foreign key constraint failed}} do_test e_fkey-59.4 { execsql { CREATE TABLE p(a, b, PRIMARY KEY(a, b)) } catchsql COMMIT } {1 {foreign key constraint failed}} do_test e_fkey-59.5 { execsql { INSERT INTO p VALUES('a', 'b') } execsql COMMIT } {} #------------------------------------------------------------------------- # /* EV: R-57242-37005 */ # # Any "foreign key mismatch" errors encountered while running an implicit # "DELETE FROM tbl" are ignored. # drop_all_tables do_test e_fkey-60.1 { execsql { PRAGMA foreign_keys = OFF; CREATE TABLE p(a PRIMARY KEY, b REFERENCES nosuchtable); CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES a); CREATE TABLE c2(c REFERENCES p(b), d); CREATE TABLE c3(c REFERENCES p ON DELETE SET NULL, d); INSERT INTO p VALUES(1, 2); INSERT INTO c1 VALUES(1, 2); INSERT INTO c2 VALUES(1, 2); INSERT INTO c3 VALUES(1, 2); } } {} do_test e_fkey-60.2 { execsql { PRAGMA foreign_keys = ON } catchsql { DELETE FROM p } } {1 {no such table: main.nosuchtable}} do_test e_fkey-60.3 { execsql { BEGIN; DROP TABLE p; SELECT * FROM c3; ROLLBACK; } } {{} 2} do_test e_fkey-60.4 { execsql { CREATE TABLE nosuchtable(x PRIMARY KEY) } catchsql { DELETE FROM p } } {1 {foreign key mismatch}} do_test e_fkey-60.5 { execsql { DROP TABLE c1 } catchsql { DELETE FROM p } } {1 {foreign key mismatch}} do_test e_fkey-60.6 { execsql { DROP TABLE c2 } execsql { DELETE FROM p } } {} #------------------------------------------------------------------------- # /* EV: R-54142-41346 */ # # Test that the special behaviours of ALTER and DROP TABLE are only # activated when foreign keys are enabled. Special behaviours are: # # 1. ADD COLUMN not allowing a REFERENCES clause with a non-NULL # default value. # 2. Modifying foreign key definitions when a parent table is RENAMEd. # 3. Running an implicit DELETE FROM command as part of DROP TABLE. # do_test e_fkey-61.1.1 { drop_all_tables execsql { CREATE TABLE t1(a, b) } catchsql { ALTER TABLE t1 ADD COLUMN c DEFAULT 'xxx' REFERENCES t2 } } {1 {Cannot add a REFERENCES column with non-NULL default value}} do_test e_fkey-61.1.2 { execsql { PRAGMA foreign_keys = OFF } execsql { ALTER TABLE t1 ADD COLUMN c DEFAULT 'xxx' REFERENCES t2 } execsql { SELECT sql FROM sqlite_master WHERE name = 't1' } } {{CREATE TABLE t1(a, b, c DEFAULT 'xxx' REFERENCES t2)}} do_test e_fkey-61.1.3 { execsql { PRAGMA foreign_keys = ON } } {} do_test e_fkey-61.2.1 { drop_all_tables execsql { CREATE TABLE p(a UNIQUE); CREATE TABLE c(b REFERENCES p(a)); BEGIN; ALTER TABLE p RENAME TO parent; SELECT sql FROM sqlite_master WHERE name = 'c'; ROLLBACK; } } {{CREATE TABLE c(b REFERENCES "parent"(a))}} do_test e_fkey-61.2.2 { execsql { PRAGMA foreign_keys = OFF; ALTER TABLE p RENAME TO parent; SELECT sql FROM sqlite_master WHERE name = 'c'; } } {{CREATE TABLE c(b REFERENCES p(a))}} do_test e_fkey-61.2.3 { execsql { PRAGMA foreign_keys = ON } } {} do_test e_fkey-61.3.1 { drop_all_tables execsql { CREATE TABLE p(a UNIQUE); CREATE TABLE c(b REFERENCES p(a) ON DELETE SET NULL); INSERT INTO p VALUES('x'); INSERT INTO c VALUES('x'); BEGIN; DROP TABLE p; SELECT * FROM c; ROLLBACK; } } {{}} do_test e_fkey-61.3.2 { execsql { PRAGMA foreign_keys = OFF; DROP TABLE p; SELECT * FROM c; } } {x} do_test e_fkey-61.3.3 { execsql { PRAGMA foreign_keys = ON } } {} ########################################################################### ### SECTION 6: Limits and Unsupported Features ########################################################################### #------------------------------------------------------------------------- # /* EV: R-24728-13230 */ # /* EV: R-24450-46174 */ # # Test that MATCH clauses are parsed, but SQLite treats every foreign key # constraint as if it were "MATCH SIMPLE". # foreach zMatch [list SIMPLE PARTIAL FULL Simple parTIAL FuLL ] { drop_all_tables do_test e_fkey-62.$zMatch.1 { execsql " CREATE TABLE p(a, b, c, PRIMARY KEY(b, c)); CREATE TABLE c(d, e, f, FOREIGN KEY(e, f) REFERENCES p MATCH $zMatch); " } {} do_test e_fkey-62.$zMatch.2 { execsql { INSERT INTO p VALUES(1, 2, 3) } # MATCH SIMPLE behaviour: Allow any child key that contains one or more # NULL value to be inserted. Non-NULL values do not have to map to any # parent key values, so long as at least one field of the child key is # NULL. execsql { INSERT INTO c VALUES('w', 2, 3) } |
︙ | ︙ | |||
2690 2691 2692 2693 2694 2695 2696 | #------------------------------------------------------------------------- # /* EV: R-21599-16038 */ # # Test that SQLite does not support the SET CONSTRAINT statement. And # that it is possible to create both immediate and deferred constraints. # drop_all_tables | | | | | | | | | 2690 2691 2692 2693 2694 2695 2696 2697 2698 2699 2700 2701 2702 2703 2704 2705 2706 2707 2708 2709 2710 2711 2712 2713 2714 2715 2716 2717 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 2730 | #------------------------------------------------------------------------- # /* EV: R-21599-16038 */ # # Test that SQLite does not support the SET CONSTRAINT statement. And # that it is possible to create both immediate and deferred constraints. # drop_all_tables do_test e_fkey-62.1 { catchsql { SET CONSTRAINTS ALL IMMEDIATE } } {1 {near "SET": syntax error}} do_test e_fkey-62.2 { catchsql { SET CONSTRAINTS ALL DEFERRED } } {1 {near "SET": syntax error}} do_test e_fkey-62.3 { execsql { CREATE TABLE p(a, b, PRIMARY KEY(a, b)); CREATE TABLE cd(c, d, FOREIGN KEY(c, d) REFERENCES p DEFERRABLE INITIALLY DEFERRED); CREATE TABLE ci(c, d, FOREIGN KEY(c, d) REFERENCES p DEFERRABLE INITIALLY IMMEDIATE); BEGIN; } } {} do_test e_fkey-62.4 { catchsql { INSERT INTO ci VALUES('x', 'y') } } {1 {foreign key constraint failed}} do_test e_fkey-62.5 { catchsql { INSERT INTO cd VALUES('x', 'y') } } {0 {}} do_test e_fkey-62.6 { catchsql { COMMIT } } {1 {foreign key constraint failed}} do_test e_fkey-62.7 { execsql { DELETE FROM cd; COMMIT; } } {} #------------------------------------------------------------------------- |
︙ | ︙ | |||
2773 2774 2775 2776 2777 2778 2779 | execsql COMMIT catchsql " UPDATE t0 SET a = 'yyy'; SELECT NOT (a='yyy') FROM t$limit; " } | | | | | | | | | | | | | | | 2773 2774 2775 2776 2777 2778 2779 2780 2781 2782 2783 2784 2785 2786 2787 2788 2789 2790 2791 2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 2804 2805 2806 2807 2808 2809 2810 2811 2812 2813 2814 2815 2816 2817 2818 2819 2820 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 2831 2832 2833 2834 2835 2836 2837 2838 2839 2840 2841 2842 2843 2844 2845 | execsql COMMIT catchsql " UPDATE t0 SET a = 'yyy'; SELECT NOT (a='yyy') FROM t$limit; " } do_test e_fkey-63.1.1 { test_on_delete_recursion $SQLITE_MAX_TRIGGER_DEPTH } {0 0} do_test e_fkey-63.1.2 { test_on_delete_recursion [expr $SQLITE_MAX_TRIGGER_DEPTH+1] } {1 {too many levels of trigger recursion}} do_test e_fkey-63.1.3 { sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 5 test_on_delete_recursion 5 } {0 0} do_test e_fkey-63.1.4 { test_on_delete_recursion 6 } {1 {too many levels of trigger recursion}} do_test e_fkey-63.1.5 { sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000 } {5} do_test e_fkey-63.2.1 { test_on_update_recursion $SQLITE_MAX_TRIGGER_DEPTH } {0 0} do_test e_fkey-63.2.2 { test_on_update_recursion [expr $SQLITE_MAX_TRIGGER_DEPTH+1] } {1 {too many levels of trigger recursion}} do_test e_fkey-63.2.3 { sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 5 test_on_update_recursion 5 } {0 0} do_test e_fkey-63.2.4 { test_on_update_recursion 6 } {1 {too many levels of trigger recursion}} do_test e_fkey-63.2.5 { sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000 } {5} #------------------------------------------------------------------------- # /* EV: R-51769-32730 */ # # The setting of the recursive_triggers pragma does not affect foreign # key actions. # foreach recursive_triggers_setting [list 0 1 ON OFF] { drop_all_tables execsql "PRAGMA recursive_triggers = $recursive_triggers_setting" do_test e_fkey-64.$recursive_triggers_setting.1 { execsql { CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1 ON DELETE CASCADE); INSERT INTO t1 VALUES(1, NULL); INSERT INTO t1 VALUES(2, 1); INSERT INTO t1 VALUES(3, 2); INSERT INTO t1 VALUES(4, 3); INSERT INTO t1 VALUES(5, 4); SELECT count(*) FROM t1; } } {5} do_test e_fkey-64.$recursive_triggers_setting.2 { execsql { SELECT count(*) FROM t1 WHERE a = 1 } } {1} do_test e_fkey-64.$recursive_triggers_setting.3 { execsql { DELETE FROM t1 WHERE a = 1; SELECT count(*) FROM t1; } } {0} } finish_test |
Changes to test/e_fts3.test.
︙ | ︙ | |||
18 19 20 21 22 23 24 | ifcapable !fts3 { finish_test return } source $testdir/fts3_common.tcl | < < > | > > > > > > | 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | ifcapable !fts3 { finish_test return } source $testdir/fts3_common.tcl # Procs used to make the tests in this file easier to read. # proc ddl_test {tn ddl} { uplevel [list do_write_test e_fts3-$tn sqlite_master $ddl] } proc write_test {tn tbl sql} { uplevel [list do_write_test e_fts3-$tn $tbl $sql] } proc read_test {tn sql result} { uplevel [list do_select_test e_fts3-$tn $sql $result] } foreach DO_MALLOC_TEST {0 1 2} { db close file delete -force test.db test.db-journal sqlite3 db test.db if {$DO_MALLOC_TEST} { sqlite3_db_config_lookaside db 0 0 0 } ########################################################################## # Test the example CREATE VIRTUAL TABLE statements in section 1.1 # of fts3.in. # ddl_test 1.1.1 {CREATE VIRTUAL TABLE data USING fts3()} read_test 1.1.2 {PRAGMA table_info(data)} {0 content {} 0 {} 0} ddl_test 1.2.1 { |
︙ | ︙ | |||
65 66 67 68 69 70 71 72 | # constraints). set largetext [string repeat "the quick brown fox " 5000] write_test 1.3.3 mail_content { INSERT INTO mail VALUES(NULL, $largetext) } read_test 1.3.4 { SELECT subject IS NULL, length(body) FROM mail } [list 1 100000] finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 | # constraints). set largetext [string repeat "the quick brown fox " 5000] write_test 1.3.3 mail_content { INSERT INTO mail VALUES(NULL, $largetext) } read_test 1.3.4 { SELECT subject IS NULL, length(body) FROM mail } [list 1 100000] ddl_test 1.4.1 { CREATE VIRTUAL TABLE papers USING fts3(author, document, tokenize=porter) } read_test 1.4.2 { PRAGMA table_info(papers) } {0 author {} 0 {} 0 1 document {} 0 {} 0} ddl_test 1.5.1 { CREATE VIRTUAL TABLE simpledata USING fts3(tokenize=simple) } read_test 1.5.2 { PRAGMA table_info(simpledata) } {0 content {} 0 {} 0} ddl_test 1.6.1 {DROP TABLE data} ddl_test 1.6.2 {DROP TABLE pages} ddl_test 1.6.3 {DROP TABLE mail} ddl_test 1.6.4 {DROP TABLE papers} ddl_test 1.6.5 {DROP TABLE simpledata} read_test 1.6.6 {SELECT * FROM sqlite_master} {} # The following is not one of the examples in section 1.1. It tests # specifying an FTS3 table with no module arguments using a slightly # different syntax. ddl_test 1.7.1 {CREATE VIRTUAL TABLE data USING fts3;} read_test 1.7.2 {PRAGMA table_info(data)} {0 content {} 0 {} 0} ddl_test 1.7.3 {DROP TABLE data} ########################################################################## } finish_test |
Changes to test/fts3_common.tcl.
︙ | ︙ | |||
374 375 376 377 378 379 380 | # Figure out an statement to get a checksum for table $tbl. db eval "SELECT * FROM $tbl" V break set cksumsql "SELECT md5sum([join [concat rowid $V(*)] ,]) FROM $tbl" # Calculate the initial table checksum. set cksum1 [db one $cksumsql] | < > | > > > | 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 | # Figure out an statement to get a checksum for table $tbl. db eval "SELECT * FROM $tbl" V break set cksumsql "SELECT md5sum([join [concat rowid $V(*)] ,]) FROM $tbl" # Calculate the initial table checksum. set cksum1 [db one $cksumsql] if {$::DO_MALLOC_TEST } { set answers [list {1 {out of memory}} {0 {}}] if {$::DO_MALLOC_TEST==1} { set modes {100000 transient} } else { set modes {1 persistent} } } else { set answers [list {0 {}}] set modes [list 0 nofail] } set str [join $answers " OR "] foreach {nRepeat zName} $modes { |
︙ | ︙ |