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 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19ifcapable {!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 88execsql { PRAGMA foreign_keys = on } 89 90set 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 106set 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 145do_test fkey2-1.1.0 { 146 execsql [string map {/D/ {}} $FkeySimpleSchema] 147} {} 148foreach {tn zSql res} $FkeySimpleTests { 149 do_test fkey2-1.1.$tn { catchsql $zSql } $res 150} 151drop_all_tables 152 153do_test fkey2-1.2.0 { 154 execsql [string map {/D/ {DEFERRABLE INITIALLY DEFERRED}} $FkeySimpleSchema] 155} {} 156foreach {tn zSql res} $FkeySimpleTests { 157 do_test fkey2-1.2.$tn { catchsql $zSql } $res 158} 159drop_all_tables 160 161do_test fkey2-1.3.0 { 162 execsql [string map {/D/ {}} $FkeySimpleSchema] 163 execsql { PRAGMA count_changes = 1 } 164} {} 165foreach {tn zSql res} $FkeySimpleTests { 166 if {$res == "0 {}"} { set res {0 1} } 167 do_test fkey2-1.3.$tn { catchsql $zSql } $res 168} 169execsql { PRAGMA count_changes = 0 } 170drop_all_tables 171 172do_test fkey2-1.4.0 { 173 execsql [string map {/D/ {}} $FkeySimpleSchema] 174 execsql { PRAGMA count_changes = 1 } 175} {} 176foreach {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} 182execsql { PRAGMA count_changes = 0 } 183drop_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. 188do_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} 197do_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. 202drop_all_tables 203do_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} 213do_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. 218drop_all_tables 219do_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. 231drop_all_tables 232do_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}} 240do_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# 253proc 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 267fkey2-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 278fkey2-2-test 1 0 "INSERT INTO node VALUES(1, 0)" FKV 279fkey2-2-test 2 0 "BEGIN" 280fkey2-2-test 3 1 "INSERT INTO node VALUES(1, 0)" 281fkey2-2-test 4 0 "UPDATE node SET parent = NULL" 282fkey2-2-test 5 0 "COMMIT" 283fkey2-2-test 6 0 "SELECT * FROM node" {1 {}} 284 285fkey2-2-test 7 0 "BEGIN" 286fkey2-2-test 8 1 "INSERT INTO leaf VALUES('a', 2)" 287fkey2-2-test 9 1 "INSERT INTO node VALUES(2, 0)" 288fkey2-2-test 10 0 "UPDATE node SET parent = 1 WHERE nodeid = 2" 289fkey2-2-test 11 0 "COMMIT" 290fkey2-2-test 12 0 "SELECT * FROM node" {1 {} 2 1} 291fkey2-2-test 13 0 "SELECT * FROM leaf" {a 2} 292 293fkey2-2-test 14 0 "BEGIN" 294fkey2-2-test 15 1 "DELETE FROM node WHERE nodeid = 2" 295fkey2-2-test 16 0 "INSERT INTO node VALUES(2, NULL)" 296fkey2-2-test 17 0 "COMMIT" 297fkey2-2-test 18 0 "SELECT * FROM node" {1 {} 2 {}} 298fkey2-2-test 19 0 "SELECT * FROM leaf" {a 2} 299 300fkey2-2-test 20 0 "BEGIN" 301fkey2-2-test 21 0 "INSERT INTO leaf VALUES('b', 1)" 302fkey2-2-test 22 0 "SAVEPOINT save" 303fkey2-2-test 23 0 "DELETE FROM node WHERE nodeid = 1" 304fkey2-2-test 24 0 "ROLLBACK TO save" 305fkey2-2-test 25 0 "COMMIT" 306fkey2-2-test 26 0 "SELECT * FROM node" {1 {} 2 {}} 307fkey2-2-test 27 0 "SELECT * FROM leaf" {a 2 b 1} 308 309fkey2-2-test 28 0 "BEGIN" 310fkey2-2-test 29 0 "INSERT INTO leaf VALUES('c', 1)" 311fkey2-2-test 30 0 "SAVEPOINT save" 312fkey2-2-test 31 0 "DELETE FROM node WHERE nodeid = 1" 313fkey2-2-test 32 1 "RELEASE save" 314fkey2-2-test 33 1 "DELETE FROM leaf WHERE cellid = 'b'" 315fkey2-2-test 34 0 "DELETE FROM leaf WHERE cellid = 'c'" 316fkey2-2-test 35 0 "COMMIT" 317fkey2-2-test 36 0 "SELECT * FROM node" {2 {}} 318fkey2-2-test 37 0 "SELECT * FROM leaf" {a 2} 319 320fkey2-2-test 38 0 "SAVEPOINT outer" 321fkey2-2-test 39 1 "INSERT INTO leaf VALUES('d', 3)" 322fkey2-2-test 40 1 "RELEASE outer" FKV 323fkey2-2-test 41 1 "INSERT INTO leaf VALUES('e', 3)" 324fkey2-2-test 42 0 "INSERT INTO node VALUES(3, 2)" 325fkey2-2-test 43 0 "RELEASE outer" 326 327fkey2-2-test 44 0 "SAVEPOINT outer" 328fkey2-2-test 45 1 "DELETE FROM node WHERE nodeid=3" 329fkey2-2-test 47 0 "INSERT INTO node VALUES(3, 2)" 330fkey2-2-test 48 0 "ROLLBACK TO outer" 331fkey2-2-test 49 0 "RELEASE outer" 332 333fkey2-2-test 50 0 "SAVEPOINT outer" 334fkey2-2-test 51 1 "INSERT INTO leaf VALUES('f', 4)" 335fkey2-2-test 52 1 "SAVEPOINT inner" 336fkey2-2-test 53 1 "INSERT INTO leaf VALUES('g', 4)" 337fkey2-2-test 54 1 "RELEASE outer" FKV 338fkey2-2-test 55 1 "ROLLBACK TO inner" 339fkey2-2-test 56 0 "COMMIT" FKV 340fkey2-2-test 57 0 "INSERT INTO node VALUES(4, NULL)" 341fkey2-2-test 58 0 "RELEASE outer" 342fkey2-2-test 59 0 "SELECT * FROM node" {2 {} 3 2 4 {}} 343fkey2-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# 351fkey2-2-test 61 0 "BEGIN" 352fkey2-2-test 62 0 "DELETE FROM leaf" 353fkey2-2-test 63 0 "DELETE FROM node" 354fkey2-2-test 64 1 "INSERT INTO leaf VALUES('a', 1)" 355fkey2-2-test 65 1 "INSERT INTO leaf VALUES('b', 2)" 356fkey2-2-test 66 1 "INSERT INTO leaf VALUES('c', 1)" 357do_test fkey2-2-test-67 { 358 catchsql "INSERT INTO node SELECT parent, 3 FROM leaf" 359} {1 {column nodeid is not unique}} 360fkey2-2-test 68 0 "COMMIT" FKV 361fkey2-2-test 69 1 "INSERT INTO node VALUES(1, NULL)" 362fkey2-2-test 70 0 "INSERT INTO node VALUES(2, NULL)" 363fkey2-2-test 71 0 "COMMIT" 364 365fkey2-2-test 72 0 "BEGIN" 366fkey2-2-test 73 1 "DELETE FROM node" 367fkey2-2-test 74 0 "INSERT INTO node(nodeid) SELECT DISTINCT parent FROM leaf" 368fkey2-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# 378drop_all_tables 379do_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} {} 392do_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} {} 399do_test fkey2-3.1.3 { 400 catchsql { UPDATE ab SET a = 5 } 401} {1 {constraint failed}} 402do_test fkey2-3.1.4 { 403 execsql { SELECT * FROM ab } 404} {1 b} 405do_test fkey2-3.1.4 { 406 execsql BEGIN; 407 catchsql { UPDATE ab SET a = 5 } 408} {1 {constraint failed}} 409do_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 414do_test fkey2-3.2.1 { 415 execsql BEGIN; 416 catchsql { DELETE FROM ab } 417} {1 {foreign key constraint failed}} 418do_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# 427drop_all_tables 428do_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} {} 448do_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} {} 456do_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} 463do_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} {} 471do_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# 483drop_all_tables 484ifcapable 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 513drop_all_tables 514ifcapable 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# 530drop_all_tables 531do_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} {} 537do_test fkey2-7.2 { 538 catchsql { INSERT INTO t2 VALUES(1, 'A'); } 539} {1 {foreign key constraint failed}} 540do_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} {} 547do_test fkey2-7.4 { 548 execsql { UPDATE t2 SET c = 2 } 549} {} 550do_test fkey2-7.5 { 551 catchsql { UPDATE t2 SET c = 3 } 552} {1 {foreign key constraint failed}} 553do_test fkey2-7.6 { 554 catchsql { DELETE FROM t1 WHERE a = 2 } 555} {1 {foreign key constraint failed}} 556do_test fkey2-7.7 { 557 execsql { DELETE FROM t1 WHERE a = 1 } 558} {} 559do_test fkey2-7.8 { 560 catchsql { UPDATE t1 SET a = 3 } 561} {1 {foreign key constraint failed}} 562do_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# 570drop_all_tables 571proc 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} 575fkey2-8-test 1 { PRAGMA foreign_keys = 0 } 0 576fkey2-8-test 2 { PRAGMA foreign_keys = 1 } 1 577fkey2-8-test 3 { BEGIN } 1 578fkey2-8-test 4 { PRAGMA foreign_keys = 0 } 1 579fkey2-8-test 5 { COMMIT } 1 580fkey2-8-test 6 { PRAGMA foreign_keys = 0 } 0 581fkey2-8-test 7 { BEGIN } 0 582fkey2-8-test 8 { PRAGMA foreign_keys = 1 } 0 583fkey2-8-test 9 { COMMIT } 0 584fkey2-8-test 10 { PRAGMA foreign_keys = 1 } 1 585fkey2-8-test 11 { PRAGMA foreign_keys = off } 0 586fkey2-8-test 12 { PRAGMA foreign_keys = on } 1 587fkey2-8-test 13 { PRAGMA foreign_keys = no } 0 588fkey2-8-test 14 { PRAGMA foreign_keys = yes } 1 589fkey2-8-test 15 { PRAGMA foreign_keys = false } 0 590fkey2-8-test 16 { PRAGMA foreign_keys = true } 1 591 592#------------------------------------------------------------------------- 593# The following tests, fkey2-9.*, test SET DEFAULT actions. 594# 595drop_all_tables 596do_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} {} 606do_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} 616do_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} 624do_test fkey2-9.1.4 { 625 execsql { SELECT * FROM t1 } 626} {2 two} 627do_test fkey2-9.1.5 { 628 catchsql { DELETE FROM t1 } 629} {1 {foreign key constraint failed}} 630 631do_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} {} 644do_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} 654do_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# 665set tn 0 666foreach 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# 690do_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}} 697do_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 {}} 704do_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}} 713do_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# 727drop_all_tables 728do_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# 743drop_all_tables 744do_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} {} 755do_test fkey2-12.1.2 { 756 execsql "BEGIN" 757 execsql "INSERT INTO t2 VALUES('two')" 758} {} 759do_test fkey2-12.1.3 { 760 execsql "UPDATE t1 SET b = 'four' WHERE b = 'one'" 761} {} 762do_test fkey2-12.1.4 { 763 catchsql "UPDATE t1 SET b = 'five' WHERE b = 'two'" 764} {1 {foreign key constraint failed}} 765do_test fkey2-12.1.5 { 766 execsql "DELETE FROM t1 WHERE b = 'two'" 767} {} 768do_test fkey2-12.1.6 { 769 catchsql "COMMIT" 770} {1 {foreign key constraint failed}} 771do_test fkey2-12.1.7 { 772 execsql { 773 INSERT INTO t1 VALUES(2, 'two'); 774 COMMIT; 775 } 776} {} 777 778drop_all_tables 779do_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} 797do_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} 804do_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}} 813do_test fkey2-12.2.4 { 814 execsql { 815 SELECT * FROM t1; 816 SELECT * FROM t2; 817 } 818} {A B a b} 819 820drop_all_tables 821do_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} {} 839do_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} 848do_test fkey2-12.3.3 { 849 catchsql { INSERT INTO down(c39, c38) VALUES('yes', 'no') } 850} {1 {foreign key constraint failed}} 851do_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}} 858do_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# 870drop_all_tables 871do_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} {} 879foreach {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} 904do_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} 911do_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# 927drop_all_tables 928ifcapable 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 1177do_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} {} 1184do_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} {} 1192do_test fkey-2.14.3.3 { 1193 catchsql { DROP TABLE t1 } 1194} {1 {foreign key constraint failed}} 1195do_test fkey-2.14.3.4 { 1196 execsql { 1197 DELETE FROM t2; 1198 DROP TABLE t1; 1199 } 1200} {} 1201do_test fkey-2.14.3.4 { 1202 catchsql { INSERT INTO t2 VALUES('x') } 1203} {1 {no such table: main.t1}} 1204do_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} {} 1211do_test fkey-2.14.3.6 { 1212 catchsql { DROP TABLE t1 } 1213} {1 {foreign key constraint failed}} 1214do_test fkey-2.14.3.7 { 1215 execsql { 1216 DROP TABLE t2; 1217 DROP TABLE t1; 1218 } 1219} {} 1220do_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}} 1227do_test fkey-2.14.3.9 { 1228 execsql { DROP TABLE cc } 1229} {} 1230do_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} {} 1246do_test fkey-2.14.3.11 { 1247 execsql { 1248 BEGIN; 1249 DROP TABLE cc; 1250 DROP TABLE pp; 1251 COMMIT; 1252 } 1253} {} 1254do_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} {} 1261do_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. 1271drop_all_tables 1272do_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} {} 1278do_test fkey-2.14.4.2 { 1279 execsql { 1280 DROP VIEW v; 1281 } 1282} {} 1283ifcapable 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# 1299drop_all_tables 1300proc 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} 1306do_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} {} 1316do_test fkey2-15.1.2 { 1317 execsqlS { INSERT INTO pp VALUES(3, 'three') } 1318} {0} 1319do_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} 1326do_test fkey2-15.1.4 { 1327 execsql { DELETE FROM cc WHERE x = 'see' } 1328 execsqlS { INSERT INTO pp VALUES(6, 'six') } 1329} {0} 1330do_test fkey2-15.1.5 { 1331 execsql COMMIT 1332} {} 1333do_test fkey2-15.1.6 { 1334 execsql BEGIN 1335 execsqlS { 1336 DELETE FROM cc WHERE x = 'neung'; 1337 ROLLBACK; 1338 } 1339} {1} 1340do_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# 1355foreach {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# 1404drop_all_tables 1405do_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} 1413do_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} 1417do_test fkey2-17.1.3 { 1418 sqlite3_step $STMT 1419} {SQLITE_CONSTRAINT} 1420do_test fkey2-17.1.4 { 1421 sqlite3_finalize $STMT 1422} {SQLITE_CONSTRAINT} 1423do_test fkey2-17.1.5 { 1424 execsql { 1425 INSERT INTO one VALUES(2, 3, 4); 1426 INSERT INTO one VALUES(3, 4, 5); 1427 INSERT INTO two VALUES(1, 2, 3); 1428 INSERT INTO two VALUES(2, 3, 4); 1429 INSERT INTO two VALUES(3, 4, 5); 1430 } 1431} {1 1 1 1 1} 1432do_test fkey2-17.1.6 { 1433 catchsql { 1434 BEGIN; 1435 INSERT INTO one VALUES(0, 0, 0); 1436 UPDATE two SET e=e+1, f=f+1; 1437 } 1438} {1 {foreign key constraint failed}} 1439do_test fkey2-17.1.7 { 1440 execsql { SELECT * FROM one } 1441} {1 2 3 2 3 4 3 4 5 0 0 0} 1442do_test fkey2-17.1.8 { 1443 execsql { SELECT * FROM two } 1444} {1 2 3 2 3 4 3 4 5} 1445do_test fkey2-17.1.9 { 1446 execsql COMMIT 1447} {} 1448do_test fkey2-17.1.10 { 1449 execsql { 1450 CREATE TABLE three( 1451 g, h, i, 1452 FOREIGN KEY(h, i) REFERENCES one(b, c) DEFERRABLE INITIALLY DEFERRED 1453 ); 1454 } 1455} {} 1456do_test fkey2-17.1.11 { 1457 set STMT [sqlite3_prepare_v2 db "INSERT INTO three VALUES(7, 8, 9)" -1 dummy] 1458 sqlite3_step $STMT 1459} {SQLITE_ROW} 1460do_test fkey2-17.1.12 { 1461 sqlite3_column_text $STMT 0 1462} {1} 1463do_test fkey2-17.1.13 { 1464 sqlite3_step $STMT 1465} {SQLITE_CONSTRAINT} 1466do_test fkey2-17.1.14 { 1467 sqlite3_finalize $STMT 1468} {SQLITE_CONSTRAINT} 1469 1470drop_all_tables 1471do_test fkey2-17.2.1 { 1472 execsql { 1473 CREATE TABLE high("a'b!" PRIMARY KEY, b); 1474 CREATE TABLE low( 1475 c, 1476 "d&6" REFERENCES high ON UPDATE CASCADE ON DELETE CASCADE 1477 ); 1478 } 1479} {} 1480do_test fkey2-17.2.2 { 1481 execsql { 1482 INSERT INTO high VALUES('a', 'b'); 1483 INSERT INTO low VALUES('b', 'a'); 1484 } 1485 db changes 1486} {1} 1487set nTotal [db total_changes] 1488do_test fkey2-17.2.3 { 1489 execsql { UPDATE high SET "a'b!" = 'c' } 1490} {1} 1491do_test fkey2-17.2.4 { 1492 db changes 1493} {1} 1494do_test fkey2-17.2.5 { 1495 expr [db total_changes] - $nTotal 1496} {2} 1497do_test fkey2-17.2.6 { 1498 execsql { SELECT * FROM high ; SELECT * FROM low } 1499} {c b b c} 1500do_test fkey2-17.2.7 { 1501 execsql { DELETE FROM high } 1502} {1} 1503do_test fkey2-17.2.8 { 1504 db changes 1505} {1} 1506do_test fkey2-17.2.9 { 1507 expr [db total_changes] - $nTotal 1508} {4} 1509do_test fkey2-17.2.10 { 1510 execsql { SELECT * FROM high ; SELECT * FROM low } 1511} {} 1512execsql { PRAGMA count_changes = 0 } 1513 1514#------------------------------------------------------------------------- 1515# Test that the authorization callback works. 1516# 1517 1518ifcapable auth { 1519 do_test fkey2-18.1 { 1520 execsql { 1521 CREATE TABLE long(a, b PRIMARY KEY, c); 1522 CREATE TABLE short(d, e, f REFERENCES long); 1523 CREATE TABLE mid(g, h, i REFERENCES long DEFERRABLE INITIALLY DEFERRED); 1524 } 1525 } {} 1526 1527 proc auth {args} {eval lappend ::authargs $args ; return SQLITE_OK} 1528 db auth auth 1529 1530 # An insert on the parent table must read the child key of any deferred 1531 # foreign key constraints. But not the child key of immediate constraints. 1532 set authargs {} 1533 do_test fkey2-18.2 { 1534 execsql { INSERT INTO long VALUES(1, 2, 3) } 1535 set authargs 1536 } {SQLITE_INSERT long {} main {} SQLITE_READ mid i main {}} 1537 1538 # An insert on the child table of an immediate constraint must read the 1539 # parent key columns (to see if it is a violation or not). 1540 set authargs {} 1541 do_test fkey2-18.3 { 1542 execsql { INSERT INTO short VALUES(1, 3, 2) } 1543 set authargs 1544 } {SQLITE_INSERT short {} main {} SQLITE_READ long b main {}} 1545 1546 # As must an insert on the child table of a deferred constraint. 1547 set authargs {} 1548 do_test fkey2-18.4 { 1549 execsql { INSERT INTO mid VALUES(1, 3, 2) } 1550 set authargs 1551 } {SQLITE_INSERT mid {} main {} SQLITE_READ long b main {}} 1552 1553 do_test fkey2-18.5 { 1554 execsql { 1555 CREATE TABLE nought(a, b PRIMARY KEY, c); 1556 CREATE TABLE cross(d, e, f, 1557 FOREIGN KEY(e) REFERENCES nought(b) ON UPDATE CASCADE 1558 ); 1559 } 1560 execsql { INSERT INTO nought VALUES(2, 1, 2) } 1561 execsql { INSERT INTO cross VALUES(0, 1, 0) } 1562 set authargs [list] 1563 execsql { UPDATE nought SET b = 5 } 1564 set authargs 1565 } {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 {}} 1566 1567 do_test fkey2-18.6 { 1568 execsql {SELECT * FROM cross} 1569 } {0 5 0} 1570 1571 do_test fkey2-18.7 { 1572 execsql { 1573 CREATE TABLE one(a INTEGER PRIMARY KEY, b); 1574 CREATE TABLE two(b, c REFERENCES one); 1575 INSERT INTO one VALUES(101, 102); 1576 } 1577 set authargs [list] 1578 execsql { INSERT INTO two VALUES(100, 101); } 1579 set authargs 1580 } {SQLITE_INSERT two {} main {} SQLITE_READ one a main {}} 1581 1582 # Return SQLITE_IGNORE to requests to read from the parent table. This 1583 # causes inserts of non-NULL keys into the child table to fail. 1584 # 1585 rename auth {} 1586 proc auth {args} { 1587 if {[lindex $args 1] == "long"} {return SQLITE_IGNORE} 1588 return SQLITE_OK 1589 } 1590 do_test fkey2-18.8 { 1591 catchsql { INSERT INTO short VALUES(1, 3, 2) } 1592 } {1 {foreign key constraint failed}} 1593 do_test fkey2-18.9 { 1594 execsql { INSERT INTO short VALUES(1, 3, NULL) } 1595 } {} 1596 do_test fkey2-18.10 { 1597 execsql { SELECT * FROM short } 1598 } {1 3 2 1 3 {}} 1599 do_test fkey2-18.11 { 1600 catchsql { UPDATE short SET f = 2 WHERE f IS NULL } 1601 } {1 {foreign key constraint failed}} 1602 1603 db auth {} 1604 unset authargs 1605} 1606 1607 1608do_test fkey2-19.1 { 1609 execsql { 1610 CREATE TABLE main(id INTEGER PRIMARY KEY); 1611 CREATE TABLE sub(id INT REFERENCES main(id)); 1612 INSERT INTO main VALUES(1); 1613 INSERT INTO main VALUES(2); 1614 INSERT INTO sub VALUES(2); 1615 } 1616} {} 1617do_test fkey2-19.2 { 1618 set S [sqlite3_prepare_v2 db "DELETE FROM main WHERE id = ?" -1 dummy] 1619 sqlite3_bind_int $S 1 2 1620 sqlite3_step $S 1621} {SQLITE_CONSTRAINT} 1622do_test fkey2-19.3 { 1623 sqlite3_reset $S 1624} {SQLITE_CONSTRAINT} 1625do_test fkey2-19.4 { 1626 sqlite3_bind_int $S 1 1 1627 sqlite3_step $S 1628} {SQLITE_DONE} 1629do_test fkey2-19.4 { 1630 sqlite3_finalize $S 1631} {SQLITE_OK} 1632 1633drop_all_tables 1634do_test fkey2-20.1 { 1635 execsql { 1636 CREATE TABLE pp(a PRIMARY KEY, b); 1637 CREATE TABLE cc(c PRIMARY KEY, d REFERENCES pp); 1638 } 1639} {} 1640 1641foreach {tn insert} { 1642 1 "INSERT" 1643 2 "INSERT OR IGNORE" 1644 3 "INSERT OR ABORT" 1645 4 "INSERT OR ROLLBACK" 1646 5 "INSERT OR REPLACE" 1647 6 "INSERT OR FAIL" 1648} { 1649 do_test fkey2-20.2.$tn.1 { 1650 catchsql "$insert INTO cc VALUES(1, 2)" 1651 } {1 {foreign key constraint failed}} 1652 do_test fkey2-20.2.$tn.2 { 1653 execsql { SELECT * FROM cc } 1654 } {} 1655 do_test fkey2-20.2.$tn.3 { 1656 execsql { 1657 BEGIN; 1658 INSERT INTO pp VALUES(2, 'two'); 1659 INSERT INTO cc VALUES(1, 2); 1660 } 1661 catchsql "$insert INTO cc VALUES(3, 4)" 1662 } {1 {foreign key constraint failed}} 1663 do_test fkey2-20.2.$tn.4 { 1664 execsql { COMMIT ; SELECT * FROM cc } 1665 } {1 2} 1666 do_test fkey2-20.2.$tn.5 { 1667 execsql { DELETE FROM cc ; DELETE FROM pp } 1668 } {} 1669} 1670 1671foreach {tn update} { 1672 1 "UPDATE" 1673 2 "UPDATE OR IGNORE" 1674 3 "UPDATE OR ABORT" 1675 4 "UPDATE OR ROLLBACK" 1676 5 "UPDATE OR REPLACE" 1677 6 "UPDATE OR FAIL" 1678} { 1679 do_test fkey2-20.3.$tn.1 { 1680 execsql { 1681 INSERT INTO pp VALUES(2, 'two'); 1682 INSERT INTO cc VALUES(1, 2); 1683 } 1684 } {} 1685 do_test fkey2-20.3.$tn.2 { 1686 catchsql "$update pp SET a = 1" 1687 } {1 {foreign key constraint failed}} 1688 do_test fkey2-20.3.$tn.3 { 1689 execsql { SELECT * FROM pp } 1690 } {2 two} 1691 do_test fkey2-20.3.$tn.4 { 1692 catchsql "$update cc SET d = 1" 1693 } {1 {foreign key constraint failed}} 1694 do_test fkey2-20.3.$tn.5 { 1695 execsql { SELECT * FROM cc } 1696 } {1 2} 1697 do_test fkey2-20.3.$tn.6 { 1698 execsql { 1699 BEGIN; 1700 INSERT INTO pp VALUES(3, 'three'); 1701 } 1702 catchsql "$update pp SET a = 1 WHERE a = 2" 1703 } {1 {foreign key constraint failed}} 1704 do_test fkey2-20.3.$tn.7 { 1705 execsql { COMMIT ; SELECT * FROM pp } 1706 } {2 two 3 three} 1707 do_test fkey2-20.3.$tn.8 { 1708 execsql { 1709 BEGIN; 1710 INSERT INTO cc VALUES(2, 2); 1711 } 1712 catchsql "$update cc SET d = 1 WHERE c = 1" 1713 } {1 {foreign key constraint failed}} 1714 do_test fkey2-20.3.$tn.9 { 1715 execsql { COMMIT ; SELECT * FROM cc } 1716 } {1 2 2 2} 1717 do_test fkey2-20.3.$tn.10 { 1718 execsql { DELETE FROM cc ; DELETE FROM pp } 1719 } {} 1720} 1721 1722#------------------------------------------------------------------------- 1723# The following block of tests, those prefixed with "fkey2-genfkey.", are 1724# the same tests that were used to test the ".genfkey" command provided 1725# by the shell tool. So these tests show that the built-in foreign key 1726# implementation is more or less compatible with the triggers generated 1727# by genfkey. 1728# 1729drop_all_tables 1730do_test fkey2-genfkey.1.1 { 1731 execsql { 1732 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); 1733 CREATE TABLE t2(e REFERENCES t1, f); 1734 CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c)); 1735 } 1736} {} 1737do_test fkey2-genfkey.1.2 { 1738 catchsql { INSERT INTO t2 VALUES(1, 2) } 1739} {1 {foreign key constraint failed}} 1740do_test fkey2-genfkey.1.3 { 1741 execsql { 1742 INSERT INTO t1 VALUES(1, 2, 3); 1743 INSERT INTO t2 VALUES(1, 2); 1744 } 1745} {} 1746do_test fkey2-genfkey.1.4 { 1747 execsql { INSERT INTO t2 VALUES(NULL, 3) } 1748} {} 1749do_test fkey2-genfkey.1.5 { 1750 catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL } 1751} {1 {foreign key constraint failed}} 1752do_test fkey2-genfkey.1.6 { 1753 execsql { UPDATE t2 SET e = 1 WHERE e IS NULL } 1754} {} 1755do_test fkey2-genfkey.1.7 { 1756 execsql { UPDATE t2 SET e = NULL WHERE f = 3 } 1757} {} 1758do_test fkey2-genfkey.1.8 { 1759 catchsql { UPDATE t1 SET a = 10 } 1760} {1 {foreign key constraint failed}} 1761do_test fkey2-genfkey.1.9 { 1762 catchsql { UPDATE t1 SET a = NULL } 1763} {1 {datatype mismatch}} 1764do_test fkey2-genfkey.1.10 { 1765 catchsql { DELETE FROM t1 } 1766} {1 {foreign key constraint failed}} 1767do_test fkey2-genfkey.1.11 { 1768 execsql { UPDATE t2 SET e = NULL } 1769} {} 1770do_test fkey2-genfkey.1.12 { 1771 execsql { 1772 UPDATE t1 SET a = 10; 1773 DELETE FROM t1; 1774 DELETE FROM t2; 1775 } 1776} {} 1777do_test fkey2-genfkey.1.13 { 1778 execsql { 1779 INSERT INTO t3 VALUES(1, NULL, NULL); 1780 INSERT INTO t3 VALUES(1, 2, NULL); 1781 INSERT INTO t3 VALUES(1, NULL, 3); 1782 } 1783} {} 1784do_test fkey2-genfkey.1.14 { 1785 catchsql { INSERT INTO t3 VALUES(3, 1, 4) } 1786} {1 {foreign key constraint failed}} 1787do_test fkey2-genfkey.1.15 { 1788 execsql { 1789 INSERT INTO t1 VALUES(1, 1, 4); 1790 INSERT INTO t3 VALUES(3, 1, 4); 1791 } 1792} {} 1793do_test fkey2-genfkey.1.16 { 1794 catchsql { DELETE FROM t1 } 1795} {1 {foreign key constraint failed}} 1796do_test fkey2-genfkey.1.17 { 1797 catchsql { UPDATE t1 SET b = 10} 1798} {1 {foreign key constraint failed}} 1799do_test fkey2-genfkey.1.18 { 1800 execsql { UPDATE t1 SET a = 10} 1801} {} 1802do_test fkey2-genfkey.1.19 { 1803 catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3} 1804} {1 {foreign key constraint failed}} 1805 1806drop_all_tables 1807do_test fkey2-genfkey.2.1 { 1808 execsql { 1809 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); 1810 CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f); 1811 CREATE TABLE t3(g, h, i, 1812 FOREIGN KEY (h, i) 1813 REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE 1814 ); 1815 } 1816} {} 1817do_test fkey2-genfkey.2.2 { 1818 execsql { 1819 INSERT INTO t1 VALUES(1, 2, 3); 1820 INSERT INTO t1 VALUES(4, 5, 6); 1821 INSERT INTO t2 VALUES(1, 'one'); 1822 INSERT INTO t2 VALUES(4, 'four'); 1823 } 1824} {} 1825do_test fkey2-genfkey.2.3 { 1826 execsql { 1827 UPDATE t1 SET a = 2 WHERE a = 1; 1828 SELECT * FROM t2; 1829 } 1830} {2 one 4 four} 1831do_test fkey2-genfkey.2.4 { 1832 execsql { 1833 DELETE FROM t1 WHERE a = 4; 1834 SELECT * FROM t2; 1835 } 1836} {2 one} 1837 1838do_test fkey2-genfkey.2.5 { 1839 execsql { 1840 INSERT INTO t3 VALUES('hello', 2, 3); 1841 UPDATE t1 SET c = 2; 1842 SELECT * FROM t3; 1843 } 1844} {hello 2 2} 1845do_test fkey2-genfkey.2.6 { 1846 execsql { 1847 DELETE FROM t1; 1848 SELECT * FROM t3; 1849 } 1850} {} 1851 1852drop_all_tables 1853do_test fkey2-genfkey.3.1 { 1854 execsql { 1855 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b)); 1856 CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f); 1857 CREATE TABLE t3(g, h, i, 1858 FOREIGN KEY (h, i) 1859 REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL 1860 ); 1861 } 1862} {} 1863do_test fkey2-genfkey.3.2 { 1864 execsql { 1865 INSERT INTO t1 VALUES(1, 2, 3); 1866 INSERT INTO t1 VALUES(4, 5, 6); 1867 INSERT INTO t2 VALUES(1, 'one'); 1868 INSERT INTO t2 VALUES(4, 'four'); 1869 } 1870} {} 1871do_test fkey2-genfkey.3.3 { 1872 execsql { 1873 UPDATE t1 SET a = 2 WHERE a = 1; 1874 SELECT * FROM t2; 1875 } 1876} {{} one 4 four} 1877do_test fkey2-genfkey.3.4 { 1878 execsql { 1879 DELETE FROM t1 WHERE a = 4; 1880 SELECT * FROM t2; 1881 } 1882} {{} one {} four} 1883do_test fkey2-genfkey.3.5 { 1884 execsql { 1885 INSERT INTO t3 VALUES('hello', 2, 3); 1886 UPDATE t1 SET c = 2; 1887 SELECT * FROM t3; 1888 } 1889} {hello {} {}} 1890do_test fkey2-genfkey.3.6 { 1891 execsql { 1892 UPDATE t3 SET h = 2, i = 2; 1893 DELETE FROM t1; 1894 SELECT * FROM t3; 1895 } 1896} {hello {} {}} 1897 1898#------------------------------------------------------------------------- 1899# Verify that ticket dd08e5a988d00decc4a543daa8dbbfab9c577ad8 has been 1900# fixed. 1901# 1902do_test fkey2-dd08e5.1.1 { 1903 execsql { 1904 PRAGMA foreign_keys=ON; 1905 CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b); 1906 CREATE UNIQUE INDEX idd08 ON tdd08(a,b); 1907 INSERT INTO tdd08 VALUES(200,300); 1908 1909 CREATE TABLE tdd08_b(w,x,y, FOREIGN KEY(x,y) REFERENCES tdd08(a,b)); 1910 INSERT INTO tdd08_b VALUES(100,200,300); 1911 } 1912} {} 1913do_test fkey2-dd08e5.1.2 { 1914 catchsql { 1915 DELETE FROM tdd08; 1916 } 1917} {1 {foreign key constraint failed}} 1918do_test fkey2-dd08e5.1.3 { 1919 execsql { 1920 SELECT * FROM tdd08; 1921 } 1922} {200 300} 1923do_test fkey2-dd08e5.1.4 { 1924 catchsql { 1925 INSERT INTO tdd08_b VALUES(400,500,300); 1926 } 1927} {1 {foreign key constraint failed}} 1928do_test fkey2-dd08e5.1.5 { 1929 catchsql { 1930 UPDATE tdd08_b SET x=x+1; 1931 } 1932} {1 {foreign key constraint failed}} 1933do_test fkey2-dd08e5.1.6 { 1934 catchsql { 1935 UPDATE tdd08 SET a=a+1; 1936 } 1937} {1 {foreign key constraint failed}} 1938 1939#------------------------------------------------------------------------- 1940# Verify that ticket ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba 1941# fixed. 1942# 1943do_test fkey2-ce7c13.1.1 { 1944 execsql { 1945 CREATE TABLE tce71(a INTEGER PRIMARY KEY, b); 1946 CREATE UNIQUE INDEX ice71 ON tce71(a,b); 1947 INSERT INTO tce71 VALUES(100,200); 1948 CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b)); 1949 INSERT INTO tce72 VALUES(300,100,200); 1950 UPDATE tce71 set b = 200 where a = 100; 1951 SELECT * FROM tce71, tce72; 1952 } 1953} {100 200 300 100 200} 1954do_test fkey2-ce7c13.1.2 { 1955 catchsql { 1956 UPDATE tce71 set b = 201 where a = 100; 1957 } 1958} {1 {foreign key constraint failed}} 1959do_test fkey2-ce7c13.1.3 { 1960 catchsql { 1961 UPDATE tce71 set a = 101 where a = 100; 1962 } 1963} {1 {foreign key constraint failed}} 1964do_test fkey2-ce7c13.1.4 { 1965 execsql { 1966 CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b)); 1967 INSERT INTO tce73 VALUES(100,200); 1968 CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b)); 1969 INSERT INTO tce74 VALUES(300,100,200); 1970 UPDATE tce73 set b = 200 where a = 100; 1971 SELECT * FROM tce73, tce74; 1972 } 1973} {100 200 300 100 200} 1974do_test fkey2-ce7c13.1.5 { 1975 catchsql { 1976 UPDATE tce73 set b = 201 where a = 100; 1977 } 1978} {1 {foreign key constraint failed}} 1979do_test fkey2-ce7c13.1.6 { 1980 catchsql { 1981 UPDATE tce73 set a = 101 where a = 100; 1982 } 1983} {1 {foreign key constraint failed}} 1984 1985finish_test 1986