Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Modify some comments in e_fkey.test. No changes to code or tests. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
1ba4f412325fc2ce552ad7feecf93891 |
User & Date: | dan 2009-12-16 14:49:16.000 |
Context
2009-12-16
| ||
22:10 | Ensure WHERE clause terms involving tables on the right end of a join are not prematurely evaluated when tables on the left end of the join make use of the OR-clause optimization. Fix for ticket [31338dca7e]. (check-in: 2c2de25266 user: drh tags: trunk) | |
14:49 | Modify some comments in e_fkey.test. No changes to code or tests. (check-in: 1ba4f41232 user: dan tags: trunk) | |
2009-12-14
| ||
17:42 | Add a few simple evidence comments to the tokenizer. No functional changes. (check-in: 0daec3099d user: drh tags: trunk) | |
Changes
Changes to test/e_fkey.test.
︙ | ︙ | |||
27 28 29 30 31 32 33 | proc eqp {sql {db db}} { uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db } ########################################################################### ### SECTION 2: Enabling Foreign Key Support ########################################################################### #------------------------------------------------------------------------- | | | | < < < > > | > | > > > > > > > > > | 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 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | proc eqp {sql {db db}} { uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db } ########################################################################### ### SECTION 2: Enabling Foreign Key Support ########################################################################### #------------------------------------------------------------------------- # EVIDENCE-OF: R-33710-56344 In order to use foreign key constraints in # SQLite, the library must be compiled with neither # SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined. # ifcapable trigger&&foreignkey { do_test e_fkey-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; } } {world} } #------------------------------------------------------------------------- # Test the effects of defining OMIT_TRIGGER but not OMIT_FOREIGN_KEY. # # EVIDENCE-OF: R-44697-61543 If SQLITE_OMIT_TRIGGER is defined but # SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to # version 3.6.19 - foreign key definitions are parsed and may be queried # using PRAGMA foreign_key_list, but foreign key constraints are not # enforced. # # 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. # # EVIDENCE-OF: R-22567-44039 The PRAGMA foreign_keys command is a no-op # in this configuration. # # EVIDENCE-OF: R-41784-13339 Tip: If the command "PRAGMA foreign_keys" # returns no data instead of a single row containing "0" or "1", then # the version of SQLite you are using does not support foreign keys # (either because it is older than 3.6.19 or because it was compiled # with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined). # reset_db ifcapable !trigger&&foreignkey { do_test e_fkey-2.1 { execsql { PRAGMA foreign_keys = ON; CREATE TABLE p(i PRIMARY KEY); |
︙ | ︙ | |||
80 81 82 83 84 85 86 | do_test e_fkey-2.3 { execsql { PRAGMA foreign_keys } } {} } #------------------------------------------------------------------------- | | | > > | 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 | do_test e_fkey-2.3 { execsql { PRAGMA foreign_keys } } {} } #------------------------------------------------------------------------- # Test the effects of defining OMIT_FOREIGN_KEY. # # EVIDENCE-OF: R-58428-36660 If OMIT_FOREIGN_KEY is defined, then # foreign key definitions cannot even be parsed (attempting to specify a # foreign key definition is a syntax error). # # /* EV: R-58428-36660 */ # # Specifically, test that foreign key constraints cannot even be parsed # in such a build. # reset_db |
︙ | ︙ | |||
116 117 118 119 120 121 122 | } ifcapable !foreignkey||!trigger { finish_test ; return } reset_db #------------------------------------------------------------------------- | | < | | < > | > > | 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 | } ifcapable !foreignkey||!trigger { finish_test ; return } reset_db #------------------------------------------------------------------------- # EVIDENCE-OF: R-07280-60510 Assuming the library is compiled with # foreign key constraints enabled, it must still be enabled by the # application at runtime, using the PRAGMA foreign_keys command. # # This also tests that foreign key constraints are disabled by default. # # EVIDENCE-OF: R-59578-04990 Foreign key constraints are disabled by # default (for backwards compatibility), so must be enabled separately # for each database connection separately. # 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'); |
︙ | ︙ | |||
149 150 151 152 153 154 155 | INSERT INTO c VALUES('hello'); UPDATE p SET i = 'world'; SELECT * FROM c; } } {world} #------------------------------------------------------------------------- | | > | < < | > > < < > > > > > | 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 | INSERT INTO c VALUES('hello'); UPDATE p SET i = 'world'; SELECT * FROM c; } } {world} #------------------------------------------------------------------------- # EVIDENCE-OF: R-15278-54456 The application can can also use a PRAGMA # foreign_keys statement to determine if foreign keys are currently # enabled. # # This also tests the example code in section 2 of foreignkeys.in. # # EVIDENCE-OF: R-11255-19907 # 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} #------------------------------------------------------------------------- # Test that it is not possible to enable or disable foreign key support # while not in auto-commit mode. # # EVIDENCE-OF: R-46649-58537 It is not possible to enable or disable # foreign key constraints in the middle of a multi-statement transaction # (when SQLite is not in autocommit mode). Attempting to do so does not # return an error; it simply has no effect. # 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)); |
︙ | ︙ | |||
217 218 219 220 221 222 223 | ########################################################################### ### SECTION 1: Introduction to Foreign Key Constraints ########################################################################### execsql "PRAGMA foreign_keys = ON" #------------------------------------------------------------------------- | | | > > > > < < | > | | > | | < < > > > > | 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 | ########################################################################### ### SECTION 1: Introduction to Foreign Key Constraints ########################################################################### execsql "PRAGMA foreign_keys = ON" #------------------------------------------------------------------------- # Verify that the syntax in the first example in section 1 is valid. # # EVIDENCE-OF: R-04042-24825 To do so, a foreign key definition may be # added by modifying the declaration of the track table to the # following: CREATE TABLE track( trackid INTEGER, trackname TEXT, # trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES # artist(artistid) ); # 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) ); } } {} #------------------------------------------------------------------------- # EVIDENCE-OF: R-61362-32087 Attempting to insert a row into the track # table that does not correspond to any row in the artist table will # fail, # 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) } } {} #------------------------------------------------------------------------- # Attempting to delete a row from the 'artist' table while there are # dependent rows in the track table also fails. # # EVIDENCE-OF: R-24401-52400 as will attempting to delete a row from the # artist table when there exist dependent rows in the track table # 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; } } {} #------------------------------------------------------------------------- # 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. # # EVIDENCE-OF: R-23980-48859 There is one exception: if the foreign key # column in the track table is NULL, then no corresponding entry in the # artist table is required. # do_test e_fkey-10.1 { execsql { INSERT INTO track VALUES(1, 'track 1', NULL); INSERT INTO track VALUES(2, 'track 2', NULL); } } {} |
︙ | ︙ | |||
310 311 312 313 314 315 316 317 318 319 320 321 322 323 | # /* EV: R-52486-21352 */ # # Test that the following is true fo all rows in the track table: # # trackartist IS NULL OR # EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist) # # This procedure executes a test case to check that statement # R-52486-21352 is true after executing the SQL statement passed. # as the second argument. proc test_r52486_21352 {tn sql} { set res [catchsql $sql] set results { | > > > > | 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 | # /* EV: R-52486-21352 */ # # Test that the following is true fo all rows in the track table: # # trackartist IS NULL OR # EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist) # # EVIDENCE-OF: R-52486-21352 Expressed in SQL, this means that for every # row in the track table, the following expression evaluates to true: # trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE # artistid=trackartist) # This procedure executes a test case to check that statement # R-52486-21352 is true after executing the SQL statement passed. # as the second argument. proc test_r52486_21352 {tn sql} { set res [catchsql $sql] set results { |
︙ | ︙ | |||
363 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 | #------------------------------------------------------------------------- # /* 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}} #------------------------------------------------------------------------- | > > > > > | | 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 | #------------------------------------------------------------------------- # /* 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. # # EVIDENCE-OF: R-42412-59321 Tip: If the application requires a stricter # relationship between artist and track, where NULL values are not # permitted in the trackartist column, simply add the appropriate "NOT # NULL" constraint to the schema. # 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}} #------------------------------------------------------------------------- # EVIDENCE-OF: R-17902-59250 # # Test an example from foreignkeys.html. # drop_all_tables do_test e_fkey-13.1 { execsql { CREATE TABLE artist( |
︙ | ︙ | |||
427 428 429 430 431 432 433 | 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); } } {} #------------------------------------------------------------------------- | | | 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 | 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); } } {} #------------------------------------------------------------------------- # EVIDENCE-OF: 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'; } |
︙ | ︙ | |||
456 457 458 459 460 461 462 | DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues'); UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; } } {} #------------------------------------------------------------------------- | | < | | | | < < | > > > | 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 | DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues'); UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; } } {} #------------------------------------------------------------------------- # EVIDENCE-OF: R-56032-24923 The foreign key constraint is satisfied if # for each row in the child table either one or more of the child key # columns are NULL, or there exists a row in the parent table for which # each parent key column contains a value equal to the value in its # associated child key column. # # Test also that the usual comparison rules are used when testing if there # is a matching row in the parent table of a foreign key constraint. # # EVIDENCE-OF: R-57765-12380 In the above paragraph, the term "equal" # means equal when values are compared using the rules specified here. # 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); |
︙ | ︙ | |||
504 505 506 507 508 509 510 | test_efkey_45 5 0 "DELETE FROM chi WHERE c = '1'" test_efkey_45 6 0 "DELETE FROM par WHERE p = '1'" test_efkey_45 7 1 "INSERT INTO chi VALUES('1')" test_efkey_45 8 0 "INSERT INTO chi VALUES(X'31')" test_efkey_45 9 1 "INSERT INTO chi VALUES(X'32')" #------------------------------------------------------------------------- | < < > > > | 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 | test_efkey_45 5 0 "DELETE FROM chi WHERE c = '1'" test_efkey_45 6 0 "DELETE FROM par WHERE p = '1'" test_efkey_45 7 1 "INSERT INTO chi VALUES('1')" test_efkey_45 8 0 "INSERT INTO chi VALUES(X'31')" test_efkey_45 9 1 "INSERT INTO chi VALUES(X'32')" #------------------------------------------------------------------------- # Specifically, test that when comparing child and parent key values the # default collation sequence of the parent key column is used. # # EVIDENCE-OF: R-15796-47513 When comparing text values, the collating # sequence associated with the parent key column is always 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); } |
︙ | ︙ | |||
533 534 535 536 537 538 539 | 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}} #------------------------------------------------------------------------- | < < > > > > | 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 | 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}} #------------------------------------------------------------------------- # 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. # # EVIDENCE-OF: R-04240-13860 When comparing values, if the parent key # column has an affinity, then that affinity is applied to the child key # value before the comparison is performed. # drop_all_tables do_test e_fkey-17.1 { execsql { CREATE TABLE t1(a NUMERIC PRIMARY KEY); CREATE TABLE t2(b TEXT REFERENCES t1); } |
︙ | ︙ | |||
567 568 569 570 571 572 573 | } {1 {foreign key constraint failed}} ########################################################################### ### SECTION 3: Required and Suggested Database Indexes ########################################################################### #------------------------------------------------------------------------- | < < | > > | > | > > > > | 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 | } {1 {foreign key constraint failed}} ########################################################################### ### SECTION 3: Required and Suggested Database Indexes ########################################################################### #------------------------------------------------------------------------- # A parent key must be either a PRIMARY KEY, subject to a UNIQUE # constraint, or have a UNIQUE index created on it. # # EVIDENCE-OF: R-13435-26311 Usually, the parent key of a foreign key # constraint is the primary key of the parent table. If they are not the # primary key, then the parent key columns must be collectively subject # to a UNIQUE constraint or have a UNIQUE index. # # 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. # # EVIDENCE-OF: R-00376-39212 If the parent key columns have a UNIQUE # index, then that index must use the collation sequences that are # specified in the CREATE TABLE statement for the parent table. # drop_all_tables do_test e_fkey-18.1 { execsql { CREATE TABLE t2(a REFERENCES t1(x)); } } {} |
︙ | ︙ | |||
612 613 614 615 616 617 618 | } #------------------------------------------------------------------------- # This block tests an example in foreignkeys.html. Several testable # statements refer to this example, as follows # | | < < | > > | | | > > | > > > > | 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 | } #------------------------------------------------------------------------- # This block tests an example in foreignkeys.html. Several testable # statements refer to this example, as follows # # EVIDENCE-OF: R-27484-01467 # # FK Constraints on child1, child2 and child3 are Ok. # # Problem with FK on child4: # # EVIDENCE-OF: R-51039-44840 The foreign key declared as part of table # child4 is an error because even though the parent key column is # indexed, the index is not UNIQUE. # # Problem with FK on child5: # # EVIDENCE-OF: R-01060-48788 The foreign key for table child5 is an # error because even though the parent key column has a unique index, # the index uses a different collating sequence. # # Problem with FK on child6 and child7: # # EVIDENCE-OF: R-63088-37469 Tables child6 and child7 are incorrect # because while both have UNIQUE indices on their parent keys, the keys # are not an exact match to the columns of a single UNIQUE index. # 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); |
︙ | ︙ | |||
667 668 669 670 671 672 673 | 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}} #------------------------------------------------------------------------- | < < < < < > > > > > > > > > > > > > > > > > > > > > > > | 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 | 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}} #------------------------------------------------------------------------- # 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]). # # EVIDENCE-OF: R-45488-08504 If the database schema contains foreign key # errors that require looking at more than one table definition to # identify, then those errors are not detected when the tables are # created. # # EVIDENCE-OF: R-48391-38472 Instead, such errors prevent the # application from preparing SQL statements that modify the content of # the child or parent tables in ways that use the foreign keys. # # EVIDENCE-OF: R-03108-63659 The English language error message for # foreign key DML errors is usually "foreign key mismatch" but can also # be "no such table" if the parent table does not exist. # # EVIDENCE-OF: R-60781-26576 Foreign key DML errors are may be reported # if: The parent table does not exist, or The parent key columns named # in the foreign key constraint do not exist, or The parent key columns # named in the foreign key constraint are not the primary key of the # parent table and are not subject to a unique constraint using # collating sequence specified in the CREATE TABLE, or The child table # references the primary key of the parent without specifying the # primary key columns and the number of primary key columns in the # parent do not match the number of child key columns. # 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)); |
︙ | ︙ | |||
735 736 737 738 739 740 741 | do_test e_fkey-20.$tn.6 { catchsql "INSERT INTO $ptbl SELECT ?, ?" } [list 1 $err] } } #------------------------------------------------------------------------- | | | 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 | do_test e_fkey-20.$tn.6 { catchsql "INSERT INTO $ptbl SELECT ?, ?" } [list 1 $err] } } #------------------------------------------------------------------------- # EVIDENCE-OF: 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 { |
︙ | ︙ | |||
777 778 779 780 781 782 783 | 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}} #------------------------------------------------------------------------- | < < | > > > > > > > | 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 | 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}} #------------------------------------------------------------------------- # Test errors that are reported when creating the child table. # Specifically: # # * different number of child and parent key columns, and # * child columns that do not exist. # # EVIDENCE-OF: R-23682-59820 By contrast, if foreign key errors can be # recognized simply by looking at the definition of the child table and # without having to consult the parent table definition, then the CREATE # TABLE statement for the child table fails. # # These errors are reported whether or not FK support is enabled. # # EVIDENCE-OF: R-33883-28833 Foreign key DDL errors are reported # regardless of whether or not foreign key constraints are enabled when # the table is created. # drop_all_tables foreach fk [list OFF ON] { execsql "PRAGMA foreign_keys = $fk" set i 0 foreach {sql error} { "CREATE TABLE child1(a, b, FOREIGN KEY(a, b) REFERENCES p(c))" |
︙ | ︙ | |||
837 838 839 840 841 842 843 | test_efkey_60 3 0 "INSERT INTO p1 VALUES(239, 231)" test_efkey_60 4 0 "INSERT INTO c1 VALUES(239, 231)" test_efkey_60 5 1 "INSERT INTO c2 VALUES(239, 231)" test_efkey_60 6 0 "INSERT INTO p2 VALUES(239, 231)" test_efkey_60 7 0 "INSERT INTO c2 VALUES(239, 231)" #------------------------------------------------------------------------- | < < > > > | 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 | test_efkey_60 3 0 "INSERT INTO p1 VALUES(239, 231)" test_efkey_60 4 0 "INSERT INTO c1 VALUES(239, 231)" test_efkey_60 5 1 "INSERT INTO c2 VALUES(239, 231)" test_efkey_60 6 0 "INSERT INTO p2 VALUES(239, 231)" test_efkey_60 7 0 "INSERT INTO c2 VALUES(239, 231)" #------------------------------------------------------------------------- # Test that an index on on the child key columns of an FK constraint # is optional. # # EVIDENCE-OF: R-15417-28014 Indices are not required for child key # columns # # /* 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 |
︙ | ︙ | |||
872 873 874 875 876 877 878 | test_efkey_61 $tn.2 0 "INSERT INTO parent VALUES(1, 2)" test_efkey_61 $tn.3 0 "INSERT INTO $c VALUES(1, 2)" execsql "DELETE FROM $c ; DELETE FROM parent" } #------------------------------------------------------------------------- | | | | < | | 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 | test_efkey_61 $tn.2 0 "INSERT INTO parent VALUES(1, 2)" test_efkey_61 $tn.3 0 "INSERT INTO $c VALUES(1, 2)" execsql "DELETE FROM $c ; DELETE FROM parent" } #------------------------------------------------------------------------- # EVIDENCE-OF: R-00279-52283 # # Test an example showing that when a row is deleted from the parent # table, the child table is queried for orphaned rows as follows: # # SELECT rowid FROM track WHERE trackartist = ? # # EVIDENCE-OF: R-23302-30956 If this SELECT returns any rows at all, # then SQLite concludes that deleting the row from the parent table # would violate the foreign key constraint and returns an error. # do_test e_fkey-25.1 { execsql { CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); |
︙ | ︙ | |||
940 941 942 943 944 945 946 | 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}} #------------------------------------------------------------------------- | | > > > > | 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 | 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}} #------------------------------------------------------------------------- # EVIDENCE-OF: R-54172-55848 # # Test that when a row is deleted from the parent table of an FK # constraint, the child table is queried for orphaned rows. The # query is equivalent to: # # SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value # # /* EV: R-61616-46700 */ # # Also test that when a row is inserted into the parent table, or when the # parent key values of an existing row are modified, a query equivalent # 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 # # EVIDENCE-OF: R-61616-46700 Similar queries may be run if the content # of the parent key is modified or a new row is inserted into the parent # table. # # drop_all_tables do_test e_fkey-26.1 { execsql { CREATE TABLE parent(x, y, UNIQUE(y, x)) } } {} foreach {tn sql} { |
︙ | ︙ |