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