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