Annotation of embedaddon/sqlite3/test/fkey2.test, revision 1.1
1.1 ! misho 1: # 2009 September 15
! 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: # This file implements regression tests for SQLite library.
! 12: #
! 13: # This file implements tests for foreign keys.
! 14: #
! 15:
! 16: set testdir [file dirname $argv0]
! 17: source $testdir/tester.tcl
! 18:
! 19: ifcapable {!foreignkey||!trigger} {
! 20: finish_test
! 21: return
! 22: }
! 23:
! 24: #-------------------------------------------------------------------------
! 25: # Test structure:
! 26: #
! 27: # fkey2-1.*: Simple tests to check that immediate and deferred foreign key
! 28: # constraints work when not inside a transaction.
! 29: #
! 30: # fkey2-2.*: Tests to verify that deferred foreign keys work inside
! 31: # explicit transactions (i.e that processing really is deferred).
! 32: #
! 33: # fkey2-3.*: Tests that a statement transaction is rolled back if an
! 34: # immediate foreign key constraint is violated.
! 35: #
! 36: # fkey2-4.*: Test that FK actions may recurse even when recursive triggers
! 37: # are disabled.
! 38: #
! 39: # fkey2-5.*: Check that if foreign-keys are enabled, it is not possible
! 40: # to write to an FK column using the incremental blob API.
! 41: #
! 42: # fkey2-6.*: Test that FK processing is automatically disabled when
! 43: # running VACUUM.
! 44: #
! 45: # fkey2-7.*: Test using an IPK as the key in the child (referencing) table.
! 46: #
! 47: # fkey2-8.*: Test that enabling/disabling foreign key support while a
! 48: # transaction is active is not possible.
! 49: #
! 50: # fkey2-9.*: Test SET DEFAULT actions.
! 51: #
! 52: # fkey2-10.*: Test errors.
! 53: #
! 54: # fkey2-11.*: Test CASCADE actions.
! 55: #
! 56: # fkey2-12.*: Test RESTRICT actions.
! 57: #
! 58: # fkey2-13.*: Test that FK processing is performed when a row is REPLACED by
! 59: # an UPDATE or INSERT statement.
! 60: #
! 61: # fkey2-14.*: Test the ALTER TABLE and DROP TABLE commands.
! 62: #
! 63: # fkey2-15.*: Test that if there are no (known) outstanding foreign key
! 64: # constraint violations in the database, inserting into a parent
! 65: # table or deleting from a child table does not cause SQLite
! 66: # to check if this has repaired an outstanding violation.
! 67: #
! 68: # fkey2-16.*: Test that rows that refer to themselves may be inserted,
! 69: # updated and deleted.
! 70: #
! 71: # fkey2-17.*: Test that the "count_changes" pragma does not interfere with
! 72: # FK constraint processing.
! 73: #
! 74: # fkey2-18.*: Test that the authorization callback is invoked when processing
! 75: # FK constraints.
! 76: #
! 77: # fkey2-20.*: Test that ON CONFLICT clauses specified as part of statements
! 78: # do not affect the operation of FK constraints.
! 79: #
! 80: # fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
! 81: # command. Recycled to test the built-in implementation.
! 82: #
! 83: # fkey2-dd08e5.*: Tests to verify that ticket dd08e5a988d00decc4a543daa8d
! 84: # has been fixed.
! 85: #
! 86:
! 87:
! 88: execsql { PRAGMA foreign_keys = on }
! 89:
! 90: set FkeySimpleSchema {
! 91: PRAGMA foreign_keys = on;
! 92: CREATE TABLE t1(a PRIMARY KEY, b);
! 93: CREATE TABLE t2(c REFERENCES t1(a) /D/ , d);
! 94:
! 95: CREATE TABLE t3(a PRIMARY KEY, b);
! 96: CREATE TABLE t4(c REFERENCES t3 /D/, d);
! 97:
! 98: CREATE TABLE t7(a, b INTEGER PRIMARY KEY);
! 99: CREATE TABLE t8(c REFERENCES t7 /D/, d);
! 100:
! 101: CREATE TABLE t9(a REFERENCES nosuchtable, b);
! 102: CREATE TABLE t10(a REFERENCES t9(c) /D/, b);
! 103: }
! 104:
! 105:
! 106: set FkeySimpleTests {
! 107: 1.1 "INSERT INTO t2 VALUES(1, 3)" {1 {foreign key constraint failed}}
! 108: 1.2 "INSERT INTO t1 VALUES(1, 2)" {0 {}}
! 109: 1.3 "INSERT INTO t2 VALUES(1, 3)" {0 {}}
! 110: 1.4 "INSERT INTO t2 VALUES(2, 4)" {1 {foreign key constraint failed}}
! 111: 1.5 "INSERT INTO t2 VALUES(NULL, 4)" {0 {}}
! 112: 1.6 "UPDATE t2 SET c=2 WHERE d=4" {1 {foreign key constraint failed}}
! 113: 1.7 "UPDATE t2 SET c=1 WHERE d=4" {0 {}}
! 114: 1.9 "UPDATE t2 SET c=1 WHERE d=4" {0 {}}
! 115: 1.10 "UPDATE t2 SET c=NULL WHERE d=4" {0 {}}
! 116: 1.11 "DELETE FROM t1 WHERE a=1" {1 {foreign key constraint failed}}
! 117: 1.12 "UPDATE t1 SET a = 2" {1 {foreign key constraint failed}}
! 118: 1.13 "UPDATE t1 SET a = 1" {0 {}}
! 119:
! 120: 2.1 "INSERT INTO t4 VALUES(1, 3)" {1 {foreign key constraint failed}}
! 121: 2.2 "INSERT INTO t3 VALUES(1, 2)" {0 {}}
! 122: 2.3 "INSERT INTO t4 VALUES(1, 3)" {0 {}}
! 123:
! 124: 4.1 "INSERT INTO t8 VALUES(1, 3)" {1 {foreign key constraint failed}}
! 125: 4.2 "INSERT INTO t7 VALUES(2, 1)" {0 {}}
! 126: 4.3 "INSERT INTO t8 VALUES(1, 3)" {0 {}}
! 127: 4.4 "INSERT INTO t8 VALUES(2, 4)" {1 {foreign key constraint failed}}
! 128: 4.5 "INSERT INTO t8 VALUES(NULL, 4)" {0 {}}
! 129: 4.6 "UPDATE t8 SET c=2 WHERE d=4" {1 {foreign key constraint failed}}
! 130: 4.7 "UPDATE t8 SET c=1 WHERE d=4" {0 {}}
! 131: 4.9 "UPDATE t8 SET c=1 WHERE d=4" {0 {}}
! 132: 4.10 "UPDATE t8 SET c=NULL WHERE d=4" {0 {}}
! 133: 4.11 "DELETE FROM t7 WHERE b=1" {1 {foreign key constraint failed}}
! 134: 4.12 "UPDATE t7 SET b = 2" {1 {foreign key constraint failed}}
! 135: 4.13 "UPDATE t7 SET b = 1" {0 {}}
! 136: 4.14 "INSERT INTO t8 VALUES('a', 'b')" {1 {foreign key constraint failed}}
! 137: 4.15 "UPDATE t7 SET b = 5" {1 {foreign key constraint failed}}
! 138: 4.16 "UPDATE t7 SET rowid = 5" {1 {foreign key constraint failed}}
! 139: 4.17 "UPDATE t7 SET a = 10" {0 {}}
! 140:
! 141: 5.1 "INSERT INTO t9 VALUES(1, 3)" {1 {no such table: main.nosuchtable}}
! 142: 5.2 "INSERT INTO t10 VALUES(1, 3)" {1 {foreign key mismatch}}
! 143: }
! 144:
! 145: do_test fkey2-1.1.0 {
! 146: execsql [string map {/D/ {}} $FkeySimpleSchema]
! 147: } {}
! 148: foreach {tn zSql res} $FkeySimpleTests {
! 149: do_test fkey2-1.1.$tn { catchsql $zSql } $res
! 150: }
! 151: drop_all_tables
! 152:
! 153: do_test fkey2-1.2.0 {
! 154: execsql [string map {/D/ {DEFERRABLE INITIALLY DEFERRED}} $FkeySimpleSchema]
! 155: } {}
! 156: foreach {tn zSql res} $FkeySimpleTests {
! 157: do_test fkey2-1.2.$tn { catchsql $zSql } $res
! 158: }
! 159: drop_all_tables
! 160:
! 161: do_test fkey2-1.3.0 {
! 162: execsql [string map {/D/ {}} $FkeySimpleSchema]
! 163: execsql { PRAGMA count_changes = 1 }
! 164: } {}
! 165: foreach {tn zSql res} $FkeySimpleTests {
! 166: if {$res == "0 {}"} { set res {0 1} }
! 167: do_test fkey2-1.3.$tn { catchsql $zSql } $res
! 168: }
! 169: execsql { PRAGMA count_changes = 0 }
! 170: drop_all_tables
! 171:
! 172: do_test fkey2-1.4.0 {
! 173: execsql [string map {/D/ {}} $FkeySimpleSchema]
! 174: execsql { PRAGMA count_changes = 1 }
! 175: } {}
! 176: foreach {tn zSql res} $FkeySimpleTests {
! 177: if {$res == "0 {}"} { set res {0 1} }
! 178: execsql BEGIN
! 179: do_test fkey2-1.4.$tn { catchsql $zSql } $res
! 180: execsql COMMIT
! 181: }
! 182: execsql { PRAGMA count_changes = 0 }
! 183: drop_all_tables
! 184:
! 185: # Special test: When the parent key is an IPK, make sure the affinity of
! 186: # the IPK is not applied to the child key value before it is inserted
! 187: # into the child table.
! 188: do_test fkey2-1.5.1 {
! 189: execsql {
! 190: CREATE TABLE i(i INTEGER PRIMARY KEY);
! 191: CREATE TABLE j(j REFERENCES i);
! 192: INSERT INTO i VALUES(35);
! 193: INSERT INTO j VALUES('35.0');
! 194: SELECT j, typeof(j) FROM j;
! 195: }
! 196: } {35.0 text}
! 197: do_test fkey2-1.5.2 {
! 198: catchsql { DELETE FROM i }
! 199: } {1 {foreign key constraint failed}}
! 200:
! 201: # Same test using a regular primary key with integer affinity.
! 202: drop_all_tables
! 203: do_test fkey2-1.6.1 {
! 204: execsql {
! 205: CREATE TABLE i(i INT UNIQUE);
! 206: CREATE TABLE j(j REFERENCES i(i));
! 207: INSERT INTO i VALUES('35.0');
! 208: INSERT INTO j VALUES('35.0');
! 209: SELECT j, typeof(j) FROM j;
! 210: SELECT i, typeof(i) FROM i;
! 211: }
! 212: } {35.0 text 35 integer}
! 213: do_test fkey2-1.6.2 {
! 214: catchsql { DELETE FROM i }
! 215: } {1 {foreign key constraint failed}}
! 216:
! 217: # Use a collation sequence on the parent key.
! 218: drop_all_tables
! 219: do_test fkey2-1.7.1 {
! 220: execsql {
! 221: CREATE TABLE i(i TEXT COLLATE nocase PRIMARY KEY);
! 222: CREATE TABLE j(j TEXT COLLATE binary REFERENCES i(i));
! 223: INSERT INTO i VALUES('SQLite');
! 224: INSERT INTO j VALUES('sqlite');
! 225: }
! 226: catchsql { DELETE FROM i }
! 227: } {1 {foreign key constraint failed}}
! 228:
! 229: # Use the parent key collation even if it is default and the child key
! 230: # has an explicit value.
! 231: drop_all_tables
! 232: do_test fkey2-1.7.2 {
! 233: execsql {
! 234: CREATE TABLE i(i TEXT PRIMARY KEY); -- Colseq is "BINARY"
! 235: CREATE TABLE j(j TEXT COLLATE nocase REFERENCES i(i));
! 236: INSERT INTO i VALUES('SQLite');
! 237: }
! 238: catchsql { INSERT INTO j VALUES('sqlite') }
! 239: } {1 {foreign key constraint failed}}
! 240: do_test fkey2-1.7.3 {
! 241: execsql {
! 242: INSERT INTO i VALUES('sqlite');
! 243: INSERT INTO j VALUES('sqlite');
! 244: DELETE FROM i WHERE i = 'SQLite';
! 245: }
! 246: catchsql { DELETE FROM i WHERE i = 'sqlite' }
! 247: } {1 {foreign key constraint failed}}
! 248:
! 249: #-------------------------------------------------------------------------
! 250: # This section (test cases fkey2-2.*) contains tests to check that the
! 251: # deferred foreign key constraint logic works.
! 252: #
! 253: proc fkey2-2-test {tn nocommit sql {res {}}} {
! 254: if {$res eq "FKV"} {
! 255: set expected {1 {foreign key constraint failed}}
! 256: } else {
! 257: set expected [list 0 $res]
! 258: }
! 259: do_test fkey2-2.$tn [list catchsql $sql] $expected
! 260: if {$nocommit} {
! 261: do_test fkey2-2.${tn}c {
! 262: catchsql COMMIT
! 263: } {1 {foreign key constraint failed}}
! 264: }
! 265: }
! 266:
! 267: fkey2-2-test 1 0 {
! 268: CREATE TABLE node(
! 269: nodeid PRIMARY KEY,
! 270: parent REFERENCES node DEFERRABLE INITIALLY DEFERRED
! 271: );
! 272: CREATE TABLE leaf(
! 273: cellid PRIMARY KEY,
! 274: parent REFERENCES node DEFERRABLE INITIALLY DEFERRED
! 275: );
! 276: }
! 277:
! 278: fkey2-2-test 1 0 "INSERT INTO node VALUES(1, 0)" FKV
! 279: fkey2-2-test 2 0 "BEGIN"
! 280: fkey2-2-test 3 1 "INSERT INTO node VALUES(1, 0)"
! 281: fkey2-2-test 4 0 "UPDATE node SET parent = NULL"
! 282: fkey2-2-test 5 0 "COMMIT"
! 283: fkey2-2-test 6 0 "SELECT * FROM node" {1 {}}
! 284:
! 285: fkey2-2-test 7 0 "BEGIN"
! 286: fkey2-2-test 8 1 "INSERT INTO leaf VALUES('a', 2)"
! 287: fkey2-2-test 9 1 "INSERT INTO node VALUES(2, 0)"
! 288: fkey2-2-test 10 0 "UPDATE node SET parent = 1 WHERE nodeid = 2"
! 289: fkey2-2-test 11 0 "COMMIT"
! 290: fkey2-2-test 12 0 "SELECT * FROM node" {1 {} 2 1}
! 291: fkey2-2-test 13 0 "SELECT * FROM leaf" {a 2}
! 292:
! 293: fkey2-2-test 14 0 "BEGIN"
! 294: fkey2-2-test 15 1 "DELETE FROM node WHERE nodeid = 2"
! 295: fkey2-2-test 16 0 "INSERT INTO node VALUES(2, NULL)"
! 296: fkey2-2-test 17 0 "COMMIT"
! 297: fkey2-2-test 18 0 "SELECT * FROM node" {1 {} 2 {}}
! 298: fkey2-2-test 19 0 "SELECT * FROM leaf" {a 2}
! 299:
! 300: fkey2-2-test 20 0 "BEGIN"
! 301: fkey2-2-test 21 0 "INSERT INTO leaf VALUES('b', 1)"
! 302: fkey2-2-test 22 0 "SAVEPOINT save"
! 303: fkey2-2-test 23 0 "DELETE FROM node WHERE nodeid = 1"
! 304: fkey2-2-test 24 0 "ROLLBACK TO save"
! 305: fkey2-2-test 25 0 "COMMIT"
! 306: fkey2-2-test 26 0 "SELECT * FROM node" {1 {} 2 {}}
! 307: fkey2-2-test 27 0 "SELECT * FROM leaf" {a 2 b 1}
! 308:
! 309: fkey2-2-test 28 0 "BEGIN"
! 310: fkey2-2-test 29 0 "INSERT INTO leaf VALUES('c', 1)"
! 311: fkey2-2-test 30 0 "SAVEPOINT save"
! 312: fkey2-2-test 31 0 "DELETE FROM node WHERE nodeid = 1"
! 313: fkey2-2-test 32 1 "RELEASE save"
! 314: fkey2-2-test 33 1 "DELETE FROM leaf WHERE cellid = 'b'"
! 315: fkey2-2-test 34 0 "DELETE FROM leaf WHERE cellid = 'c'"
! 316: fkey2-2-test 35 0 "COMMIT"
! 317: fkey2-2-test 36 0 "SELECT * FROM node" {2 {}}
! 318: fkey2-2-test 37 0 "SELECT * FROM leaf" {a 2}
! 319:
! 320: fkey2-2-test 38 0 "SAVEPOINT outer"
! 321: fkey2-2-test 39 1 "INSERT INTO leaf VALUES('d', 3)"
! 322: fkey2-2-test 40 1 "RELEASE outer" FKV
! 323: fkey2-2-test 41 1 "INSERT INTO leaf VALUES('e', 3)"
! 324: fkey2-2-test 42 0 "INSERT INTO node VALUES(3, 2)"
! 325: fkey2-2-test 43 0 "RELEASE outer"
! 326:
! 327: fkey2-2-test 44 0 "SAVEPOINT outer"
! 328: fkey2-2-test 45 1 "DELETE FROM node WHERE nodeid=3"
! 329: fkey2-2-test 47 0 "INSERT INTO node VALUES(3, 2)"
! 330: fkey2-2-test 48 0 "ROLLBACK TO outer"
! 331: fkey2-2-test 49 0 "RELEASE outer"
! 332:
! 333: fkey2-2-test 50 0 "SAVEPOINT outer"
! 334: fkey2-2-test 51 1 "INSERT INTO leaf VALUES('f', 4)"
! 335: fkey2-2-test 52 1 "SAVEPOINT inner"
! 336: fkey2-2-test 53 1 "INSERT INTO leaf VALUES('g', 4)"
! 337: fkey2-2-test 54 1 "RELEASE outer" FKV
! 338: fkey2-2-test 55 1 "ROLLBACK TO inner"
! 339: fkey2-2-test 56 0 "COMMIT" FKV
! 340: fkey2-2-test 57 0 "INSERT INTO node VALUES(4, NULL)"
! 341: fkey2-2-test 58 0 "RELEASE outer"
! 342: fkey2-2-test 59 0 "SELECT * FROM node" {2 {} 3 2 4 {}}
! 343: fkey2-2-test 60 0 "SELECT * FROM leaf" {a 2 d 3 e 3 f 4}
! 344:
! 345: # The following set of tests check that if a statement that affects
! 346: # multiple rows violates some foreign key constraints, then strikes a
! 347: # constraint that causes the statement-transaction to be rolled back,
! 348: # the deferred constraint counter is correctly reset to the value it
! 349: # had before the statement-transaction was opened.
! 350: #
! 351: fkey2-2-test 61 0 "BEGIN"
! 352: fkey2-2-test 62 0 "DELETE FROM leaf"
! 353: fkey2-2-test 63 0 "DELETE FROM node"
! 354: fkey2-2-test 64 1 "INSERT INTO leaf VALUES('a', 1)"
! 355: fkey2-2-test 65 1 "INSERT INTO leaf VALUES('b', 2)"
! 356: fkey2-2-test 66 1 "INSERT INTO leaf VALUES('c', 1)"
! 357: do_test fkey2-2-test-67 {
! 358: catchsql "INSERT INTO node SELECT parent, 3 FROM leaf"
! 359: } {1 {column nodeid is not unique}}
! 360: fkey2-2-test 68 0 "COMMIT" FKV
! 361: fkey2-2-test 69 1 "INSERT INTO node VALUES(1, NULL)"
! 362: fkey2-2-test 70 0 "INSERT INTO node VALUES(2, NULL)"
! 363: fkey2-2-test 71 0 "COMMIT"
! 364:
! 365: fkey2-2-test 72 0 "BEGIN"
! 366: fkey2-2-test 73 1 "DELETE FROM node"
! 367: fkey2-2-test 74 0 "INSERT INTO node(nodeid) SELECT DISTINCT parent FROM leaf"
! 368: fkey2-2-test 75 0 "COMMIT"
! 369:
! 370: #-------------------------------------------------------------------------
! 371: # Test cases fkey2-3.* test that a program that executes foreign key
! 372: # actions (CASCADE, SET DEFAULT, SET NULL etc.) or tests FK constraints
! 373: # opens a statement transaction if required.
! 374: #
! 375: # fkey2-3.1.*: Test UPDATE statements.
! 376: # fkey2-3.2.*: Test DELETE statements.
! 377: #
! 378: drop_all_tables
! 379: do_test fkey2-3.1.1 {
! 380: execsql {
! 381: CREATE TABLE ab(a PRIMARY KEY, b);
! 382: CREATE TABLE cd(
! 383: c PRIMARY KEY REFERENCES ab ON UPDATE CASCADE ON DELETE CASCADE,
! 384: d
! 385: );
! 386: CREATE TABLE ef(
! 387: e REFERENCES cd ON UPDATE CASCADE,
! 388: f, CHECK (e!=5)
! 389: );
! 390: }
! 391: } {}
! 392: do_test fkey2-3.1.2 {
! 393: execsql {
! 394: INSERT INTO ab VALUES(1, 'b');
! 395: INSERT INTO cd VALUES(1, 'd');
! 396: INSERT INTO ef VALUES(1, 'e');
! 397: }
! 398: } {}
! 399: do_test fkey2-3.1.3 {
! 400: catchsql { UPDATE ab SET a = 5 }
! 401: } {1 {constraint failed}}
! 402: do_test fkey2-3.1.4 {
! 403: execsql { SELECT * FROM ab }
! 404: } {1 b}
! 405: do_test fkey2-3.1.4 {
! 406: execsql BEGIN;
! 407: catchsql { UPDATE ab SET a = 5 }
! 408: } {1 {constraint failed}}
! 409: do_test fkey2-3.1.5 {
! 410: execsql COMMIT;
! 411: execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
! 412: } {1 b 1 d 1 e}
! 413:
! 414: do_test fkey2-3.2.1 {
! 415: execsql BEGIN;
! 416: catchsql { DELETE FROM ab }
! 417: } {1 {foreign key constraint failed}}
! 418: do_test fkey2-3.2.2 {
! 419: execsql COMMIT
! 420: execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
! 421: } {1 b 1 d 1 e}
! 422:
! 423: #-------------------------------------------------------------------------
! 424: # Test cases fkey2-4.* test that recursive foreign key actions
! 425: # (i.e. CASCADE) are allowed even if recursive triggers are disabled.
! 426: #
! 427: drop_all_tables
! 428: do_test fkey2-4.1 {
! 429: execsql {
! 430: CREATE TABLE t1(
! 431: node PRIMARY KEY,
! 432: parent REFERENCES t1 ON DELETE CASCADE
! 433: );
! 434: CREATE TABLE t2(node PRIMARY KEY, parent);
! 435: CREATE TRIGGER t2t AFTER DELETE ON t2 BEGIN
! 436: DELETE FROM t2 WHERE parent = old.node;
! 437: END;
! 438: INSERT INTO t1 VALUES(1, NULL);
! 439: INSERT INTO t1 VALUES(2, 1);
! 440: INSERT INTO t1 VALUES(3, 1);
! 441: INSERT INTO t1 VALUES(4, 2);
! 442: INSERT INTO t1 VALUES(5, 2);
! 443: INSERT INTO t1 VALUES(6, 3);
! 444: INSERT INTO t1 VALUES(7, 3);
! 445: INSERT INTO t2 SELECT * FROM t1;
! 446: }
! 447: } {}
! 448: do_test fkey2-4.2 {
! 449: execsql { PRAGMA recursive_triggers = off }
! 450: execsql {
! 451: BEGIN;
! 452: DELETE FROM t1 WHERE node = 1;
! 453: SELECT node FROM t1;
! 454: }
! 455: } {}
! 456: do_test fkey2-4.3 {
! 457: execsql {
! 458: DELETE FROM t2 WHERE node = 1;
! 459: SELECT node FROM t2;
! 460: ROLLBACK;
! 461: }
! 462: } {4 5 6 7}
! 463: do_test fkey2-4.4 {
! 464: execsql { PRAGMA recursive_triggers = on }
! 465: execsql {
! 466: BEGIN;
! 467: DELETE FROM t1 WHERE node = 1;
! 468: SELECT node FROM t1;
! 469: }
! 470: } {}
! 471: do_test fkey2-4.3 {
! 472: execsql {
! 473: DELETE FROM t2 WHERE node = 1;
! 474: SELECT node FROM t2;
! 475: ROLLBACK;
! 476: }
! 477: } {}
! 478:
! 479: #-------------------------------------------------------------------------
! 480: # Test cases fkey2-5.* verify that the incremental blob API may not
! 481: # write to a foreign key column while foreign-keys are enabled.
! 482: #
! 483: drop_all_tables
! 484: ifcapable incrblob {
! 485: do_test fkey2-5.1 {
! 486: execsql {
! 487: CREATE TABLE t1(a PRIMARY KEY, b);
! 488: CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1(a));
! 489: INSERT INTO t1 VALUES('hello', 'world');
! 490: INSERT INTO t2 VALUES('key', 'hello');
! 491: }
! 492: } {}
! 493: do_test fkey2-5.2 {
! 494: set rc [catch { set fd [db incrblob t2 b 1] } msg]
! 495: list $rc $msg
! 496: } {1 {cannot open foreign key column for writing}}
! 497: do_test fkey2-5.3 {
! 498: set rc [catch { set fd [db incrblob -readonly t2 b 1] } msg]
! 499: close $fd
! 500: set rc
! 501: } {0}
! 502: do_test fkey2-5.4 {
! 503: execsql { PRAGMA foreign_keys = off }
! 504: set rc [catch { set fd [db incrblob t2 b 1] } msg]
! 505: close $fd
! 506: set rc
! 507: } {0}
! 508: do_test fkey2-5.5 {
! 509: execsql { PRAGMA foreign_keys = on }
! 510: } {}
! 511: }
! 512:
! 513: drop_all_tables
! 514: ifcapable vacuum {
! 515: do_test fkey2-6.1 {
! 516: execsql {
! 517: CREATE TABLE t1(a REFERENCES t2(c), b);
! 518: CREATE TABLE t2(c UNIQUE, b);
! 519: INSERT INTO t2 VALUES(1, 2);
! 520: INSERT INTO t1 VALUES(1, 2);
! 521: VACUUM;
! 522: }
! 523: } {}
! 524: }
! 525:
! 526: #-------------------------------------------------------------------------
! 527: # Test that it is possible to use an INTEGER PRIMARY KEY as the child key
! 528: # of a foreign constraint.
! 529: #
! 530: drop_all_tables
! 531: do_test fkey2-7.1 {
! 532: execsql {
! 533: CREATE TABLE t1(a PRIMARY KEY, b);
! 534: CREATE TABLE t2(c INTEGER PRIMARY KEY REFERENCES t1, b);
! 535: }
! 536: } {}
! 537: do_test fkey2-7.2 {
! 538: catchsql { INSERT INTO t2 VALUES(1, 'A'); }
! 539: } {1 {foreign key constraint failed}}
! 540: do_test fkey2-7.3 {
! 541: execsql {
! 542: INSERT INTO t1 VALUES(1, 2);
! 543: INSERT INTO t1 VALUES(2, 3);
! 544: INSERT INTO t2 VALUES(1, 'A');
! 545: }
! 546: } {}
! 547: do_test fkey2-7.4 {
! 548: execsql { UPDATE t2 SET c = 2 }
! 549: } {}
! 550: do_test fkey2-7.5 {
! 551: catchsql { UPDATE t2 SET c = 3 }
! 552: } {1 {foreign key constraint failed}}
! 553: do_test fkey2-7.6 {
! 554: catchsql { DELETE FROM t1 WHERE a = 2 }
! 555: } {1 {foreign key constraint failed}}
! 556: do_test fkey2-7.7 {
! 557: execsql { DELETE FROM t1 WHERE a = 1 }
! 558: } {}
! 559: do_test fkey2-7.8 {
! 560: catchsql { UPDATE t1 SET a = 3 }
! 561: } {1 {foreign key constraint failed}}
! 562: do_test fkey2-7.9 {
! 563: catchsql { UPDATE t2 SET rowid = 3 }
! 564: } {1 {foreign key constraint failed}}
! 565:
! 566: #-------------------------------------------------------------------------
! 567: # Test that it is not possible to enable/disable FK support while a
! 568: # transaction is open.
! 569: #
! 570: drop_all_tables
! 571: proc fkey2-8-test {tn zSql value} {
! 572: do_test fkey-2.8.$tn.1 [list execsql $zSql] {}
! 573: do_test fkey-2.8.$tn.2 { execsql "PRAGMA foreign_keys" } $value
! 574: }
! 575: fkey2-8-test 1 { PRAGMA foreign_keys = 0 } 0
! 576: fkey2-8-test 2 { PRAGMA foreign_keys = 1 } 1
! 577: fkey2-8-test 3 { BEGIN } 1
! 578: fkey2-8-test 4 { PRAGMA foreign_keys = 0 } 1
! 579: fkey2-8-test 5 { COMMIT } 1
! 580: fkey2-8-test 6 { PRAGMA foreign_keys = 0 } 0
! 581: fkey2-8-test 7 { BEGIN } 0
! 582: fkey2-8-test 8 { PRAGMA foreign_keys = 1 } 0
! 583: fkey2-8-test 9 { COMMIT } 0
! 584: fkey2-8-test 10 { PRAGMA foreign_keys = 1 } 1
! 585: fkey2-8-test 11 { PRAGMA foreign_keys = off } 0
! 586: fkey2-8-test 12 { PRAGMA foreign_keys = on } 1
! 587: fkey2-8-test 13 { PRAGMA foreign_keys = no } 0
! 588: fkey2-8-test 14 { PRAGMA foreign_keys = yes } 1
! 589: fkey2-8-test 15 { PRAGMA foreign_keys = false } 0
! 590: fkey2-8-test 16 { PRAGMA foreign_keys = true } 1
! 591:
! 592: #-------------------------------------------------------------------------
! 593: # The following tests, fkey2-9.*, test SET DEFAULT actions.
! 594: #
! 595: drop_all_tables
! 596: do_test fkey2-9.1.1 {
! 597: execsql {
! 598: CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
! 599: CREATE TABLE t2(
! 600: c INTEGER PRIMARY KEY,
! 601: d INTEGER DEFAULT 1 REFERENCES t1 ON DELETE SET DEFAULT
! 602: );
! 603: DELETE FROM t1;
! 604: }
! 605: } {}
! 606: do_test fkey2-9.1.2 {
! 607: execsql {
! 608: INSERT INTO t1 VALUES(1, 'one');
! 609: INSERT INTO t1 VALUES(2, 'two');
! 610: INSERT INTO t2 VALUES(1, 2);
! 611: SELECT * FROM t2;
! 612: DELETE FROM t1 WHERE a = 2;
! 613: SELECT * FROM t2;
! 614: }
! 615: } {1 2 1 1}
! 616: do_test fkey2-9.1.3 {
! 617: execsql {
! 618: INSERT INTO t1 VALUES(2, 'two');
! 619: UPDATE t2 SET d = 2;
! 620: DELETE FROM t1 WHERE a = 1;
! 621: SELECT * FROM t2;
! 622: }
! 623: } {1 2}
! 624: do_test fkey2-9.1.4 {
! 625: execsql { SELECT * FROM t1 }
! 626: } {2 two}
! 627: do_test fkey2-9.1.5 {
! 628: catchsql { DELETE FROM t1 }
! 629: } {1 {foreign key constraint failed}}
! 630:
! 631: do_test fkey2-9.2.1 {
! 632: execsql {
! 633: CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c));
! 634: CREATE TABLE cc(d DEFAULT 3, e DEFAULT 1, f DEFAULT 2,
! 635: FOREIGN KEY(f, d) REFERENCES pp
! 636: ON UPDATE SET DEFAULT
! 637: ON DELETE SET NULL
! 638: );
! 639: INSERT INTO pp VALUES(1, 2, 3);
! 640: INSERT INTO pp VALUES(4, 5, 6);
! 641: INSERT INTO pp VALUES(7, 8, 9);
! 642: }
! 643: } {}
! 644: do_test fkey2-9.2.2 {
! 645: execsql {
! 646: INSERT INTO cc VALUES(6, 'A', 5);
! 647: INSERT INTO cc VALUES(6, 'B', 5);
! 648: INSERT INTO cc VALUES(9, 'A', 8);
! 649: INSERT INTO cc VALUES(9, 'B', 8);
! 650: UPDATE pp SET b = 1 WHERE a = 7;
! 651: SELECT * FROM cc;
! 652: }
! 653: } {6 A 5 6 B 5 3 A 2 3 B 2}
! 654: do_test fkey2-9.2.3 {
! 655: execsql {
! 656: DELETE FROM pp WHERE a = 4;
! 657: SELECT * FROM cc;
! 658: }
! 659: } {{} A {} {} B {} 3 A 2 3 B 2}
! 660:
! 661: #-------------------------------------------------------------------------
! 662: # The following tests, fkey2-10.*, test "foreign key mismatch" and
! 663: # other errors.
! 664: #
! 665: set tn 0
! 666: foreach zSql [list {
! 667: CREATE TABLE p(a PRIMARY KEY, b);
! 668: CREATE TABLE c(x REFERENCES p(c));
! 669: } {
! 670: CREATE TABLE c(x REFERENCES v(y));
! 671: CREATE VIEW v AS SELECT x AS y FROM c;
! 672: } {
! 673: CREATE TABLE p(a, b, PRIMARY KEY(a, b));
! 674: CREATE TABLE c(x REFERENCES p);
! 675: } {
! 676: CREATE TABLE p(a COLLATE binary, b);
! 677: CREATE UNIQUE INDEX i ON p(a COLLATE nocase);
! 678: CREATE TABLE c(x REFERENCES p(a));
! 679: }] {
! 680: drop_all_tables
! 681: do_test fkey2-10.1.[incr tn] {
! 682: execsql $zSql
! 683: catchsql { INSERT INTO c DEFAULT VALUES }
! 684: } {1 {foreign key mismatch}}
! 685: }
! 686:
! 687: # "rowid" cannot be used as part of a child or parent key definition
! 688: # unless it happens to be the name of an explicitly declared column.
! 689: #
! 690: do_test fkey2-10.2.1 {
! 691: drop_all_tables
! 692: catchsql {
! 693: CREATE TABLE t1(a PRIMARY KEY, b);
! 694: CREATE TABLE t2(c, d, FOREIGN KEY(rowid) REFERENCES t1(a));
! 695: }
! 696: } {1 {unknown column "rowid" in foreign key definition}}
! 697: do_test fkey2-10.2.2 {
! 698: drop_all_tables
! 699: catchsql {
! 700: CREATE TABLE t1(a PRIMARY KEY, b);
! 701: CREATE TABLE t2(rowid, d, FOREIGN KEY(rowid) REFERENCES t1(a));
! 702: }
! 703: } {0 {}}
! 704: do_test fkey2-10.2.1 {
! 705: drop_all_tables
! 706: catchsql {
! 707: CREATE TABLE t1(a, b);
! 708: CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
! 709: INSERT INTO t1(rowid, a, b) VALUES(1, 1, 1);
! 710: INSERT INTO t2 VALUES(1, 1);
! 711: }
! 712: } {1 {foreign key mismatch}}
! 713: do_test fkey2-10.2.2 {
! 714: drop_all_tables
! 715: catchsql {
! 716: CREATE TABLE t1(rowid PRIMARY KEY, b);
! 717: CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
! 718: INSERT INTO t1(rowid, b) VALUES(1, 1);
! 719: INSERT INTO t2 VALUES(1, 1);
! 720: }
! 721: } {0 {}}
! 722:
! 723:
! 724: #-------------------------------------------------------------------------
! 725: # The following tests, fkey2-11.*, test CASCADE actions.
! 726: #
! 727: drop_all_tables
! 728: do_test fkey2-11.1.1 {
! 729: execsql {
! 730: CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
! 731: CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE);
! 732:
! 733: INSERT INTO t1 VALUES(10, 100);
! 734: INSERT INTO t2 VALUES(10, 100);
! 735: UPDATE t1 SET a = 15;
! 736: SELECT * FROM t2;
! 737: }
! 738: } {15 100}
! 739:
! 740: #-------------------------------------------------------------------------
! 741: # The following tests, fkey2-12.*, test RESTRICT actions.
! 742: #
! 743: drop_all_tables
! 744: do_test fkey2-12.1.1 {
! 745: execsql {
! 746: CREATE TABLE t1(a, b PRIMARY KEY);
! 747: CREATE TABLE t2(
! 748: x REFERENCES t1 ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED
! 749: );
! 750: INSERT INTO t1 VALUES(1, 'one');
! 751: INSERT INTO t1 VALUES(2, 'two');
! 752: INSERT INTO t1 VALUES(3, 'three');
! 753: }
! 754: } {}
! 755: do_test fkey2-12.1.2 {
! 756: execsql "BEGIN"
! 757: execsql "INSERT INTO t2 VALUES('two')"
! 758: } {}
! 759: do_test fkey2-12.1.3 {
! 760: execsql "UPDATE t1 SET b = 'four' WHERE b = 'one'"
! 761: } {}
! 762: do_test fkey2-12.1.4 {
! 763: catchsql "UPDATE t1 SET b = 'five' WHERE b = 'two'"
! 764: } {1 {foreign key constraint failed}}
! 765: do_test fkey2-12.1.5 {
! 766: execsql "DELETE FROM t1 WHERE b = 'two'"
! 767: } {}
! 768: do_test fkey2-12.1.6 {
! 769: catchsql "COMMIT"
! 770: } {1 {foreign key constraint failed}}
! 771: do_test fkey2-12.1.7 {
! 772: execsql {
! 773: INSERT INTO t1 VALUES(2, 'two');
! 774: COMMIT;
! 775: }
! 776: } {}
! 777:
! 778: drop_all_tables
! 779: do_test fkey2-12.2.1 {
! 780: execsql {
! 781: CREATE TABLE t1(x COLLATE NOCASE PRIMARY KEY);
! 782: CREATE TRIGGER tt1 AFTER DELETE ON t1
! 783: WHEN EXISTS ( SELECT 1 FROM t2 WHERE old.x = y )
! 784: BEGIN
! 785: INSERT INTO t1 VALUES(old.x);
! 786: END;
! 787: CREATE TABLE t2(y REFERENCES t1);
! 788: INSERT INTO t1 VALUES('A');
! 789: INSERT INTO t1 VALUES('B');
! 790: INSERT INTO t2 VALUES('a');
! 791: INSERT INTO t2 VALUES('b');
! 792:
! 793: SELECT * FROM t1;
! 794: SELECT * FROM t2;
! 795: }
! 796: } {A B a b}
! 797: do_test fkey2-12.2.2 {
! 798: execsql { DELETE FROM t1 }
! 799: execsql {
! 800: SELECT * FROM t1;
! 801: SELECT * FROM t2;
! 802: }
! 803: } {A B a b}
! 804: do_test fkey2-12.2.3 {
! 805: execsql {
! 806: DROP TABLE t2;
! 807: CREATE TABLE t2(y REFERENCES t1 ON DELETE RESTRICT);
! 808: INSERT INTO t2 VALUES('a');
! 809: INSERT INTO t2 VALUES('b');
! 810: }
! 811: catchsql { DELETE FROM t1 }
! 812: } {1 {foreign key constraint failed}}
! 813: do_test fkey2-12.2.4 {
! 814: execsql {
! 815: SELECT * FROM t1;
! 816: SELECT * FROM t2;
! 817: }
! 818: } {A B a b}
! 819:
! 820: drop_all_tables
! 821: do_test fkey2-12.3.1 {
! 822: execsql {
! 823: CREATE TABLE up(
! 824: c00, c01, c02, c03, c04, c05, c06, c07, c08, c09,
! 825: c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
! 826: c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
! 827: c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
! 828: PRIMARY KEY(c34, c35)
! 829: );
! 830: CREATE TABLE down(
! 831: c00, c01, c02, c03, c04, c05, c06, c07, c08, c09,
! 832: c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
! 833: c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
! 834: c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
! 835: FOREIGN KEY(c39, c38) REFERENCES up ON UPDATE CASCADE
! 836: );
! 837: }
! 838: } {}
! 839: do_test fkey2-12.3.2 {
! 840: execsql {
! 841: INSERT INTO up(c34, c35) VALUES('yes', 'no');
! 842: INSERT INTO down(c39, c38) VALUES('yes', 'no');
! 843: UPDATE up SET c34 = 'possibly';
! 844: SELECT c38, c39 FROM down;
! 845: DELETE FROM down;
! 846: }
! 847: } {no possibly}
! 848: do_test fkey2-12.3.3 {
! 849: catchsql { INSERT INTO down(c39, c38) VALUES('yes', 'no') }
! 850: } {1 {foreign key constraint failed}}
! 851: do_test fkey2-12.3.4 {
! 852: execsql {
! 853: INSERT INTO up(c34, c35) VALUES('yes', 'no');
! 854: INSERT INTO down(c39, c38) VALUES('yes', 'no');
! 855: }
! 856: catchsql { DELETE FROM up WHERE c34 = 'yes' }
! 857: } {1 {foreign key constraint failed}}
! 858: do_test fkey2-12.3.5 {
! 859: execsql {
! 860: DELETE FROM up WHERE c34 = 'possibly';
! 861: SELECT c34, c35 FROM up;
! 862: SELECT c39, c38 FROM down;
! 863: }
! 864: } {yes no yes no}
! 865:
! 866: #-------------------------------------------------------------------------
! 867: # The following tests, fkey2-13.*, test that FK processing is performed
! 868: # when rows are REPLACEd.
! 869: #
! 870: drop_all_tables
! 871: do_test fkey2-13.1.1 {
! 872: execsql {
! 873: CREATE TABLE pp(a UNIQUE, b, c, PRIMARY KEY(b, c));
! 874: CREATE TABLE cc(d, e, f UNIQUE, FOREIGN KEY(d, e) REFERENCES pp);
! 875: INSERT INTO pp VALUES(1, 2, 3);
! 876: INSERT INTO cc VALUES(2, 3, 1);
! 877: }
! 878: } {}
! 879: foreach {tn stmt} {
! 880: 1 "REPLACE INTO pp VALUES(1, 4, 5)"
! 881: 2 "REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 3, 4)"
! 882: } {
! 883: do_test fkey2-13.1.$tn.1 {
! 884: catchsql $stmt
! 885: } {1 {foreign key constraint failed}}
! 886: do_test fkey2-13.1.$tn.2 {
! 887: execsql {
! 888: SELECT * FROM pp;
! 889: SELECT * FROM cc;
! 890: }
! 891: } {1 2 3 2 3 1}
! 892: do_test fkey2-13.1.$tn.3 {
! 893: execsql BEGIN;
! 894: catchsql $stmt
! 895: } {1 {foreign key constraint failed}}
! 896: do_test fkey2-13.1.$tn.4 {
! 897: execsql {
! 898: COMMIT;
! 899: SELECT * FROM pp;
! 900: SELECT * FROM cc;
! 901: }
! 902: } {1 2 3 2 3 1}
! 903: }
! 904: do_test fkey2-13.1.3 {
! 905: execsql {
! 906: REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 2, 3);
! 907: SELECT rowid, * FROM pp;
! 908: SELECT * FROM cc;
! 909: }
! 910: } {1 2 2 3 2 3 1}
! 911: do_test fkey2-13.1.4 {
! 912: execsql {
! 913: REPLACE INTO pp(rowid, a, b, c) VALUES(2, 2, 2, 3);
! 914: SELECT rowid, * FROM pp;
! 915: SELECT * FROM cc;
! 916: }
! 917: } {2 2 2 3 2 3 1}
! 918:
! 919: #-------------------------------------------------------------------------
! 920: # The following tests, fkey2-14.*, test that the "DROP TABLE" and "ALTER
! 921: # TABLE" commands work as expected wrt foreign key constraints.
! 922: #
! 923: # fkey2-14.1*: ALTER TABLE ADD COLUMN
! 924: # fkey2-14.2*: ALTER TABLE RENAME TABLE
! 925: # fkey2-14.3*: DROP TABLE
! 926: #
! 927: drop_all_tables
! 928: ifcapable altertable {
! 929: do_test fkey2-14.1.1 {
! 930: # Adding a column with a REFERENCES clause is not supported.
! 931: execsql {
! 932: CREATE TABLE t1(a PRIMARY KEY);
! 933: CREATE TABLE t2(a, b);
! 934: }
! 935: catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
! 936: } {0 {}}
! 937: do_test fkey2-14.1.2 {
! 938: catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
! 939: } {0 {}}
! 940: do_test fkey2-14.1.3 {
! 941: catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
! 942: } {0 {}}
! 943: do_test fkey2-14.1.4 {
! 944: catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
! 945: } {1 {Cannot add a REFERENCES column with non-NULL default value}}
! 946: do_test fkey2-14.1.5 {
! 947: catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
! 948: } {1 {Cannot add a REFERENCES column with non-NULL default value}}
! 949: do_test fkey2-14.1.6 {
! 950: execsql {
! 951: PRAGMA foreign_keys = off;
! 952: ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
! 953: PRAGMA foreign_keys = on;
! 954: SELECT sql FROM sqlite_master WHERE name='t2';
! 955: }
! 956: } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
! 957:
! 958:
! 959: # Test the sqlite_rename_parent() function directly.
! 960: #
! 961: proc test_rename_parent {zCreate zOld zNew} {
! 962: db eval {SELECT sqlite_rename_parent($zCreate, $zOld, $zNew)}
! 963: }
! 964: do_test fkey2-14.2.1.1 {
! 965: test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
! 966: } {{CREATE TABLE t1(a REFERENCES "t3")}}
! 967: do_test fkey2-14.2.1.2 {
! 968: test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
! 969: } {{CREATE TABLE t1(a REFERENCES t2)}}
! 970: do_test fkey2-14.2.1.3 {
! 971: test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
! 972: } {{CREATE TABLE t1(a REFERENCES "t3")}}
! 973:
! 974: # Test ALTER TABLE RENAME TABLE a bit.
! 975: #
! 976: do_test fkey2-14.2.2.1 {
! 977: drop_all_tables
! 978: execsql {
! 979: CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1);
! 980: CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
! 981: CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
! 982: }
! 983: execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
! 984: } [list \
! 985: {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \
! 986: {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \
! 987: {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \
! 988: ]
! 989: do_test fkey2-14.2.2.2 {
! 990: execsql { ALTER TABLE t1 RENAME TO t4 }
! 991: execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
! 992: } [list \
! 993: {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \
! 994: {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \
! 995: {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
! 996: ]
! 997: do_test fkey2-14.2.2.3 {
! 998: catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
! 999: } {1 {foreign key constraint failed}}
! 1000: do_test fkey2-14.2.2.4 {
! 1001: execsql { INSERT INTO t4 VALUES(1, NULL) }
! 1002: } {}
! 1003: do_test fkey2-14.2.2.5 {
! 1004: catchsql { UPDATE t4 SET b = 5 }
! 1005: } {1 {foreign key constraint failed}}
! 1006: do_test fkey2-14.2.2.6 {
! 1007: catchsql { UPDATE t4 SET b = 1 }
! 1008: } {0 {}}
! 1009: do_test fkey2-14.2.2.7 {
! 1010: execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
! 1011: } {}
! 1012:
! 1013: # Repeat for TEMP tables
! 1014: #
! 1015: drop_all_tables
! 1016: do_test fkey2-14.1tmp.1 {
! 1017: # Adding a column with a REFERENCES clause is not supported.
! 1018: execsql {
! 1019: CREATE TEMP TABLE t1(a PRIMARY KEY);
! 1020: CREATE TEMP TABLE t2(a, b);
! 1021: }
! 1022: catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
! 1023: } {0 {}}
! 1024: do_test fkey2-14.1tmp.2 {
! 1025: catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
! 1026: } {0 {}}
! 1027: do_test fkey2-14.1tmp.3 {
! 1028: catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
! 1029: } {0 {}}
! 1030: do_test fkey2-14.1tmp.4 {
! 1031: catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
! 1032: } {1 {Cannot add a REFERENCES column with non-NULL default value}}
! 1033: do_test fkey2-14.1tmp.5 {
! 1034: catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
! 1035: } {1 {Cannot add a REFERENCES column with non-NULL default value}}
! 1036: do_test fkey2-14.1tmp.6 {
! 1037: execsql {
! 1038: PRAGMA foreign_keys = off;
! 1039: ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
! 1040: PRAGMA foreign_keys = on;
! 1041: SELECT sql FROM sqlite_temp_master WHERE name='t2';
! 1042: }
! 1043: } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
! 1044:
! 1045: do_test fkey2-14.2tmp.1.1 {
! 1046: test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
! 1047: } {{CREATE TABLE t1(a REFERENCES "t3")}}
! 1048: do_test fkey2-14.2tmp.1.2 {
! 1049: test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
! 1050: } {{CREATE TABLE t1(a REFERENCES t2)}}
! 1051: do_test fkey2-14.2tmp.1.3 {
! 1052: test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
! 1053: } {{CREATE TABLE t1(a REFERENCES "t3")}}
! 1054:
! 1055: # Test ALTER TABLE RENAME TABLE a bit.
! 1056: #
! 1057: do_test fkey2-14.2tmp.2.1 {
! 1058: drop_all_tables
! 1059: execsql {
! 1060: CREATE TEMP TABLE t1(a PRIMARY KEY, b REFERENCES t1);
! 1061: CREATE TEMP TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
! 1062: CREATE TEMP TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
! 1063: }
! 1064: execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'}
! 1065: } [list \
! 1066: {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \
! 1067: {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \
! 1068: {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \
! 1069: ]
! 1070: do_test fkey2-14.2tmp.2.2 {
! 1071: execsql { ALTER TABLE t1 RENAME TO t4 }
! 1072: execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'}
! 1073: } [list \
! 1074: {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \
! 1075: {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \
! 1076: {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
! 1077: ]
! 1078: do_test fkey2-14.2tmp.2.3 {
! 1079: catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
! 1080: } {1 {foreign key constraint failed}}
! 1081: do_test fkey2-14.2tmp.2.4 {
! 1082: execsql { INSERT INTO t4 VALUES(1, NULL) }
! 1083: } {}
! 1084: do_test fkey2-14.2tmp.2.5 {
! 1085: catchsql { UPDATE t4 SET b = 5 }
! 1086: } {1 {foreign key constraint failed}}
! 1087: do_test fkey2-14.2tmp.2.6 {
! 1088: catchsql { UPDATE t4 SET b = 1 }
! 1089: } {0 {}}
! 1090: do_test fkey2-14.2tmp.2.7 {
! 1091: execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
! 1092: } {}
! 1093:
! 1094: # Repeat for ATTACH-ed tables
! 1095: #
! 1096: drop_all_tables
! 1097: do_test fkey2-14.1aux.1 {
! 1098: # Adding a column with a REFERENCES clause is not supported.
! 1099: execsql {
! 1100: ATTACH ':memory:' AS aux;
! 1101: CREATE TABLE aux.t1(a PRIMARY KEY);
! 1102: CREATE TABLE aux.t2(a, b);
! 1103: }
! 1104: catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
! 1105: } {0 {}}
! 1106: do_test fkey2-14.1aux.2 {
! 1107: catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
! 1108: } {0 {}}
! 1109: do_test fkey2-14.1aux.3 {
! 1110: catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
! 1111: } {0 {}}
! 1112: do_test fkey2-14.1aux.4 {
! 1113: catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
! 1114: } {1 {Cannot add a REFERENCES column with non-NULL default value}}
! 1115: do_test fkey2-14.1aux.5 {
! 1116: catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
! 1117: } {1 {Cannot add a REFERENCES column with non-NULL default value}}
! 1118: do_test fkey2-14.1aux.6 {
! 1119: execsql {
! 1120: PRAGMA foreign_keys = off;
! 1121: ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
! 1122: PRAGMA foreign_keys = on;
! 1123: SELECT sql FROM aux.sqlite_master WHERE name='t2';
! 1124: }
! 1125: } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
! 1126:
! 1127: do_test fkey2-14.2aux.1.1 {
! 1128: test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
! 1129: } {{CREATE TABLE t1(a REFERENCES "t3")}}
! 1130: do_test fkey2-14.2aux.1.2 {
! 1131: test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
! 1132: } {{CREATE TABLE t1(a REFERENCES t2)}}
! 1133: do_test fkey2-14.2aux.1.3 {
! 1134: test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
! 1135: } {{CREATE TABLE t1(a REFERENCES "t3")}}
! 1136:
! 1137: # Test ALTER TABLE RENAME TABLE a bit.
! 1138: #
! 1139: do_test fkey2-14.2aux.2.1 {
! 1140: drop_all_tables
! 1141: execsql {
! 1142: CREATE TABLE aux.t1(a PRIMARY KEY, b REFERENCES t1);
! 1143: CREATE TABLE aux.t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
! 1144: CREATE TABLE aux.t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
! 1145: }
! 1146: execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'}
! 1147: } [list \
! 1148: {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \
! 1149: {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \
! 1150: {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \
! 1151: ]
! 1152: do_test fkey2-14.2aux.2.2 {
! 1153: execsql { ALTER TABLE t1 RENAME TO t4 }
! 1154: execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'}
! 1155: } [list \
! 1156: {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \
! 1157: {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \
! 1158: {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
! 1159: ]
! 1160: do_test fkey2-14.2aux.2.3 {
! 1161: catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
! 1162: } {1 {foreign key constraint failed}}
! 1163: do_test fkey2-14.2aux.2.4 {
! 1164: execsql { INSERT INTO t4 VALUES(1, NULL) }
! 1165: } {}
! 1166: do_test fkey2-14.2aux.2.5 {
! 1167: catchsql { UPDATE t4 SET b = 5 }
! 1168: } {1 {foreign key constraint failed}}
! 1169: do_test fkey2-14.2aux.2.6 {
! 1170: catchsql { UPDATE t4 SET b = 1 }
! 1171: } {0 {}}
! 1172: do_test fkey2-14.2aux.2.7 {
! 1173: execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
! 1174: } {}
! 1175: }
! 1176:
! 1177: do_test fkey-2.14.3.1 {
! 1178: drop_all_tables
! 1179: execsql {
! 1180: CREATE TABLE t1(a, b REFERENCES nosuchtable);
! 1181: DROP TABLE t1;
! 1182: }
! 1183: } {}
! 1184: do_test fkey-2.14.3.2 {
! 1185: execsql {
! 1186: CREATE TABLE t1(a PRIMARY KEY, b);
! 1187: INSERT INTO t1 VALUES('a', 1);
! 1188: CREATE TABLE t2(x REFERENCES t1);
! 1189: INSERT INTO t2 VALUES('a');
! 1190: }
! 1191: } {}
! 1192: do_test fkey-2.14.3.3 {
! 1193: catchsql { DROP TABLE t1 }
! 1194: } {1 {foreign key constraint failed}}
! 1195: do_test fkey-2.14.3.4 {
! 1196: execsql {
! 1197: DELETE FROM t2;
! 1198: DROP TABLE t1;
! 1199: }
! 1200: } {}
! 1201: do_test fkey-2.14.3.4 {
! 1202: catchsql { INSERT INTO t2 VALUES('x') }
! 1203: } {1 {no such table: main.t1}}
! 1204: do_test fkey-2.14.3.5 {
! 1205: execsql {
! 1206: CREATE TABLE t1(x PRIMARY KEY);
! 1207: INSERT INTO t1 VALUES('x');
! 1208: }
! 1209: execsql { INSERT INTO t2 VALUES('x') }
! 1210: } {}
! 1211: do_test fkey-2.14.3.6 {
! 1212: catchsql { DROP TABLE t1 }
! 1213: } {1 {foreign key constraint failed}}
! 1214: do_test fkey-2.14.3.7 {
! 1215: execsql {
! 1216: DROP TABLE t2;
! 1217: DROP TABLE t1;
! 1218: }
! 1219: } {}
! 1220: do_test fkey-2.14.3.8 {
! 1221: execsql {
! 1222: CREATE TABLE pp(x, y, PRIMARY KEY(x, y));
! 1223: CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z));
! 1224: }
! 1225: catchsql { INSERT INTO cc VALUES(1, 2) }
! 1226: } {1 {foreign key mismatch}}
! 1227: do_test fkey-2.14.3.9 {
! 1228: execsql { DROP TABLE cc }
! 1229: } {}
! 1230: do_test fkey-2.14.3.10 {
! 1231: execsql {
! 1232: CREATE TABLE cc(a, b,
! 1233: FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED
! 1234: );
! 1235: }
! 1236: execsql {
! 1237: INSERT INTO pp VALUES('a', 'b');
! 1238: INSERT INTO cc VALUES('a', 'b');
! 1239: BEGIN;
! 1240: DROP TABLE pp;
! 1241: CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c));
! 1242: INSERT INTO pp VALUES(1, 'a', 'b');
! 1243: COMMIT;
! 1244: }
! 1245: } {}
! 1246: do_test fkey-2.14.3.11 {
! 1247: execsql {
! 1248: BEGIN;
! 1249: DROP TABLE cc;
! 1250: DROP TABLE pp;
! 1251: COMMIT;
! 1252: }
! 1253: } {}
! 1254: do_test fkey-2.14.3.12 {
! 1255: execsql {
! 1256: CREATE TABLE b1(a, b);
! 1257: CREATE TABLE b2(a, b REFERENCES b1);
! 1258: DROP TABLE b1;
! 1259: }
! 1260: } {}
! 1261: do_test fkey-2.14.3.13 {
! 1262: execsql {
! 1263: CREATE TABLE b3(a, b REFERENCES b2 DEFERRABLE INITIALLY DEFERRED);
! 1264: DROP TABLE b2;
! 1265: }
! 1266: } {}
! 1267:
! 1268: # Test that nothing goes wrong when dropping a table that refers to a view.
! 1269: # Or dropping a view that an existing FK (incorrectly) refers to. Or either
! 1270: # of the above scenarios with a virtual table.
! 1271: drop_all_tables
! 1272: do_test fkey-2.14.4.1 {
! 1273: execsql {
! 1274: CREATE TABLE t1(x REFERENCES v);
! 1275: CREATE VIEW v AS SELECT * FROM t1;
! 1276: }
! 1277: } {}
! 1278: do_test fkey-2.14.4.2 {
! 1279: execsql {
! 1280: DROP VIEW v;
! 1281: }
! 1282: } {}
! 1283: ifcapable vtab {
! 1284: register_echo_module db
! 1285: do_test fkey-2.14.4.3 {
! 1286: execsql { CREATE VIRTUAL TABLE v USING echo(t1) }
! 1287: } {}
! 1288: do_test fkey-2.14.4.2 {
! 1289: execsql {
! 1290: DROP TABLE v;
! 1291: }
! 1292: } {}
! 1293: }
! 1294:
! 1295: #-------------------------------------------------------------------------
! 1296: # The following tests, fkey2-15.*, test that unnecessary FK related scans
! 1297: # and lookups are avoided when the constraint counters are zero.
! 1298: #
! 1299: drop_all_tables
! 1300: proc execsqlS {zSql} {
! 1301: set ::sqlite_search_count 0
! 1302: set ::sqlite_found_count 0
! 1303: set res [uplevel [list execsql $zSql]]
! 1304: concat [expr $::sqlite_found_count + $::sqlite_search_count] $res
! 1305: }
! 1306: do_test fkey2-15.1.1 {
! 1307: execsql {
! 1308: CREATE TABLE pp(a PRIMARY KEY, b);
! 1309: CREATE TABLE cc(x, y REFERENCES pp DEFERRABLE INITIALLY DEFERRED);
! 1310: INSERT INTO pp VALUES(1, 'one');
! 1311: INSERT INTO pp VALUES(2, 'two');
! 1312: INSERT INTO cc VALUES('neung', 1);
! 1313: INSERT INTO cc VALUES('song', 2);
! 1314: }
! 1315: } {}
! 1316: do_test fkey2-15.1.2 {
! 1317: execsqlS { INSERT INTO pp VALUES(3, 'three') }
! 1318: } {0}
! 1319: do_test fkey2-15.1.3 {
! 1320: execsql {
! 1321: BEGIN;
! 1322: INSERT INTO cc VALUES('see', 4); -- Violates deferred constraint
! 1323: }
! 1324: execsqlS { INSERT INTO pp VALUES(5, 'five') }
! 1325: } {2}
! 1326: do_test fkey2-15.1.4 {
! 1327: execsql { DELETE FROM cc WHERE x = 'see' }
! 1328: execsqlS { INSERT INTO pp VALUES(6, 'six') }
! 1329: } {0}
! 1330: do_test fkey2-15.1.5 {
! 1331: execsql COMMIT
! 1332: } {}
! 1333: do_test fkey2-15.1.6 {
! 1334: execsql BEGIN
! 1335: execsqlS {
! 1336: DELETE FROM cc WHERE x = 'neung';
! 1337: ROLLBACK;
! 1338: }
! 1339: } {1}
! 1340: do_test fkey2-15.1.7 {
! 1341: execsql {
! 1342: BEGIN;
! 1343: DELETE FROM pp WHERE a = 2;
! 1344: }
! 1345: execsqlS {
! 1346: DELETE FROM cc WHERE x = 'neung';
! 1347: ROLLBACK;
! 1348: }
! 1349: } {2}
! 1350:
! 1351: #-------------------------------------------------------------------------
! 1352: # This next block of tests, fkey2-16.*, test that rows that refer to
! 1353: # themselves may be inserted and deleted.
! 1354: #
! 1355: foreach {tn zSchema} {
! 1356: 1 { CREATE TABLE self(a INTEGER PRIMARY KEY, b REFERENCES self(a)) }
! 1357: 2 { CREATE TABLE self(a PRIMARY KEY, b REFERENCES self(a)) }
! 1358: 3 { CREATE TABLE self(a UNIQUE, b INTEGER PRIMARY KEY REFERENCES self(a)) }
! 1359: } {
! 1360: drop_all_tables
! 1361: do_test fkey2-16.1.$tn.1 {
! 1362: execsql $zSchema
! 1363: execsql { INSERT INTO self VALUES(13, 13) }
! 1364: } {}
! 1365: do_test fkey2-16.1.$tn.2 {
! 1366: execsql { UPDATE self SET a = 14, b = 14 }
! 1367: } {}
! 1368:
! 1369: do_test fkey2-16.1.$tn.3 {
! 1370: catchsql { UPDATE self SET b = 15 }
! 1371: } {1 {foreign key constraint failed}}
! 1372:
! 1373: do_test fkey2-16.1.$tn.4 {
! 1374: catchsql { UPDATE self SET a = 15 }
! 1375: } {1 {foreign key constraint failed}}
! 1376:
! 1377: do_test fkey2-16.1.$tn.5 {
! 1378: catchsql { UPDATE self SET a = 15, b = 16 }
! 1379: } {1 {foreign key constraint failed}}
! 1380:
! 1381: do_test fkey2-16.1.$tn.6 {
! 1382: catchsql { UPDATE self SET a = 17, b = 17 }
! 1383: } {0 {}}
! 1384:
! 1385: do_test fkey2-16.1.$tn.7 {
! 1386: execsql { DELETE FROM self }
! 1387: } {}
! 1388: do_test fkey2-16.1.$tn.8 {
! 1389: catchsql { INSERT INTO self VALUES(20, 21) }
! 1390: } {1 {foreign key constraint failed}}
! 1391: }
! 1392:
! 1393: #-------------------------------------------------------------------------
! 1394: # This next block of tests, fkey2-17.*, tests that if "PRAGMA count_changes"
! 1395: # is turned on statements that violate immediate FK constraints return
! 1396: # SQLITE_CONSTRAINT immediately, not after returning a number of rows.
! 1397: # Whereas statements that violate deferred FK constraints return the number
! 1398: # of rows before failing.
! 1399: #
! 1400: # Also test that rows modified by FK actions are not counted in either the
! 1401: # returned row count or the values returned by sqlite3_changes(). Like
! 1402: # trigger related changes, they are included in sqlite3_total_changes() though.
! 1403: #
! 1404: drop_all_tables
! 1405: do_test fkey2-17.1.1 {
! 1406: execsql { PRAGMA count_changes = 1 }
! 1407: execsql {
! 1408: CREATE TABLE one(a, b, c, UNIQUE(b, c));
! 1409: CREATE TABLE two(d, e, f, FOREIGN KEY(e, f) REFERENCES one(b, c));
! 1410: INSERT INTO one VALUES(1, 2, 3);
! 1411: }
! 1412: } {1}
! 1413: do_test fkey2-17.1.2 {
! 1414: set STMT [sqlite3_prepare_v2 db "INSERT INTO two VALUES(4, 5, 6)" -1 dummy]
! 1415: sqlite3_step $STMT
! 1416: } {SQLITE_CONSTRAINT}
! 1417: ifcapable autoreset {
! 1418: do_test fkey2-17.1.3 {
! 1419: sqlite3_step $STMT
! 1420: } {SQLITE_CONSTRAINT}
! 1421: } else {
! 1422: do_test fkey2-17.1.3 {
! 1423: sqlite3_step $STMT
! 1424: } {SQLITE_MISUSE}
! 1425: }
! 1426: do_test fkey2-17.1.4 {
! 1427: sqlite3_finalize $STMT
! 1428: } {SQLITE_CONSTRAINT}
! 1429: do_test fkey2-17.1.5 {
! 1430: execsql {
! 1431: INSERT INTO one VALUES(2, 3, 4);
! 1432: INSERT INTO one VALUES(3, 4, 5);
! 1433: INSERT INTO two VALUES(1, 2, 3);
! 1434: INSERT INTO two VALUES(2, 3, 4);
! 1435: INSERT INTO two VALUES(3, 4, 5);
! 1436: }
! 1437: } {1 1 1 1 1}
! 1438: do_test fkey2-17.1.6 {
! 1439: catchsql {
! 1440: BEGIN;
! 1441: INSERT INTO one VALUES(0, 0, 0);
! 1442: UPDATE two SET e=e+1, f=f+1;
! 1443: }
! 1444: } {1 {foreign key constraint failed}}
! 1445: do_test fkey2-17.1.7 {
! 1446: execsql { SELECT * FROM one }
! 1447: } {1 2 3 2 3 4 3 4 5 0 0 0}
! 1448: do_test fkey2-17.1.8 {
! 1449: execsql { SELECT * FROM two }
! 1450: } {1 2 3 2 3 4 3 4 5}
! 1451: do_test fkey2-17.1.9 {
! 1452: execsql COMMIT
! 1453: } {}
! 1454: do_test fkey2-17.1.10 {
! 1455: execsql {
! 1456: CREATE TABLE three(
! 1457: g, h, i,
! 1458: FOREIGN KEY(h, i) REFERENCES one(b, c) DEFERRABLE INITIALLY DEFERRED
! 1459: );
! 1460: }
! 1461: } {}
! 1462: do_test fkey2-17.1.11 {
! 1463: set STMT [sqlite3_prepare_v2 db "INSERT INTO three VALUES(7, 8, 9)" -1 dummy]
! 1464: sqlite3_step $STMT
! 1465: } {SQLITE_ROW}
! 1466: do_test fkey2-17.1.12 {
! 1467: sqlite3_column_text $STMT 0
! 1468: } {1}
! 1469: do_test fkey2-17.1.13 {
! 1470: sqlite3_step $STMT
! 1471: } {SQLITE_CONSTRAINT}
! 1472: do_test fkey2-17.1.14 {
! 1473: sqlite3_finalize $STMT
! 1474: } {SQLITE_CONSTRAINT}
! 1475:
! 1476: drop_all_tables
! 1477: do_test fkey2-17.2.1 {
! 1478: execsql {
! 1479: CREATE TABLE high("a'b!" PRIMARY KEY, b);
! 1480: CREATE TABLE low(
! 1481: c,
! 1482: "d&6" REFERENCES high ON UPDATE CASCADE ON DELETE CASCADE
! 1483: );
! 1484: }
! 1485: } {}
! 1486: do_test fkey2-17.2.2 {
! 1487: execsql {
! 1488: INSERT INTO high VALUES('a', 'b');
! 1489: INSERT INTO low VALUES('b', 'a');
! 1490: }
! 1491: db changes
! 1492: } {1}
! 1493: set nTotal [db total_changes]
! 1494: do_test fkey2-17.2.3 {
! 1495: execsql { UPDATE high SET "a'b!" = 'c' }
! 1496: } {1}
! 1497: do_test fkey2-17.2.4 {
! 1498: db changes
! 1499: } {1}
! 1500: do_test fkey2-17.2.5 {
! 1501: expr [db total_changes] - $nTotal
! 1502: } {2}
! 1503: do_test fkey2-17.2.6 {
! 1504: execsql { SELECT * FROM high ; SELECT * FROM low }
! 1505: } {c b b c}
! 1506: do_test fkey2-17.2.7 {
! 1507: execsql { DELETE FROM high }
! 1508: } {1}
! 1509: do_test fkey2-17.2.8 {
! 1510: db changes
! 1511: } {1}
! 1512: do_test fkey2-17.2.9 {
! 1513: expr [db total_changes] - $nTotal
! 1514: } {4}
! 1515: do_test fkey2-17.2.10 {
! 1516: execsql { SELECT * FROM high ; SELECT * FROM low }
! 1517: } {}
! 1518: execsql { PRAGMA count_changes = 0 }
! 1519:
! 1520: #-------------------------------------------------------------------------
! 1521: # Test that the authorization callback works.
! 1522: #
! 1523:
! 1524: ifcapable auth {
! 1525: do_test fkey2-18.1 {
! 1526: execsql {
! 1527: CREATE TABLE long(a, b PRIMARY KEY, c);
! 1528: CREATE TABLE short(d, e, f REFERENCES long);
! 1529: CREATE TABLE mid(g, h, i REFERENCES long DEFERRABLE INITIALLY DEFERRED);
! 1530: }
! 1531: } {}
! 1532:
! 1533: proc auth {args} {eval lappend ::authargs $args ; return SQLITE_OK}
! 1534: db auth auth
! 1535:
! 1536: # An insert on the parent table must read the child key of any deferred
! 1537: # foreign key constraints. But not the child key of immediate constraints.
! 1538: set authargs {}
! 1539: do_test fkey2-18.2 {
! 1540: execsql { INSERT INTO long VALUES(1, 2, 3) }
! 1541: set authargs
! 1542: } {SQLITE_INSERT long {} main {} SQLITE_READ mid i main {}}
! 1543:
! 1544: # An insert on the child table of an immediate constraint must read the
! 1545: # parent key columns (to see if it is a violation or not).
! 1546: set authargs {}
! 1547: do_test fkey2-18.3 {
! 1548: execsql { INSERT INTO short VALUES(1, 3, 2) }
! 1549: set authargs
! 1550: } {SQLITE_INSERT short {} main {} SQLITE_READ long b main {}}
! 1551:
! 1552: # As must an insert on the child table of a deferred constraint.
! 1553: set authargs {}
! 1554: do_test fkey2-18.4 {
! 1555: execsql { INSERT INTO mid VALUES(1, 3, 2) }
! 1556: set authargs
! 1557: } {SQLITE_INSERT mid {} main {} SQLITE_READ long b main {}}
! 1558:
! 1559: do_test fkey2-18.5 {
! 1560: execsql {
! 1561: CREATE TABLE nought(a, b PRIMARY KEY, c);
! 1562: CREATE TABLE cross(d, e, f,
! 1563: FOREIGN KEY(e) REFERENCES nought(b) ON UPDATE CASCADE
! 1564: );
! 1565: }
! 1566: execsql { INSERT INTO nought VALUES(2, 1, 2) }
! 1567: execsql { INSERT INTO cross VALUES(0, 1, 0) }
! 1568: set authargs [list]
! 1569: execsql { UPDATE nought SET b = 5 }
! 1570: set authargs
! 1571: } {SQLITE_UPDATE nought b main {} SQLITE_READ cross e main {} SQLITE_READ cross e main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {} SQLITE_UPDATE cross e main {} SQLITE_READ nought b main {} SQLITE_READ cross e main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {}}
! 1572:
! 1573: do_test fkey2-18.6 {
! 1574: execsql {SELECT * FROM cross}
! 1575: } {0 5 0}
! 1576:
! 1577: do_test fkey2-18.7 {
! 1578: execsql {
! 1579: CREATE TABLE one(a INTEGER PRIMARY KEY, b);
! 1580: CREATE TABLE two(b, c REFERENCES one);
! 1581: INSERT INTO one VALUES(101, 102);
! 1582: }
! 1583: set authargs [list]
! 1584: execsql { INSERT INTO two VALUES(100, 101); }
! 1585: set authargs
! 1586: } {SQLITE_INSERT two {} main {} SQLITE_READ one a main {}}
! 1587:
! 1588: # Return SQLITE_IGNORE to requests to read from the parent table. This
! 1589: # causes inserts of non-NULL keys into the child table to fail.
! 1590: #
! 1591: rename auth {}
! 1592: proc auth {args} {
! 1593: if {[lindex $args 1] == "long"} {return SQLITE_IGNORE}
! 1594: return SQLITE_OK
! 1595: }
! 1596: do_test fkey2-18.8 {
! 1597: catchsql { INSERT INTO short VALUES(1, 3, 2) }
! 1598: } {1 {foreign key constraint failed}}
! 1599: do_test fkey2-18.9 {
! 1600: execsql { INSERT INTO short VALUES(1, 3, NULL) }
! 1601: } {}
! 1602: do_test fkey2-18.10 {
! 1603: execsql { SELECT * FROM short }
! 1604: } {1 3 2 1 3 {}}
! 1605: do_test fkey2-18.11 {
! 1606: catchsql { UPDATE short SET f = 2 WHERE f IS NULL }
! 1607: } {1 {foreign key constraint failed}}
! 1608:
! 1609: db auth {}
! 1610: unset authargs
! 1611: }
! 1612:
! 1613:
! 1614: do_test fkey2-19.1 {
! 1615: execsql {
! 1616: CREATE TABLE main(id INTEGER PRIMARY KEY);
! 1617: CREATE TABLE sub(id INT REFERENCES main(id));
! 1618: INSERT INTO main VALUES(1);
! 1619: INSERT INTO main VALUES(2);
! 1620: INSERT INTO sub VALUES(2);
! 1621: }
! 1622: } {}
! 1623: do_test fkey2-19.2 {
! 1624: set S [sqlite3_prepare_v2 db "DELETE FROM main WHERE id = ?" -1 dummy]
! 1625: sqlite3_bind_int $S 1 2
! 1626: sqlite3_step $S
! 1627: } {SQLITE_CONSTRAINT}
! 1628: do_test fkey2-19.3 {
! 1629: sqlite3_reset $S
! 1630: } {SQLITE_CONSTRAINT}
! 1631: do_test fkey2-19.4 {
! 1632: sqlite3_bind_int $S 1 1
! 1633: sqlite3_step $S
! 1634: } {SQLITE_DONE}
! 1635: do_test fkey2-19.4 {
! 1636: sqlite3_finalize $S
! 1637: } {SQLITE_OK}
! 1638:
! 1639: drop_all_tables
! 1640: do_test fkey2-20.1 {
! 1641: execsql {
! 1642: CREATE TABLE pp(a PRIMARY KEY, b);
! 1643: CREATE TABLE cc(c PRIMARY KEY, d REFERENCES pp);
! 1644: }
! 1645: } {}
! 1646:
! 1647: foreach {tn insert} {
! 1648: 1 "INSERT"
! 1649: 2 "INSERT OR IGNORE"
! 1650: 3 "INSERT OR ABORT"
! 1651: 4 "INSERT OR ROLLBACK"
! 1652: 5 "INSERT OR REPLACE"
! 1653: 6 "INSERT OR FAIL"
! 1654: } {
! 1655: do_test fkey2-20.2.$tn.1 {
! 1656: catchsql "$insert INTO cc VALUES(1, 2)"
! 1657: } {1 {foreign key constraint failed}}
! 1658: do_test fkey2-20.2.$tn.2 {
! 1659: execsql { SELECT * FROM cc }
! 1660: } {}
! 1661: do_test fkey2-20.2.$tn.3 {
! 1662: execsql {
! 1663: BEGIN;
! 1664: INSERT INTO pp VALUES(2, 'two');
! 1665: INSERT INTO cc VALUES(1, 2);
! 1666: }
! 1667: catchsql "$insert INTO cc VALUES(3, 4)"
! 1668: } {1 {foreign key constraint failed}}
! 1669: do_test fkey2-20.2.$tn.4 {
! 1670: execsql { COMMIT ; SELECT * FROM cc }
! 1671: } {1 2}
! 1672: do_test fkey2-20.2.$tn.5 {
! 1673: execsql { DELETE FROM cc ; DELETE FROM pp }
! 1674: } {}
! 1675: }
! 1676:
! 1677: foreach {tn update} {
! 1678: 1 "UPDATE"
! 1679: 2 "UPDATE OR IGNORE"
! 1680: 3 "UPDATE OR ABORT"
! 1681: 4 "UPDATE OR ROLLBACK"
! 1682: 5 "UPDATE OR REPLACE"
! 1683: 6 "UPDATE OR FAIL"
! 1684: } {
! 1685: do_test fkey2-20.3.$tn.1 {
! 1686: execsql {
! 1687: INSERT INTO pp VALUES(2, 'two');
! 1688: INSERT INTO cc VALUES(1, 2);
! 1689: }
! 1690: } {}
! 1691: do_test fkey2-20.3.$tn.2 {
! 1692: catchsql "$update pp SET a = 1"
! 1693: } {1 {foreign key constraint failed}}
! 1694: do_test fkey2-20.3.$tn.3 {
! 1695: execsql { SELECT * FROM pp }
! 1696: } {2 two}
! 1697: do_test fkey2-20.3.$tn.4 {
! 1698: catchsql "$update cc SET d = 1"
! 1699: } {1 {foreign key constraint failed}}
! 1700: do_test fkey2-20.3.$tn.5 {
! 1701: execsql { SELECT * FROM cc }
! 1702: } {1 2}
! 1703: do_test fkey2-20.3.$tn.6 {
! 1704: execsql {
! 1705: BEGIN;
! 1706: INSERT INTO pp VALUES(3, 'three');
! 1707: }
! 1708: catchsql "$update pp SET a = 1 WHERE a = 2"
! 1709: } {1 {foreign key constraint failed}}
! 1710: do_test fkey2-20.3.$tn.7 {
! 1711: execsql { COMMIT ; SELECT * FROM pp }
! 1712: } {2 two 3 three}
! 1713: do_test fkey2-20.3.$tn.8 {
! 1714: execsql {
! 1715: BEGIN;
! 1716: INSERT INTO cc VALUES(2, 2);
! 1717: }
! 1718: catchsql "$update cc SET d = 1 WHERE c = 1"
! 1719: } {1 {foreign key constraint failed}}
! 1720: do_test fkey2-20.3.$tn.9 {
! 1721: execsql { COMMIT ; SELECT * FROM cc }
! 1722: } {1 2 2 2}
! 1723: do_test fkey2-20.3.$tn.10 {
! 1724: execsql { DELETE FROM cc ; DELETE FROM pp }
! 1725: } {}
! 1726: }
! 1727:
! 1728: #-------------------------------------------------------------------------
! 1729: # The following block of tests, those prefixed with "fkey2-genfkey.", are
! 1730: # the same tests that were used to test the ".genfkey" command provided
! 1731: # by the shell tool. So these tests show that the built-in foreign key
! 1732: # implementation is more or less compatible with the triggers generated
! 1733: # by genfkey.
! 1734: #
! 1735: drop_all_tables
! 1736: do_test fkey2-genfkey.1.1 {
! 1737: execsql {
! 1738: CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
! 1739: CREATE TABLE t2(e REFERENCES t1, f);
! 1740: CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c));
! 1741: }
! 1742: } {}
! 1743: do_test fkey2-genfkey.1.2 {
! 1744: catchsql { INSERT INTO t2 VALUES(1, 2) }
! 1745: } {1 {foreign key constraint failed}}
! 1746: do_test fkey2-genfkey.1.3 {
! 1747: execsql {
! 1748: INSERT INTO t1 VALUES(1, 2, 3);
! 1749: INSERT INTO t2 VALUES(1, 2);
! 1750: }
! 1751: } {}
! 1752: do_test fkey2-genfkey.1.4 {
! 1753: execsql { INSERT INTO t2 VALUES(NULL, 3) }
! 1754: } {}
! 1755: do_test fkey2-genfkey.1.5 {
! 1756: catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL }
! 1757: } {1 {foreign key constraint failed}}
! 1758: do_test fkey2-genfkey.1.6 {
! 1759: execsql { UPDATE t2 SET e = 1 WHERE e IS NULL }
! 1760: } {}
! 1761: do_test fkey2-genfkey.1.7 {
! 1762: execsql { UPDATE t2 SET e = NULL WHERE f = 3 }
! 1763: } {}
! 1764: do_test fkey2-genfkey.1.8 {
! 1765: catchsql { UPDATE t1 SET a = 10 }
! 1766: } {1 {foreign key constraint failed}}
! 1767: do_test fkey2-genfkey.1.9 {
! 1768: catchsql { UPDATE t1 SET a = NULL }
! 1769: } {1 {datatype mismatch}}
! 1770: do_test fkey2-genfkey.1.10 {
! 1771: catchsql { DELETE FROM t1 }
! 1772: } {1 {foreign key constraint failed}}
! 1773: do_test fkey2-genfkey.1.11 {
! 1774: execsql { UPDATE t2 SET e = NULL }
! 1775: } {}
! 1776: do_test fkey2-genfkey.1.12 {
! 1777: execsql {
! 1778: UPDATE t1 SET a = 10;
! 1779: DELETE FROM t1;
! 1780: DELETE FROM t2;
! 1781: }
! 1782: } {}
! 1783: do_test fkey2-genfkey.1.13 {
! 1784: execsql {
! 1785: INSERT INTO t3 VALUES(1, NULL, NULL);
! 1786: INSERT INTO t3 VALUES(1, 2, NULL);
! 1787: INSERT INTO t3 VALUES(1, NULL, 3);
! 1788: }
! 1789: } {}
! 1790: do_test fkey2-genfkey.1.14 {
! 1791: catchsql { INSERT INTO t3 VALUES(3, 1, 4) }
! 1792: } {1 {foreign key constraint failed}}
! 1793: do_test fkey2-genfkey.1.15 {
! 1794: execsql {
! 1795: INSERT INTO t1 VALUES(1, 1, 4);
! 1796: INSERT INTO t3 VALUES(3, 1, 4);
! 1797: }
! 1798: } {}
! 1799: do_test fkey2-genfkey.1.16 {
! 1800: catchsql { DELETE FROM t1 }
! 1801: } {1 {foreign key constraint failed}}
! 1802: do_test fkey2-genfkey.1.17 {
! 1803: catchsql { UPDATE t1 SET b = 10}
! 1804: } {1 {foreign key constraint failed}}
! 1805: do_test fkey2-genfkey.1.18 {
! 1806: execsql { UPDATE t1 SET a = 10}
! 1807: } {}
! 1808: do_test fkey2-genfkey.1.19 {
! 1809: catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3}
! 1810: } {1 {foreign key constraint failed}}
! 1811:
! 1812: drop_all_tables
! 1813: do_test fkey2-genfkey.2.1 {
! 1814: execsql {
! 1815: CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
! 1816: CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f);
! 1817: CREATE TABLE t3(g, h, i,
! 1818: FOREIGN KEY (h, i)
! 1819: REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE
! 1820: );
! 1821: }
! 1822: } {}
! 1823: do_test fkey2-genfkey.2.2 {
! 1824: execsql {
! 1825: INSERT INTO t1 VALUES(1, 2, 3);
! 1826: INSERT INTO t1 VALUES(4, 5, 6);
! 1827: INSERT INTO t2 VALUES(1, 'one');
! 1828: INSERT INTO t2 VALUES(4, 'four');
! 1829: }
! 1830: } {}
! 1831: do_test fkey2-genfkey.2.3 {
! 1832: execsql {
! 1833: UPDATE t1 SET a = 2 WHERE a = 1;
! 1834: SELECT * FROM t2;
! 1835: }
! 1836: } {2 one 4 four}
! 1837: do_test fkey2-genfkey.2.4 {
! 1838: execsql {
! 1839: DELETE FROM t1 WHERE a = 4;
! 1840: SELECT * FROM t2;
! 1841: }
! 1842: } {2 one}
! 1843:
! 1844: do_test fkey2-genfkey.2.5 {
! 1845: execsql {
! 1846: INSERT INTO t3 VALUES('hello', 2, 3);
! 1847: UPDATE t1 SET c = 2;
! 1848: SELECT * FROM t3;
! 1849: }
! 1850: } {hello 2 2}
! 1851: do_test fkey2-genfkey.2.6 {
! 1852: execsql {
! 1853: DELETE FROM t1;
! 1854: SELECT * FROM t3;
! 1855: }
! 1856: } {}
! 1857:
! 1858: drop_all_tables
! 1859: do_test fkey2-genfkey.3.1 {
! 1860: execsql {
! 1861: CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b));
! 1862: CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f);
! 1863: CREATE TABLE t3(g, h, i,
! 1864: FOREIGN KEY (h, i)
! 1865: REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL
! 1866: );
! 1867: }
! 1868: } {}
! 1869: do_test fkey2-genfkey.3.2 {
! 1870: execsql {
! 1871: INSERT INTO t1 VALUES(1, 2, 3);
! 1872: INSERT INTO t1 VALUES(4, 5, 6);
! 1873: INSERT INTO t2 VALUES(1, 'one');
! 1874: INSERT INTO t2 VALUES(4, 'four');
! 1875: }
! 1876: } {}
! 1877: do_test fkey2-genfkey.3.3 {
! 1878: execsql {
! 1879: UPDATE t1 SET a = 2 WHERE a = 1;
! 1880: SELECT * FROM t2;
! 1881: }
! 1882: } {{} one 4 four}
! 1883: do_test fkey2-genfkey.3.4 {
! 1884: execsql {
! 1885: DELETE FROM t1 WHERE a = 4;
! 1886: SELECT * FROM t2;
! 1887: }
! 1888: } {{} one {} four}
! 1889: do_test fkey2-genfkey.3.5 {
! 1890: execsql {
! 1891: INSERT INTO t3 VALUES('hello', 2, 3);
! 1892: UPDATE t1 SET c = 2;
! 1893: SELECT * FROM t3;
! 1894: }
! 1895: } {hello {} {}}
! 1896: do_test fkey2-genfkey.3.6 {
! 1897: execsql {
! 1898: UPDATE t3 SET h = 2, i = 2;
! 1899: DELETE FROM t1;
! 1900: SELECT * FROM t3;
! 1901: }
! 1902: } {hello {} {}}
! 1903:
! 1904: #-------------------------------------------------------------------------
! 1905: # Verify that ticket dd08e5a988d00decc4a543daa8dbbfab9c577ad8 has been
! 1906: # fixed.
! 1907: #
! 1908: do_test fkey2-dd08e5.1.1 {
! 1909: execsql {
! 1910: PRAGMA foreign_keys=ON;
! 1911: CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b);
! 1912: CREATE UNIQUE INDEX idd08 ON tdd08(a,b);
! 1913: INSERT INTO tdd08 VALUES(200,300);
! 1914:
! 1915: CREATE TABLE tdd08_b(w,x,y, FOREIGN KEY(x,y) REFERENCES tdd08(a,b));
! 1916: INSERT INTO tdd08_b VALUES(100,200,300);
! 1917: }
! 1918: } {}
! 1919: do_test fkey2-dd08e5.1.2 {
! 1920: catchsql {
! 1921: DELETE FROM tdd08;
! 1922: }
! 1923: } {1 {foreign key constraint failed}}
! 1924: do_test fkey2-dd08e5.1.3 {
! 1925: execsql {
! 1926: SELECT * FROM tdd08;
! 1927: }
! 1928: } {200 300}
! 1929: do_test fkey2-dd08e5.1.4 {
! 1930: catchsql {
! 1931: INSERT INTO tdd08_b VALUES(400,500,300);
! 1932: }
! 1933: } {1 {foreign key constraint failed}}
! 1934: do_test fkey2-dd08e5.1.5 {
! 1935: catchsql {
! 1936: UPDATE tdd08_b SET x=x+1;
! 1937: }
! 1938: } {1 {foreign key constraint failed}}
! 1939: do_test fkey2-dd08e5.1.6 {
! 1940: catchsql {
! 1941: UPDATE tdd08 SET a=a+1;
! 1942: }
! 1943: } {1 {foreign key constraint failed}}
! 1944:
! 1945: #-------------------------------------------------------------------------
! 1946: # Verify that ticket ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba
! 1947: # fixed.
! 1948: #
! 1949: do_test fkey2-ce7c13.1.1 {
! 1950: execsql {
! 1951: CREATE TABLE tce71(a INTEGER PRIMARY KEY, b);
! 1952: CREATE UNIQUE INDEX ice71 ON tce71(a,b);
! 1953: INSERT INTO tce71 VALUES(100,200);
! 1954: CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b));
! 1955: INSERT INTO tce72 VALUES(300,100,200);
! 1956: UPDATE tce71 set b = 200 where a = 100;
! 1957: SELECT * FROM tce71, tce72;
! 1958: }
! 1959: } {100 200 300 100 200}
! 1960: do_test fkey2-ce7c13.1.2 {
! 1961: catchsql {
! 1962: UPDATE tce71 set b = 201 where a = 100;
! 1963: }
! 1964: } {1 {foreign key constraint failed}}
! 1965: do_test fkey2-ce7c13.1.3 {
! 1966: catchsql {
! 1967: UPDATE tce71 set a = 101 where a = 100;
! 1968: }
! 1969: } {1 {foreign key constraint failed}}
! 1970: do_test fkey2-ce7c13.1.4 {
! 1971: execsql {
! 1972: CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b));
! 1973: INSERT INTO tce73 VALUES(100,200);
! 1974: CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b));
! 1975: INSERT INTO tce74 VALUES(300,100,200);
! 1976: UPDATE tce73 set b = 200 where a = 100;
! 1977: SELECT * FROM tce73, tce74;
! 1978: }
! 1979: } {100 200 300 100 200}
! 1980: do_test fkey2-ce7c13.1.5 {
! 1981: catchsql {
! 1982: UPDATE tce73 set b = 201 where a = 100;
! 1983: }
! 1984: } {1 {foreign key constraint failed}}
! 1985: do_test fkey2-ce7c13.1.6 {
! 1986: catchsql {
! 1987: UPDATE tce73 set a = 101 where a = 100;
! 1988: }
! 1989: } {1 {foreign key constraint failed}}
! 1990:
! 1991: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to fkey2.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