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 {column nodeid is not unique}} 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 {constraint failed}} 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 {constraint failed}} 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} 1436ifcapable autoreset { 1437 do_test fkey2-17.1.3 { 1438 sqlite3_step $STMT 1439 } {SQLITE_CONSTRAINT} 1440} else { 1441 do_test fkey2-17.1.3 { 1442 sqlite3_step $STMT 1443 } {SQLITE_MISUSE} 1444} 1445do_test fkey2-17.1.4 { 1446 sqlite3_finalize $STMT 1447} {SQLITE_CONSTRAINT} 1448do_test fkey2-17.1.5 { 1449 execsql { 1450 INSERT INTO one VALUES(2, 3, 4); 1451 INSERT INTO one VALUES(3, 4, 5); 1452 INSERT INTO two VALUES(1, 2, 3); 1453 INSERT INTO two VALUES(2, 3, 4); 1454 INSERT INTO two VALUES(3, 4, 5); 1455 } 1456} {1 1 1 1 1} 1457do_test fkey2-17.1.6 { 1458 catchsql { 1459 BEGIN; 1460 INSERT INTO one VALUES(0, 0, 0); 1461 UPDATE two SET e=e+1, f=f+1; 1462 } 1463} {1 {foreign key constraint failed}} 1464do_test fkey2-17.1.7 { 1465 execsql { SELECT * FROM one } 1466} {1 2 3 2 3 4 3 4 5 0 0 0} 1467do_test fkey2-17.1.8 { 1468 execsql { SELECT * FROM two } 1469} {1 2 3 2 3 4 3 4 5} 1470do_test fkey2-17.1.9 { 1471 execsql COMMIT 1472} {} 1473do_test fkey2-17.1.10 { 1474 execsql { 1475 CREATE TABLE three( 1476 g, h, i, 1477 FOREIGN KEY(h, i) REFERENCES one(b, c) DEFERRABLE INITIALLY DEFERRED 1478 ); 1479 } 1480} {} 1481do_test fkey2-17.1.11 { 1482 set STMT [sqlite3_prepare_v2 db "INSERT INTO three VALUES(7, 8, 9)" -1 dummy] 1483 sqlite3_step $STMT 1484} {SQLITE_ROW} 1485do_test fkey2-17.1.12 { 1486 sqlite3_column_text $STMT 0 1487} {1} 1488do_test fkey2-17.1.13 { 1489 sqlite3_step $STMT 1490} {SQLITE_CONSTRAINT} 1491do_test fkey2-17.1.14 { 1492 sqlite3_finalize $STMT 1493} {SQLITE_CONSTRAINT} 1494 1495drop_all_tables 1496do_test fkey2-17.2.1 { 1497 execsql { 1498 CREATE TABLE high("a'b!" PRIMARY KEY, b); 1499 CREATE TABLE low( 1500 c, 1501 "d&6" REFERENCES high ON UPDATE CASCADE ON DELETE CASCADE 1502 ); 1503 } 1504} {} 1505do_test fkey2-17.2.2 { 1506 execsql { 1507 INSERT INTO high VALUES('a', 'b'); 1508 INSERT INTO low VALUES('b', 'a'); 1509 } 1510 db changes 1511} {1} 1512set nTotal [db total_changes] 1513do_test fkey2-17.2.3 { 1514 execsql { UPDATE high SET "a'b!" = 'c' } 1515} {1} 1516do_test fkey2-17.2.4 { 1517 db changes 1518} {1} 1519do_test fkey2-17.2.5 { 1520 expr [db total_changes] - $nTotal 1521} {2} 1522do_test fkey2-17.2.6 { 1523 execsql { SELECT * FROM high ; SELECT * FROM low } 1524} {c b b c} 1525do_test fkey2-17.2.7 { 1526 execsql { DELETE FROM high } 1527} {1} 1528do_test fkey2-17.2.8 { 1529 db changes 1530} {1} 1531do_test fkey2-17.2.9 { 1532 expr [db total_changes] - $nTotal 1533} {4} 1534do_test fkey2-17.2.10 { 1535 execsql { SELECT * FROM high ; SELECT * FROM low } 1536} {} 1537execsql { PRAGMA count_changes = 0 } 1538 1539#------------------------------------------------------------------------- 1540# Test that the authorization callback works. 1541# 1542 1543ifcapable auth { 1544 do_test fkey2-18.1 { 1545 execsql { 1546 CREATE TABLE long(a, b PRIMARY KEY, c); 1547 CREATE TABLE short(d, e, f REFERENCES long); 1548 CREATE TABLE mid(g, h, i REFERENCES long DEFERRABLE INITIALLY DEFERRED); 1549 } 1550 } {} 1551 1552 proc auth {args} {eval lappend ::authargs $args ; return SQLITE_OK} 1553 db auth auth 1554 1555 # An insert on the parent table must read the child key of any deferred 1556 # foreign key constraints. But not the child key of immediate constraints. 1557 set authargs {} 1558 do_test fkey2-18.2 { 1559 execsql { INSERT INTO long VALUES(1, 2, 3) } 1560 set authargs 1561 } {SQLITE_INSERT long {} main {} SQLITE_READ mid i main {}} 1562 1563 # An insert on the child table of an immediate constraint must read the 1564 # parent key columns (to see if it is a violation or not). 1565 set authargs {} 1566 do_test fkey2-18.3 { 1567 execsql { INSERT INTO short VALUES(1, 3, 2) } 1568 set authargs 1569 } {SQLITE_INSERT short {} main {} SQLITE_READ long b main {}} 1570 1571 # As must an insert on the child table of a deferred constraint. 1572 set authargs {} 1573 do_test fkey2-18.4 { 1574 execsql { INSERT INTO mid VALUES(1, 3, 2) } 1575 set authargs 1576 } {SQLITE_INSERT mid {} main {} SQLITE_READ long b main {}} 1577 1578 do_test fkey2-18.5 { 1579 execsql { 1580 CREATE TABLE nought(a, b PRIMARY KEY, c); 1581 CREATE TABLE cross(d, e, f, 1582 FOREIGN KEY(e) REFERENCES nought(b) ON UPDATE CASCADE 1583 ); 1584 } 1585 execsql { INSERT INTO nought VALUES(2, 1, 2) } 1586 execsql { INSERT INTO cross VALUES(0, 1, 0) } 1587 set authargs [list] 1588 execsql { UPDATE nought SET b = 5 } 1589 set authargs 1590 } {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 {}} 1591 1592 do_test fkey2-18.6 { 1593 execsql {SELECT * FROM cross} 1594 } {0 5 0} 1595 1596 do_test fkey2-18.7 { 1597 execsql { 1598 CREATE TABLE one(a INTEGER PRIMARY KEY, b); 1599 CREATE TABLE two(b, c REFERENCES one); 1600 INSERT INTO one VALUES(101, 102); 1601 } 1602 set authargs [list] 1603 execsql { INSERT INTO two VALUES(100, 101); } 1604 set authargs 1605 } {SQLITE_INSERT two {} main {} SQLITE_READ one a main {}} 1606 1607 # Return SQLITE_IGNORE to requests to read from the parent table. This 1608 # causes inserts of non-NULL keys into the child table to fail. 1609 # 1610 rename auth {} 1611 proc auth {args} { 1612 if {[lindex $args 1] == "long"} {return SQLITE_IGNORE} 1613 return SQLITE_OK 1614 } 1615 do_test fkey2-18.8 { 1616 catchsql { INSERT INTO short VALUES(1, 3, 2) } 1617 } {1 {foreign key constraint failed}} 1618 do_test fkey2-18.9 { 1619 execsql { INSERT INTO short VALUES(1, 3, NULL) } 1620 } {} 1621 do_test fkey2-18.10 { 1622 execsql { SELECT * FROM short } 1623 } {1 3 2 1 3 {}} 1624 do_test fkey2-18.11 { 1625 catchsql { UPDATE short SET f = 2 WHERE f IS NULL } 1626 } {1 {foreign key constraint failed}} 1627 1628 db auth {} 1629 unset authargs 1630} 1631 1632 1633do_test fkey2-19.1 { 1634 execsql { 1635 CREATE TABLE main(id INTEGER PRIMARY KEY); 1636 CREATE TABLE sub(id INT REFERENCES main(id)); 1637 INSERT INTO main VALUES(1); 1638 INSERT INTO main VALUES(2); 1639 INSERT INTO sub VALUES(2); 1640 } 1641} {} 1642do_test fkey2-19.2 { 1643 set S [sqlite3_prepare_v2 db "DELETE FROM main WHERE id = ?" -1 dummy] 1644 sqlite3_bind_int $S 1 2 1645 sqlite3_step $S 1646} {SQLITE_CONSTRAINT} 1647do_test fkey2-19.3 { 1648 sqlite3_reset $S 1649} {SQLITE_CONSTRAINT} 1650do_test fkey2-19.4 { 1651 sqlite3_bind_int $S 1 1 1652 sqlite3_step $S 1653} {SQLITE_DONE} 1654do_test fkey2-19.4 { 1655 sqlite3_finalize $S 1656} {SQLITE_OK} 1657 1658drop_all_tables 1659do_test fkey2-20.1 { 1660 execsql { 1661 CREATE TABLE pp(a PRIMARY KEY, b); 1662 CREATE TABLE cc(c PRIMARY KEY, d REFERENCES pp); 1663 } 1664} {} 1665 1666foreach {tn insert} { 1667 1 "INSERT" 1668 2 "INSERT OR IGNORE" 1669 3 "INSERT OR ABORT" 1670 4 "INSERT OR ROLLBACK" 1671 5 "INSERT OR REPLACE" 1672 6 "INSERT OR FAIL" 1673} { 1674 do_test fkey2-20.2.$tn.1 { 1675 catchsql "$insert INTO cc VALUES(1, 2)" 1676 } {1 {foreign key constraint failed}} 1677 do_test fkey2-20.2.$tn.2 { 1678 execsql { SELECT * FROM cc } 1679 } {} 1680 do_test fkey2-20.2.$tn.3 { 1681 execsql { 1682 BEGIN; 1683 INSERT INTO pp VALUES(2, 'two'); 1684 INSERT INTO cc VALUES(1, 2); 1685 } 1686 catchsql "$insert INTO cc VALUES(3, 4)" 1687 } {1 {foreign key constraint failed}} 1688 do_test fkey2-20.2.$tn.4 { 1689 execsql { COMMIT ; SELECT * FROM cc } 1690 } {1 2} 1691 do_test fkey2-20.2.$tn.5 { 1692 execsql { DELETE FROM cc ; DELETE FROM pp } 1693 } {} 1694} 1695 1696foreach {tn update} { 1697 1 "UPDATE" 1698 2 "UPDATE OR IGNORE" 1699 3 "UPDATE OR ABORT" 1700 4 "UPDATE OR ROLLBACK" 1701 5 "UPDATE OR REPLACE" 1702 6 "UPDATE OR FAIL" 1703} { 1704 do_test fkey2-20.3.$tn.1 { 1705 execsql { 1706 INSERT INTO pp VALUES(2, 'two'); 1707 INSERT INTO cc VALUES(1, 2); 1708 } 1709 } {} 1710 do_test fkey2-20.3.$tn.2 { 1711 catchsql "$update pp SET a = 1" 1712 } {1 {foreign key constraint failed}} 1713 do_test fkey2-20.3.$tn.3 { 1714 execsql { SELECT * FROM pp } 1715 } {2 two} 1716 do_test fkey2-20.3.$tn.4 { 1717 catchsql "$update cc SET d = 1" 1718 } {1 {foreign key constraint failed}} 1719 do_test fkey2-20.3.$tn.5 { 1720 execsql { SELECT * FROM cc } 1721 } {1 2} 1722 do_test fkey2-20.3.$tn.6 { 1723 execsql { 1724 BEGIN; 1725 INSERT INTO pp VALUES(3, 'three'); 1726 } 1727 catchsql "$update pp SET a = 1 WHERE a = 2" 1728 } {1 {foreign key constraint failed}} 1729 do_test fkey2-20.3.$tn.7 { 1730 execsql { COMMIT ; SELECT * FROM pp } 1731 } {2 two 3 three} 1732 do_test fkey2-20.3.$tn.8 { 1733 execsql { 1734 BEGIN; 1735 INSERT INTO cc VALUES(2, 2); 1736 } 1737 catchsql "$update cc SET d = 1 WHERE c = 1" 1738 } {1 {foreign key constraint failed}} 1739 do_test fkey2-20.3.$tn.9 { 1740 execsql { COMMIT ; SELECT * FROM cc } 1741 } {1 2 2 2} 1742 do_test fkey2-20.3.$tn.10 { 1743 execsql { DELETE FROM cc ; DELETE FROM pp } 1744 } {} 1745} 1746 1747#------------------------------------------------------------------------- 1748# The following block of tests, those prefixed with "fkey2-genfkey.", are 1749# the same tests that were used to test the ".genfkey" command provided 1750# by the shell tool. So these tests show that the built-in foreign key 1751# implementation is more or less compatible with the triggers generated 1752# by genfkey. 1753# 1754drop_all_tables 1755do_test fkey2-genfkey.1.1 { 1756 execsql { 1757 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); 1758 CREATE TABLE t2(e REFERENCES t1, f); 1759 CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c)); 1760 } 1761} {} 1762do_test fkey2-genfkey.1.2 { 1763 catchsql { INSERT INTO t2 VALUES(1, 2) } 1764} {1 {foreign key constraint failed}} 1765do_test fkey2-genfkey.1.3 { 1766 execsql { 1767 INSERT INTO t1 VALUES(1, 2, 3); 1768 INSERT INTO t2 VALUES(1, 2); 1769 } 1770} {} 1771do_test fkey2-genfkey.1.4 { 1772 execsql { INSERT INTO t2 VALUES(NULL, 3) } 1773} {} 1774do_test fkey2-genfkey.1.5 { 1775 catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL } 1776} {1 {foreign key constraint failed}} 1777do_test fkey2-genfkey.1.6 { 1778 execsql { UPDATE t2 SET e = 1 WHERE e IS NULL } 1779} {} 1780do_test fkey2-genfkey.1.7 { 1781 execsql { UPDATE t2 SET e = NULL WHERE f = 3 } 1782} {} 1783do_test fkey2-genfkey.1.8 { 1784 catchsql { UPDATE t1 SET a = 10 } 1785} {1 {foreign key constraint failed}} 1786do_test fkey2-genfkey.1.9 { 1787 catchsql { UPDATE t1 SET a = NULL } 1788} {1 {datatype mismatch}} 1789do_test fkey2-genfkey.1.10 { 1790 catchsql { DELETE FROM t1 } 1791} {1 {foreign key constraint failed}} 1792do_test fkey2-genfkey.1.11 { 1793 execsql { UPDATE t2 SET e = NULL } 1794} {} 1795do_test fkey2-genfkey.1.12 { 1796 execsql { 1797 UPDATE t1 SET a = 10; 1798 DELETE FROM t1; 1799 DELETE FROM t2; 1800 } 1801} {} 1802do_test fkey2-genfkey.1.13 { 1803 execsql { 1804 INSERT INTO t3 VALUES(1, NULL, NULL); 1805 INSERT INTO t3 VALUES(1, 2, NULL); 1806 INSERT INTO t3 VALUES(1, NULL, 3); 1807 } 1808} {} 1809do_test fkey2-genfkey.1.14 { 1810 catchsql { INSERT INTO t3 VALUES(3, 1, 4) } 1811} {1 {foreign key constraint failed}} 1812do_test fkey2-genfkey.1.15 { 1813 execsql { 1814 INSERT INTO t1 VALUES(1, 1, 4); 1815 INSERT INTO t3 VALUES(3, 1, 4); 1816 } 1817} {} 1818do_test fkey2-genfkey.1.16 { 1819 catchsql { DELETE FROM t1 } 1820} {1 {foreign key constraint failed}} 1821do_test fkey2-genfkey.1.17 { 1822 catchsql { UPDATE t1 SET b = 10} 1823} {1 {foreign key constraint failed}} 1824do_test fkey2-genfkey.1.18 { 1825 execsql { UPDATE t1 SET a = 10} 1826} {} 1827do_test fkey2-genfkey.1.19 { 1828 catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3} 1829} {1 {foreign key constraint failed}} 1830 1831drop_all_tables 1832do_test fkey2-genfkey.2.1 { 1833 execsql { 1834 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); 1835 CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f); 1836 CREATE TABLE t3(g, h, i, 1837 FOREIGN KEY (h, i) 1838 REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE 1839 ); 1840 } 1841} {} 1842do_test fkey2-genfkey.2.2 { 1843 execsql { 1844 INSERT INTO t1 VALUES(1, 2, 3); 1845 INSERT INTO t1 VALUES(4, 5, 6); 1846 INSERT INTO t2 VALUES(1, 'one'); 1847 INSERT INTO t2 VALUES(4, 'four'); 1848 } 1849} {} 1850do_test fkey2-genfkey.2.3 { 1851 execsql { 1852 UPDATE t1 SET a = 2 WHERE a = 1; 1853 SELECT * FROM t2; 1854 } 1855} {2 one 4 four} 1856do_test fkey2-genfkey.2.4 { 1857 execsql { 1858 DELETE FROM t1 WHERE a = 4; 1859 SELECT * FROM t2; 1860 } 1861} {2 one} 1862 1863do_test fkey2-genfkey.2.5 { 1864 execsql { 1865 INSERT INTO t3 VALUES('hello', 2, 3); 1866 UPDATE t1 SET c = 2; 1867 SELECT * FROM t3; 1868 } 1869} {hello 2 2} 1870do_test fkey2-genfkey.2.6 { 1871 execsql { 1872 DELETE FROM t1; 1873 SELECT * FROM t3; 1874 } 1875} {} 1876 1877drop_all_tables 1878do_test fkey2-genfkey.3.1 { 1879 execsql { 1880 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b)); 1881 CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f); 1882 CREATE TABLE t3(g, h, i, 1883 FOREIGN KEY (h, i) 1884 REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL 1885 ); 1886 } 1887} {} 1888do_test fkey2-genfkey.3.2 { 1889 execsql { 1890 INSERT INTO t1 VALUES(1, 2, 3); 1891 INSERT INTO t1 VALUES(4, 5, 6); 1892 INSERT INTO t2 VALUES(1, 'one'); 1893 INSERT INTO t2 VALUES(4, 'four'); 1894 } 1895} {} 1896do_test fkey2-genfkey.3.3 { 1897 execsql { 1898 UPDATE t1 SET a = 2 WHERE a = 1; 1899 SELECT * FROM t2; 1900 } 1901} {{} one 4 four} 1902do_test fkey2-genfkey.3.4 { 1903 execsql { 1904 DELETE FROM t1 WHERE a = 4; 1905 SELECT * FROM t2; 1906 } 1907} {{} one {} four} 1908do_test fkey2-genfkey.3.5 { 1909 execsql { 1910 INSERT INTO t3 VALUES('hello', 2, 3); 1911 UPDATE t1 SET c = 2; 1912 SELECT * FROM t3; 1913 } 1914} {hello {} {}} 1915do_test fkey2-genfkey.3.6 { 1916 execsql { 1917 UPDATE t3 SET h = 2, i = 2; 1918 DELETE FROM t1; 1919 SELECT * FROM t3; 1920 } 1921} {hello {} {}} 1922 1923#------------------------------------------------------------------------- 1924# Verify that ticket dd08e5a988d00decc4a543daa8dbbfab9c577ad8 has been 1925# fixed. 1926# 1927do_test fkey2-dd08e5.1.1 { 1928 execsql { 1929 PRAGMA foreign_keys=ON; 1930 CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b); 1931 CREATE UNIQUE INDEX idd08 ON tdd08(a,b); 1932 INSERT INTO tdd08 VALUES(200,300); 1933 1934 CREATE TABLE tdd08_b(w,x,y, FOREIGN KEY(x,y) REFERENCES tdd08(a,b)); 1935 INSERT INTO tdd08_b VALUES(100,200,300); 1936 } 1937} {} 1938do_test fkey2-dd08e5.1.2 { 1939 catchsql { 1940 DELETE FROM tdd08; 1941 } 1942} {1 {foreign key constraint failed}} 1943do_test fkey2-dd08e5.1.3 { 1944 execsql { 1945 SELECT * FROM tdd08; 1946 } 1947} {200 300} 1948do_test fkey2-dd08e5.1.4 { 1949 catchsql { 1950 INSERT INTO tdd08_b VALUES(400,500,300); 1951 } 1952} {1 {foreign key constraint failed}} 1953do_test fkey2-dd08e5.1.5 { 1954 catchsql { 1955 UPDATE tdd08_b SET x=x+1; 1956 } 1957} {1 {foreign key constraint failed}} 1958do_test fkey2-dd08e5.1.6 { 1959 catchsql { 1960 UPDATE tdd08 SET a=a+1; 1961 } 1962} {1 {foreign key constraint failed}} 1963 1964#------------------------------------------------------------------------- 1965# Verify that ticket ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba 1966# fixed. 1967# 1968do_test fkey2-ce7c13.1.1 { 1969 execsql { 1970 CREATE TABLE tce71(a INTEGER PRIMARY KEY, b); 1971 CREATE UNIQUE INDEX ice71 ON tce71(a,b); 1972 INSERT INTO tce71 VALUES(100,200); 1973 CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b)); 1974 INSERT INTO tce72 VALUES(300,100,200); 1975 UPDATE tce71 set b = 200 where a = 100; 1976 SELECT * FROM tce71, tce72; 1977 } 1978} {100 200 300 100 200} 1979do_test fkey2-ce7c13.1.2 { 1980 catchsql { 1981 UPDATE tce71 set b = 201 where a = 100; 1982 } 1983} {1 {foreign key constraint failed}} 1984do_test fkey2-ce7c13.1.3 { 1985 catchsql { 1986 UPDATE tce71 set a = 101 where a = 100; 1987 } 1988} {1 {foreign key constraint failed}} 1989do_test fkey2-ce7c13.1.4 { 1990 execsql { 1991 CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b)); 1992 INSERT INTO tce73 VALUES(100,200); 1993 CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b)); 1994 INSERT INTO tce74 VALUES(300,100,200); 1995 UPDATE tce73 set b = 200 where a = 100; 1996 SELECT * FROM tce73, tce74; 1997 } 1998} {100 200 300 100 200} 1999do_test fkey2-ce7c13.1.5 { 2000 catchsql { 2001 UPDATE tce73 set b = 201 where a = 100; 2002 } 2003} {1 {foreign key constraint failed}} 2004do_test fkey2-ce7c13.1.6 { 2005 catchsql { 2006 UPDATE tce73 set a = 101 where a = 100; 2007 } 2008} {1 {foreign key constraint failed}} 2009 2010finish_test 2011