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: e!=5}} 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: e!=5}} 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} 679do_execsql_test fkey2-9.3.0 { 680 CREATE TABLE t3(x PRIMARY KEY REFERENCES t3 ON DELETE SET NULL); 681 INSERT INTO t3(x) VALUES(12345); 682 DROP TABLE t3; 683} {} 684 685#------------------------------------------------------------------------- 686# The following tests, fkey2-10.*, test "foreign key mismatch" and 687# other errors. 688# 689set tn 0 690foreach zSql [list { 691 CREATE TABLE p(a PRIMARY KEY, b); 692 CREATE TABLE c(x REFERENCES p(c)); 693} { 694 CREATE TABLE c(x REFERENCES v(y)); 695 CREATE VIEW v AS SELECT x AS y FROM c; 696} { 697 CREATE TABLE p(a, b, PRIMARY KEY(a, b)); 698 CREATE TABLE c(x REFERENCES p); 699} { 700 CREATE TABLE p(a COLLATE binary, b); 701 CREATE UNIQUE INDEX i ON p(a COLLATE nocase); 702 CREATE TABLE c(x REFERENCES p(a)); 703}] { 704 drop_all_tables 705 do_test fkey2-10.1.[incr tn] { 706 execsql $zSql 707 catchsql { INSERT INTO c DEFAULT VALUES } 708 } {/1 {foreign key mismatch - "c" referencing "."}/} 709} 710 711# "rowid" cannot be used as part of a child or parent key definition 712# unless it happens to be the name of an explicitly declared column. 713# 714do_test fkey2-10.2.1 { 715 drop_all_tables 716 catchsql { 717 CREATE TABLE t1(a PRIMARY KEY, b); 718 CREATE TABLE t2(c, d, FOREIGN KEY(rowid) REFERENCES t1(a)); 719 } 720} {1 {unknown column "rowid" in foreign key definition}} 721do_test fkey2-10.2.2 { 722 drop_all_tables 723 catchsql { 724 CREATE TABLE t1(a PRIMARY KEY, b); 725 CREATE TABLE t2(rowid, d, FOREIGN KEY(rowid) REFERENCES t1(a)); 726 } 727} {0 {}} 728do_test fkey2-10.2.1 { 729 drop_all_tables 730 catchsql { 731 CREATE TABLE t1(a, b); 732 CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid)); 733 INSERT INTO t1(rowid, a, b) VALUES(1, 1, 1); 734 INSERT INTO t2 VALUES(1, 1); 735 } 736} {1 {foreign key mismatch - "t2" referencing "t1"}} 737do_test fkey2-10.2.2 { 738 drop_all_tables 739 catchsql { 740 CREATE TABLE t1(rowid PRIMARY KEY, b); 741 CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid)); 742 INSERT INTO t1(rowid, b) VALUES(1, 1); 743 INSERT INTO t2 VALUES(1, 1); 744 } 745} {0 {}} 746 747 748#------------------------------------------------------------------------- 749# The following tests, fkey2-11.*, test CASCADE actions. 750# 751drop_all_tables 752do_test fkey2-11.1.1 { 753 execsql { 754 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, rowid, _rowid_, oid); 755 CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE); 756 757 INSERT INTO t1 VALUES(10, 100, 'abc', 'def', 'ghi'); 758 INSERT INTO t2 VALUES(10, 100); 759 UPDATE t1 SET a = 15; 760 SELECT * FROM t2; 761 } 762} {15 100} 763 764#------------------------------------------------------------------------- 765# The following tests, fkey2-12.*, test RESTRICT actions. 766# 767drop_all_tables 768do_test fkey2-12.1.1 { 769 execsql { 770 CREATE TABLE t1(a, b PRIMARY KEY); 771 CREATE TABLE t2( 772 x REFERENCES t1 ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED 773 ); 774 INSERT INTO t1 VALUES(1, 'one'); 775 INSERT INTO t1 VALUES(2, 'two'); 776 INSERT INTO t1 VALUES(3, 'three'); 777 } 778} {} 779do_test fkey2-12.1.2 { 780 execsql "BEGIN" 781 execsql "INSERT INTO t2 VALUES('two')" 782} {} 783do_test fkey2-12.1.3 { 784 execsql "UPDATE t1 SET b = 'four' WHERE b = 'one'" 785} {} 786do_test fkey2-12.1.4 { 787 catchsql "UPDATE t1 SET b = 'five' WHERE b = 'two'" 788} {1 {FOREIGN KEY constraint failed}} 789do_test fkey2-12.1.5 { 790 execsql "DELETE FROM t1 WHERE b = 'two'" 791} {} 792do_test fkey2-12.1.6 { 793 catchsql "COMMIT" 794} {1 {FOREIGN KEY constraint failed}} 795do_test fkey2-12.1.7 { 796 execsql { 797 INSERT INTO t1 VALUES(2, 'two'); 798 COMMIT; 799 } 800} {} 801 802drop_all_tables 803do_test fkey2-12.2.1 { 804 execsql { 805 CREATE TABLE t1(x COLLATE NOCASE PRIMARY KEY); 806 CREATE TRIGGER tt1 AFTER DELETE ON t1 807 WHEN EXISTS ( SELECT 1 FROM t2 WHERE old.x = y ) 808 BEGIN 809 INSERT INTO t1 VALUES(old.x); 810 END; 811 CREATE TABLE t2(y REFERENCES t1); 812 INSERT INTO t1 VALUES('A'); 813 INSERT INTO t1 VALUES('B'); 814 INSERT INTO t2 VALUES('a'); 815 INSERT INTO t2 VALUES('b'); 816 817 SELECT * FROM t1; 818 SELECT * FROM t2; 819 } 820} {A B a b} 821do_test fkey2-12.2.2 { 822 execsql { DELETE FROM t1 } 823 execsql { 824 SELECT * FROM t1; 825 SELECT * FROM t2; 826 } 827} {A B a b} 828do_test fkey2-12.2.3 { 829 execsql { 830 DROP TABLE t2; 831 CREATE TABLE t2(y REFERENCES t1 ON DELETE RESTRICT); 832 INSERT INTO t2 VALUES('a'); 833 INSERT INTO t2 VALUES('b'); 834 } 835 catchsql { DELETE FROM t1 } 836} {1 {FOREIGN KEY constraint failed}} 837do_test fkey2-12.2.4 { 838 execsql { 839 SELECT * FROM t1; 840 SELECT * FROM t2; 841 } 842} {A B a b} 843 844drop_all_tables 845do_test fkey2-12.3.1 { 846 execsql { 847 CREATE TABLE up( 848 c00, c01, c02, c03, c04, c05, c06, c07, c08, c09, 849 c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, 850 c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, 851 c30, c31, c32, c33, c34, c35, c36, c37, c38, c39, 852 PRIMARY KEY(c34, c35) 853 ); 854 CREATE TABLE down( 855 c00, c01, c02, c03, c04, c05, c06, c07, c08, c09, 856 c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, 857 c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, 858 c30, c31, c32, c33, c34, c35, c36, c37, c38, c39, 859 FOREIGN KEY(c39, c38) REFERENCES up ON UPDATE CASCADE 860 ); 861 } 862} {} 863do_test fkey2-12.3.2 { 864 execsql { 865 INSERT INTO up(c34, c35) VALUES('yes', 'no'); 866 INSERT INTO down(c39, c38) VALUES('yes', 'no'); 867 UPDATE up SET c34 = 'possibly'; 868 SELECT c38, c39 FROM down; 869 DELETE FROM down; 870 } 871} {no possibly} 872do_test fkey2-12.3.3 { 873 catchsql { INSERT INTO down(c39, c38) VALUES('yes', 'no') } 874} {1 {FOREIGN KEY constraint failed}} 875do_test fkey2-12.3.4 { 876 execsql { 877 INSERT INTO up(c34, c35) VALUES('yes', 'no'); 878 INSERT INTO down(c39, c38) VALUES('yes', 'no'); 879 } 880 catchsql { DELETE FROM up WHERE c34 = 'yes' } 881} {1 {FOREIGN KEY constraint failed}} 882do_test fkey2-12.3.5 { 883 execsql { 884 DELETE FROM up WHERE c34 = 'possibly'; 885 SELECT c34, c35 FROM up; 886 SELECT c39, c38 FROM down; 887 } 888} {yes no yes no} 889 890#------------------------------------------------------------------------- 891# The following tests, fkey2-13.*, test that FK processing is performed 892# when rows are REPLACEd. 893# 894drop_all_tables 895do_test fkey2-13.1.1 { 896 execsql { 897 CREATE TABLE pp(a UNIQUE, b, c, PRIMARY KEY(b, c)); 898 CREATE TABLE cc(d, e, f UNIQUE, FOREIGN KEY(d, e) REFERENCES pp); 899 INSERT INTO pp VALUES(1, 2, 3); 900 INSERT INTO cc VALUES(2, 3, 1); 901 } 902} {} 903foreach {tn stmt} { 904 1 "REPLACE INTO pp VALUES(1, 4, 5)" 905 2 "REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 3, 4)" 906} { 907 do_test fkey2-13.1.$tn.1 { 908 catchsql $stmt 909 } {1 {FOREIGN KEY constraint failed}} 910 do_test fkey2-13.1.$tn.2 { 911 execsql { 912 SELECT * FROM pp; 913 SELECT * FROM cc; 914 } 915 } {1 2 3 2 3 1} 916 do_test fkey2-13.1.$tn.3 { 917 execsql BEGIN; 918 catchsql $stmt 919 } {1 {FOREIGN KEY constraint failed}} 920 do_test fkey2-13.1.$tn.4 { 921 execsql { 922 COMMIT; 923 SELECT * FROM pp; 924 SELECT * FROM cc; 925 } 926 } {1 2 3 2 3 1} 927} 928do_test fkey2-13.1.3 { 929 execsql { 930 REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 2, 3); 931 SELECT rowid, * FROM pp; 932 SELECT * FROM cc; 933 } 934} {1 2 2 3 2 3 1} 935do_test fkey2-13.1.4 { 936 execsql { 937 REPLACE INTO pp(rowid, a, b, c) VALUES(2, 2, 2, 3); 938 SELECT rowid, * FROM pp; 939 SELECT * FROM cc; 940 } 941} {2 2 2 3 2 3 1} 942 943#------------------------------------------------------------------------- 944# The following tests, fkey2-14.*, test that the "DROP TABLE" and "ALTER 945# TABLE" commands work as expected wrt foreign key constraints. 946# 947# fkey2-14.1*: ALTER TABLE ADD COLUMN 948# fkey2-14.2*: ALTER TABLE RENAME TABLE 949# fkey2-14.3*: DROP TABLE 950# 951drop_all_tables 952ifcapable altertable { 953 do_test fkey2-14.1.1 { 954 # Adding a column with a REFERENCES clause is not supported. 955 execsql { 956 CREATE TABLE t1(a PRIMARY KEY); 957 CREATE TABLE t2(a, b); 958 INSERT INTO t2 VALUES(1,2); 959 } 960 catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 } 961 } {0 {}} 962 do_test fkey2-14.1.2 { 963 catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 } 964 } {0 {}} 965 do_test fkey2-14.1.3 { 966 catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL} 967 } {0 {}} 968 do_test fkey2-14.1.4 { 969 catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'} 970 } {1 {Cannot add a REFERENCES column with non-NULL default value}} 971 do_test fkey2-14.1.5 { 972 catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 } 973 } {1 {Cannot add a REFERENCES column with non-NULL default value}} 974 do_test fkey2-14.1.6 { 975 execsql { 976 PRAGMA foreign_keys = off; 977 ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1; 978 PRAGMA foreign_keys = on; 979 SELECT sql FROM sqlite_master WHERE name='t2'; 980 } 981 } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}} 982 983 984 # Test the sqlite_rename_parent() function directly. 985 # 986 proc test_rename_parent {zCreate zOld zNew} { 987 db eval {SELECT sqlite_rename_table( 988 'main', 'table', 't1', $zCreate, $zOld, $zNew, 0 989 )} 990 } 991 sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db 992 do_test fkey2-14.2.1.1 { 993 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3 994 } {{CREATE TABLE t1(a REFERENCES "t3")}} 995 do_test fkey2-14.2.1.2 { 996 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3 997 } {{CREATE TABLE t1(a REFERENCES t2)}} 998 do_test fkey2-14.2.1.3 { 999 test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3 1000 } {{CREATE TABLE t1(a REFERENCES "t3")}} 1001 sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db 1002 1003 # Test ALTER TABLE RENAME TABLE a bit. 1004 # 1005 do_test fkey2-14.2.2.1 { 1006 drop_all_tables 1007 execsql { 1008 CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1); 1009 CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2); 1010 CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1); 1011 } 1012 execsql { SELECT sql FROM sqlite_master WHERE type = 'table'} 1013 } [list \ 1014 {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \ 1015 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \ 1016 {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \ 1017 ] 1018 do_test fkey2-14.2.2.2 { 1019 execsql { ALTER TABLE t1 RENAME TO t4 } 1020 execsql { SELECT sql FROM sqlite_master WHERE type = 'table'} 1021 } [list \ 1022 {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \ 1023 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \ 1024 {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \ 1025 ] 1026 do_test fkey2-14.2.2.3 { 1027 catchsql { INSERT INTO t3 VALUES(1, 2, 3) } 1028 } {1 {FOREIGN KEY constraint failed}} 1029 do_test fkey2-14.2.2.4 { 1030 execsql { INSERT INTO t4 VALUES(1, NULL) } 1031 } {} 1032 do_test fkey2-14.2.2.5 { 1033 catchsql { UPDATE t4 SET b = 5 } 1034 } {1 {FOREIGN KEY constraint failed}} 1035 do_test fkey2-14.2.2.6 { 1036 catchsql { UPDATE t4 SET b = 1 } 1037 } {0 {}} 1038 do_test fkey2-14.2.2.7 { 1039 execsql { INSERT INTO t3 VALUES(1, NULL, 1) } 1040 } {} 1041 1042 # Repeat for TEMP tables 1043 # 1044 drop_all_tables 1045 do_test fkey2-14.1tmp.1 { 1046 # Adding a column with a REFERENCES clause is not supported. 1047 execsql { 1048 CREATE TEMP TABLE t1(a PRIMARY KEY); 1049 CREATE TEMP TABLE t2(a, b); 1050 INSERT INTO temp.t2 VALUES(1,2); 1051 } 1052 catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 } 1053 } {0 {}} 1054 do_test fkey2-14.1tmp.2 { 1055 catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 } 1056 } {0 {}} 1057 do_test fkey2-14.1tmp.3 { 1058 catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL} 1059 } {0 {}} 1060 do_test fkey2-14.1tmp.4 { 1061 catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'} 1062 } {1 {Cannot add a REFERENCES column with non-NULL default value}} 1063 do_test fkey2-14.1tmp.5 { 1064 catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 } 1065 } {1 {Cannot add a REFERENCES column with non-NULL default value}} 1066 do_test fkey2-14.1tmp.6 { 1067 execsql { 1068 PRAGMA foreign_keys = off; 1069 ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1; 1070 PRAGMA foreign_keys = on; 1071 SELECT sql FROM temp.sqlite_master WHERE name='t2'; 1072 } 1073 } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}} 1074 1075 sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db 1076 do_test fkey2-14.2tmp.1.1 { 1077 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3 1078 } {{CREATE TABLE t1(a REFERENCES "t3")}} 1079 do_test fkey2-14.2tmp.1.2 { 1080 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3 1081 } {{CREATE TABLE t1(a REFERENCES t2)}} 1082 do_test fkey2-14.2tmp.1.3 { 1083 test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3 1084 } {{CREATE TABLE t1(a REFERENCES "t3")}} 1085 sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db 1086 1087 # Test ALTER TABLE RENAME TABLE a bit. 1088 # 1089 do_test fkey2-14.2tmp.2.1 { 1090 drop_all_tables 1091 execsql { 1092 CREATE TEMP TABLE t1(a PRIMARY KEY, b REFERENCES t1); 1093 CREATE TEMP TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2); 1094 CREATE TEMP TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1); 1095 } 1096 execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'} 1097 } [list \ 1098 {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \ 1099 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \ 1100 {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \ 1101 ] 1102 do_test fkey2-14.2tmp.2.2 { 1103 execsql { ALTER TABLE t1 RENAME TO t4 } 1104 execsql { SELECT sql FROM temp.sqlite_master WHERE type = 'table'} 1105 } [list \ 1106 {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \ 1107 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \ 1108 {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \ 1109 ] 1110 do_test fkey2-14.2tmp.2.3 { 1111 catchsql { INSERT INTO t3 VALUES(1, 2, 3) } 1112 } {1 {FOREIGN KEY constraint failed}} 1113 do_test fkey2-14.2tmp.2.4 { 1114 execsql { INSERT INTO t4 VALUES(1, NULL) } 1115 } {} 1116 do_test fkey2-14.2tmp.2.5 { 1117 catchsql { UPDATE t4 SET b = 5 } 1118 } {1 {FOREIGN KEY constraint failed}} 1119 do_test fkey2-14.2tmp.2.6 { 1120 catchsql { UPDATE t4 SET b = 1 } 1121 } {0 {}} 1122 do_test fkey2-14.2tmp.2.7 { 1123 execsql { INSERT INTO t3 VALUES(1, NULL, 1) } 1124 } {} 1125 1126 # Repeat for ATTACH-ed tables 1127 # 1128 drop_all_tables 1129 do_test fkey2-14.1aux.1 { 1130 # Adding a column with a REFERENCES clause is not supported. 1131 execsql { 1132 ATTACH ':memory:' AS aux; 1133 CREATE TABLE aux.t1(a PRIMARY KEY); 1134 CREATE TABLE aux.t2(a, b); 1135 INSERT INTO aux.t2(a,b) VALUES(1,2); 1136 } 1137 catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 } 1138 } {0 {}} 1139 do_test fkey2-14.1aux.2 { 1140 catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 } 1141 } {0 {}} 1142 do_test fkey2-14.1aux.3 { 1143 catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL} 1144 } {0 {}} 1145 do_test fkey2-14.1aux.4 { 1146 catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'} 1147 } {1 {Cannot add a REFERENCES column with non-NULL default value}} 1148 do_test fkey2-14.1aux.5 { 1149 catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 } 1150 } {1 {Cannot add a REFERENCES column with non-NULL default value}} 1151 do_test fkey2-14.1aux.6 { 1152 execsql { 1153 PRAGMA foreign_keys = off; 1154 ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1; 1155 PRAGMA foreign_keys = on; 1156 SELECT sql FROM aux.sqlite_master WHERE name='t2'; 1157 } 1158 } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}} 1159 1160 sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db 1161 do_test fkey2-14.2aux.1.1 { 1162 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3 1163 } {{CREATE TABLE t1(a REFERENCES "t3")}} 1164 do_test fkey2-14.2aux.1.2 { 1165 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3 1166 } {{CREATE TABLE t1(a REFERENCES t2)}} 1167 do_test fkey2-14.2aux.1.3 { 1168 test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3 1169 } {{CREATE TABLE t1(a REFERENCES "t3")}} 1170 sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db 1171 1172 # Test ALTER TABLE RENAME TABLE a bit. 1173 # 1174 do_test fkey2-14.2aux.2.1 { 1175 drop_all_tables 1176 execsql { 1177 CREATE TABLE aux.t1(a PRIMARY KEY, b REFERENCES t1); 1178 CREATE TABLE aux.t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2); 1179 CREATE TABLE aux.t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1); 1180 } 1181 execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'} 1182 } [list \ 1183 {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \ 1184 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \ 1185 {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \ 1186 ] 1187 do_test fkey2-14.2aux.2.2 { 1188 execsql { ALTER TABLE t1 RENAME TO t4 } 1189 execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'} 1190 } [list \ 1191 {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \ 1192 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \ 1193 {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \ 1194 ] 1195 do_test fkey2-14.2aux.2.3 { 1196 catchsql { INSERT INTO t3 VALUES(1, 2, 3) } 1197 } {1 {FOREIGN KEY constraint failed}} 1198 do_test fkey2-14.2aux.2.4 { 1199 execsql { INSERT INTO t4 VALUES(1, NULL) } 1200 } {} 1201 do_test fkey2-14.2aux.2.5 { 1202 catchsql { UPDATE t4 SET b = 5 } 1203 } {1 {FOREIGN KEY constraint failed}} 1204 do_test fkey2-14.2aux.2.6 { 1205 catchsql { UPDATE t4 SET b = 1 } 1206 } {0 {}} 1207 do_test fkey2-14.2aux.2.7 { 1208 execsql { INSERT INTO t3 VALUES(1, NULL, 1) } 1209 } {} 1210} 1211 1212do_test fkey-2.14.3.1 { 1213 drop_all_tables 1214 execsql { 1215 CREATE TABLE t1(a, b REFERENCES nosuchtable); 1216 DROP TABLE t1; 1217 } 1218} {} 1219do_test fkey-2.14.3.2 { 1220 execsql { 1221 CREATE TABLE t1(a PRIMARY KEY, b); 1222 INSERT INTO t1 VALUES('a', 1); 1223 CREATE TABLE t2(x REFERENCES t1); 1224 INSERT INTO t2 VALUES('a'); 1225 } 1226} {} 1227do_test fkey-2.14.3.3 { 1228 catchsql { DROP TABLE t1 } 1229} {1 {FOREIGN KEY constraint failed}} 1230do_test fkey-2.14.3.4 { 1231 execsql { 1232 DELETE FROM t2; 1233 DROP TABLE t1; 1234 } 1235} {} 1236do_test fkey-2.14.3.4 { 1237 catchsql { INSERT INTO t2 VALUES('x') } 1238} {1 {no such table: main.t1}} 1239do_test fkey-2.14.3.5 { 1240 execsql { 1241 CREATE TABLE t1(x PRIMARY KEY); 1242 INSERT INTO t1 VALUES('x'); 1243 } 1244 execsql { INSERT INTO t2 VALUES('x') } 1245} {} 1246do_test fkey-2.14.3.6 { 1247 catchsql { DROP TABLE t1 } 1248} {1 {FOREIGN KEY constraint failed}} 1249do_test fkey-2.14.3.7 { 1250 execsql { 1251 DROP TABLE t2; 1252 DROP TABLE t1; 1253 } 1254} {} 1255do_test fkey-2.14.3.8 { 1256 execsql { 1257 CREATE TABLE pp(x, y, PRIMARY KEY(x, y)); 1258 CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z)); 1259 } 1260 catchsql { INSERT INTO cc VALUES(1, 2) } 1261} {1 {foreign key mismatch - "cc" referencing "pp"}} 1262do_test fkey-2.14.3.9 { 1263 execsql { DROP TABLE cc } 1264} {} 1265do_test fkey-2.14.3.10 { 1266 execsql { 1267 CREATE TABLE cc(a, b, 1268 FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED 1269 ); 1270 } 1271 execsql { 1272 INSERT INTO pp VALUES('a', 'b'); 1273 INSERT INTO cc VALUES('a', 'b'); 1274 BEGIN; 1275 DROP TABLE pp; 1276 CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c)); 1277 INSERT INTO pp VALUES(1, 'a', 'b'); 1278 COMMIT; 1279 } 1280} {} 1281do_test fkey-2.14.3.11 { 1282 execsql { 1283 BEGIN; 1284 DROP TABLE cc; 1285 DROP TABLE pp; 1286 COMMIT; 1287 } 1288} {} 1289do_test fkey-2.14.3.12 { 1290 execsql { 1291 CREATE TABLE b1(a, b); 1292 CREATE TABLE b2(a, b REFERENCES b1); 1293 DROP TABLE b1; 1294 } 1295} {} 1296do_test fkey-2.14.3.13 { 1297 execsql { 1298 CREATE TABLE b3(a, b REFERENCES b2 DEFERRABLE INITIALLY DEFERRED); 1299 DROP TABLE b2; 1300 } 1301} {} 1302 1303# Test that nothing goes wrong when dropping a table that refers to a view. 1304# Or dropping a view that an existing FK (incorrectly) refers to. Or either 1305# of the above scenarios with a virtual table. 1306drop_all_tables 1307do_test fkey-2.14.4.1 { 1308 execsql { 1309 CREATE TABLE t1(x REFERENCES v); 1310 CREATE VIEW v AS SELECT * FROM t1; 1311 } 1312} {} 1313do_test fkey-2.14.4.2 { 1314 execsql { 1315 DROP VIEW v; 1316 } 1317} {} 1318ifcapable vtab { 1319 register_echo_module db 1320 do_test fkey-2.14.4.3 { 1321 execsql { CREATE VIRTUAL TABLE v USING echo(t1) } 1322 } {} 1323 do_test fkey-2.14.4.2 { 1324 execsql { 1325 DROP TABLE v; 1326 } 1327 } {} 1328} 1329 1330#------------------------------------------------------------------------- 1331# The following tests, fkey2-15.*, test that unnecessary FK related scans 1332# and lookups are avoided when the constraint counters are zero. 1333# 1334drop_all_tables 1335proc execsqlS {zSql} { 1336 set ::sqlite_search_count 0 1337 set ::sqlite_found_count 0 1338 set res [uplevel [list execsql $zSql]] 1339 concat [expr $::sqlite_found_count + $::sqlite_search_count] $res 1340} 1341do_test fkey2-15.1.1 { 1342 execsql { 1343 CREATE TABLE pp(a PRIMARY KEY, b); 1344 CREATE TABLE cc(x, y REFERENCES pp DEFERRABLE INITIALLY DEFERRED); 1345 INSERT INTO pp VALUES(1, 'one'); 1346 INSERT INTO pp VALUES(2, 'two'); 1347 INSERT INTO cc VALUES('neung', 1); 1348 INSERT INTO cc VALUES('song', 2); 1349 } 1350} {} 1351do_test fkey2-15.1.2 { 1352 execsqlS { INSERT INTO pp VALUES(3, 'three') } 1353} {0} 1354do_test fkey2-15.1.3 { 1355 execsql { 1356 BEGIN; 1357 INSERT INTO cc VALUES('see', 4); -- Violates deferred constraint 1358 } 1359 execsqlS { INSERT INTO pp VALUES(5, 'five') } 1360} {2} 1361do_test fkey2-15.1.4 { 1362 execsql { DELETE FROM cc WHERE x = 'see' } 1363 execsqlS { INSERT INTO pp VALUES(6, 'six') } 1364} {0} 1365do_test fkey2-15.1.5 { 1366 execsql COMMIT 1367} {} 1368do_test fkey2-15.1.6 { 1369 execsql BEGIN 1370 execsqlS { 1371 DELETE FROM cc WHERE x = 'neung'; 1372 ROLLBACK; 1373 } 1374} {1} 1375do_test fkey2-15.1.7 { 1376 execsql { 1377 BEGIN; 1378 DELETE FROM pp WHERE a = 2; 1379 } 1380 execsqlS { 1381 DELETE FROM cc WHERE x = 'neung'; 1382 ROLLBACK; 1383 } 1384} {2} 1385 1386#------------------------------------------------------------------------- 1387# This next block of tests, fkey2-16.*, test that rows that refer to 1388# themselves may be inserted and deleted. 1389# 1390foreach {tn zSchema} { 1391 1 { CREATE TABLE self(a INTEGER PRIMARY KEY, b REFERENCES self(a)) } 1392 2 { CREATE TABLE self(a PRIMARY KEY, b REFERENCES self(a)) } 1393 3 { CREATE TABLE self(a UNIQUE, b INTEGER PRIMARY KEY REFERENCES self(a)) } 1394} { 1395 drop_all_tables 1396 do_test fkey2-16.1.$tn.1 { 1397 execsql $zSchema 1398 execsql { INSERT INTO self VALUES(13, 13) } 1399 } {} 1400 do_test fkey2-16.1.$tn.2 { 1401 execsql { UPDATE self SET a = 14, b = 14 } 1402 } {} 1403 1404 do_test fkey2-16.1.$tn.3 { 1405 catchsql { UPDATE self SET b = 15 } 1406 } {1 {FOREIGN KEY constraint failed}} 1407 1408 do_test fkey2-16.1.$tn.4 { 1409 catchsql { UPDATE self SET a = 15 } 1410 } {1 {FOREIGN KEY constraint failed}} 1411 1412 do_test fkey2-16.1.$tn.5 { 1413 catchsql { UPDATE self SET a = 15, b = 16 } 1414 } {1 {FOREIGN KEY constraint failed}} 1415 1416 do_test fkey2-16.1.$tn.6 { 1417 catchsql { UPDATE self SET a = 17, b = 17 } 1418 } {0 {}} 1419 1420 do_test fkey2-16.1.$tn.7 { 1421 execsql { DELETE FROM self } 1422 } {} 1423 do_test fkey2-16.1.$tn.8 { 1424 catchsql { INSERT INTO self VALUES(20, 21) } 1425 } {1 {FOREIGN KEY constraint failed}} 1426} 1427 1428#------------------------------------------------------------------------- 1429# This next block of tests, fkey2-17.*, tests that if "PRAGMA count_changes" 1430# is turned on statements that violate immediate FK constraints return 1431# SQLITE_CONSTRAINT immediately, not after returning a number of rows. 1432# Whereas statements that violate deferred FK constraints return the number 1433# of rows before failing. 1434# 1435# Also test that rows modified by FK actions are not counted in either the 1436# returned row count or the values returned by sqlite3_changes(). Like 1437# trigger related changes, they are included in sqlite3_total_changes() though. 1438# 1439drop_all_tables 1440do_test fkey2-17.1.1 { 1441 execsql { PRAGMA count_changes = 1 } 1442 execsql { 1443 CREATE TABLE one(a, b, c, UNIQUE(b, c)); 1444 CREATE TABLE two(d, e, f, FOREIGN KEY(e, f) REFERENCES one(b, c)); 1445 INSERT INTO one VALUES(1, 2, 3); 1446 } 1447} {1} 1448do_test fkey2-17.1.2 { 1449 set STMT [sqlite3_prepare_v2 db "INSERT INTO two VALUES(4, 5, 6)" -1 dummy] 1450 sqlite3_step $STMT 1451} {SQLITE_CONSTRAINT} 1452verify_ex_errcode fkey2-17.1.2b SQLITE_CONSTRAINT_FOREIGNKEY 1453ifcapable autoreset { 1454 do_test fkey2-17.1.3 { 1455 sqlite3_step $STMT 1456 } {SQLITE_CONSTRAINT} 1457 verify_ex_errcode fkey2-17.1.3b SQLITE_CONSTRAINT_FOREIGNKEY 1458} else { 1459 do_test fkey2-17.1.3 { 1460 sqlite3_step $STMT 1461 } {SQLITE_MISUSE} 1462} 1463do_test fkey2-17.1.4 { 1464 sqlite3_finalize $STMT 1465} {SQLITE_CONSTRAINT} 1466verify_ex_errcode fkey2-17.1.4b SQLITE_CONSTRAINT_FOREIGNKEY 1467do_test fkey2-17.1.5 { 1468 execsql { 1469 INSERT INTO one VALUES(2, 3, 4); 1470 INSERT INTO one VALUES(3, 4, 5); 1471 INSERT INTO two VALUES(1, 2, 3); 1472 INSERT INTO two VALUES(2, 3, 4); 1473 INSERT INTO two VALUES(3, 4, 5); 1474 } 1475} {1 1 1 1 1} 1476do_test fkey2-17.1.6 { 1477 catchsql { 1478 BEGIN; 1479 INSERT INTO one VALUES(0, 0, 0); 1480 UPDATE two SET e=e+1, f=f+1; 1481 } 1482} {1 {FOREIGN KEY constraint failed}} 1483do_test fkey2-17.1.7 { 1484 execsql { SELECT * FROM one } 1485} {1 2 3 2 3 4 3 4 5 0 0 0} 1486do_test fkey2-17.1.8 { 1487 execsql { SELECT * FROM two } 1488} {1 2 3 2 3 4 3 4 5} 1489do_test fkey2-17.1.9 { 1490 execsql COMMIT 1491} {} 1492do_test fkey2-17.1.10 { 1493 execsql { 1494 CREATE TABLE three( 1495 g, h, i, 1496 FOREIGN KEY(h, i) REFERENCES one(b, c) DEFERRABLE INITIALLY DEFERRED 1497 ); 1498 } 1499} {} 1500do_test fkey2-17.1.11 { 1501 set STMT [sqlite3_prepare_v2 db "INSERT INTO three VALUES(7, 8, 9)" -1 dummy] 1502 sqlite3_step $STMT 1503} {SQLITE_ROW} 1504do_test fkey2-17.1.12 { 1505 sqlite3_column_text $STMT 0 1506} {1} 1507do_test fkey2-17.1.13 { 1508 sqlite3_step $STMT 1509} {SQLITE_CONSTRAINT} 1510verify_ex_errcode fkey2-17.1.13b SQLITE_CONSTRAINT_FOREIGNKEY 1511do_test fkey2-17.1.14 { 1512 sqlite3_finalize $STMT 1513} {SQLITE_CONSTRAINT} 1514verify_ex_errcode fkey2-17.1.14b SQLITE_CONSTRAINT_FOREIGNKEY 1515 1516drop_all_tables 1517do_test fkey2-17.2.1 { 1518 execsql { 1519 CREATE TABLE high("a'b!" PRIMARY KEY, b); 1520 CREATE TABLE low( 1521 c, 1522 "d&6" REFERENCES high ON UPDATE CASCADE ON DELETE CASCADE 1523 ); 1524 } 1525} {} 1526do_test fkey2-17.2.2 { 1527 execsql { 1528 INSERT INTO high VALUES('a', 'b'); 1529 INSERT INTO low VALUES('b', 'a'); 1530 } 1531 db changes 1532} {1} 1533set nTotal [db total_changes] 1534do_test fkey2-17.2.3 { 1535 execsql { UPDATE high SET "a'b!" = 'c' } 1536} {1} 1537do_test fkey2-17.2.4 { 1538 db changes 1539} {1} 1540do_test fkey2-17.2.5 { 1541 expr [db total_changes] - $nTotal 1542} {2} 1543do_test fkey2-17.2.6 { 1544 execsql { SELECT * FROM high ; SELECT * FROM low } 1545} {c b b c} 1546do_test fkey2-17.2.7 { 1547 execsql { DELETE FROM high } 1548} {1} 1549do_test fkey2-17.2.8 { 1550 db changes 1551} {1} 1552do_test fkey2-17.2.9 { 1553 expr [db total_changes] - $nTotal 1554} {4} 1555do_test fkey2-17.2.10 { 1556 execsql { SELECT * FROM high ; SELECT * FROM low } 1557} {} 1558execsql { PRAGMA count_changes = 0 } 1559 1560#------------------------------------------------------------------------- 1561# Test that the authorization callback works. 1562# 1563 1564ifcapable auth { 1565 do_test fkey2-18.1 { 1566 execsql { 1567 CREATE TABLE long(a, b PRIMARY KEY, c); 1568 CREATE TABLE short(d, e, f REFERENCES long); 1569 CREATE TABLE mid(g, h, i REFERENCES long DEFERRABLE INITIALLY DEFERRED); 1570 } 1571 } {} 1572 1573 proc auth {args} {eval lappend ::authargs [lrange $args 0 4]; return SQLITE_OK} 1574 db auth auth 1575 1576 # An insert on the parent table must read the child key of any deferred 1577 # foreign key constraints. But not the child key of immediate constraints. 1578 set authargs {} 1579 do_test fkey2-18.2 { 1580 execsql { INSERT INTO long VALUES(1, 2, 3) } 1581 set authargs 1582 } {SQLITE_INSERT long {} main {} SQLITE_READ mid i main {}} 1583 1584 # An insert on the child table of an immediate constraint must read the 1585 # parent key columns (to see if it is a violation or not). 1586 set authargs {} 1587 do_test fkey2-18.3 { 1588 execsql { INSERT INTO short VALUES(1, 3, 2) } 1589 set authargs 1590 } {SQLITE_INSERT short {} main {} SQLITE_READ long b main {}} 1591 1592 # As must an insert on the child table of a deferred constraint. 1593 set authargs {} 1594 do_test fkey2-18.4 { 1595 execsql { INSERT INTO mid VALUES(1, 3, 2) } 1596 set authargs 1597 } {SQLITE_INSERT mid {} main {} SQLITE_READ long b main {}} 1598 1599 do_test fkey2-18.5 { 1600 execsql { 1601 CREATE TABLE nought(a, b PRIMARY KEY, c); 1602 CREATE TABLE cross(d, e, f, 1603 FOREIGN KEY(e) REFERENCES nought(b) ON UPDATE CASCADE 1604 ); 1605 } 1606 execsql { INSERT INTO nought VALUES(2, 1, 2) } 1607 execsql { INSERT INTO cross VALUES(0, 1, 0) } 1608 set authargs [list] 1609 execsql { UPDATE nought SET b = 5 } 1610 set authargs 1611 } {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 {}} 1612 1613 do_test fkey2-18.6 { 1614 execsql {SELECT * FROM cross} 1615 } {0 5 0} 1616 1617 do_test fkey2-18.7 { 1618 execsql { 1619 CREATE TABLE one(a INTEGER PRIMARY KEY, b); 1620 CREATE TABLE two(b, c REFERENCES one); 1621 INSERT INTO one VALUES(101, 102); 1622 } 1623 set authargs [list] 1624 execsql { INSERT INTO two VALUES(100, 101); } 1625 set authargs 1626 } {SQLITE_INSERT two {} main {} SQLITE_READ one a main {}} 1627 1628 # Return SQLITE_IGNORE to requests to read from the parent table. This 1629 # causes inserts of non-NULL keys into the child table to fail. 1630 # 1631 rename auth {} 1632 proc auth {args} { 1633 if {[lindex $args 1] == "long"} {return SQLITE_IGNORE} 1634 return SQLITE_OK 1635 } 1636 do_test fkey2-18.8 { 1637 catchsql { INSERT INTO short VALUES(1, 3, 2) } 1638 } {1 {FOREIGN KEY constraint failed}} 1639 do_test fkey2-18.9 { 1640 execsql { INSERT INTO short VALUES(1, 3, NULL) } 1641 } {} 1642 do_test fkey2-18.10 { 1643 execsql { SELECT * FROM short } 1644 } {1 3 2 1 3 {}} 1645 do_test fkey2-18.11 { 1646 catchsql { UPDATE short SET f = 2 WHERE f IS NULL } 1647 } {1 {FOREIGN KEY constraint failed}} 1648 1649 db auth {} 1650 unset authargs 1651} 1652 1653 1654do_test fkey2-19.1 { 1655 execsql { 1656 CREATE TABLE main(id INTEGER PRIMARY KEY); 1657 CREATE TABLE sub(id INT REFERENCES main(id)); 1658 INSERT INTO main VALUES(1); 1659 INSERT INTO main VALUES(2); 1660 INSERT INTO sub VALUES(2); 1661 } 1662} {} 1663do_test fkey2-19.2 { 1664 set S [sqlite3_prepare_v2 db "DELETE FROM main WHERE id = ?" -1 dummy] 1665 sqlite3_bind_int $S 1 2 1666 sqlite3_step $S 1667} {SQLITE_CONSTRAINT} 1668verify_ex_errcode fkey2-19.2b SQLITE_CONSTRAINT_FOREIGNKEY 1669do_test fkey2-19.3 { 1670 sqlite3_reset $S 1671} {SQLITE_CONSTRAINT} 1672verify_ex_errcode fkey2-19.3b SQLITE_CONSTRAINT_FOREIGNKEY 1673do_test fkey2-19.4 { 1674 sqlite3_bind_int $S 1 1 1675 sqlite3_step $S 1676} {SQLITE_DONE} 1677do_test fkey2-19.4 { 1678 sqlite3_finalize $S 1679} {SQLITE_OK} 1680 1681drop_all_tables 1682do_test fkey2-20.1 { 1683 execsql { 1684 CREATE TABLE pp(a PRIMARY KEY, b); 1685 CREATE TABLE cc(c PRIMARY KEY, d REFERENCES pp); 1686 } 1687} {} 1688 1689foreach {tn insert} { 1690 1 "INSERT" 1691 2 "INSERT OR IGNORE" 1692 3 "INSERT OR ABORT" 1693 4 "INSERT OR ROLLBACK" 1694 5 "INSERT OR REPLACE" 1695 6 "INSERT OR FAIL" 1696} { 1697 do_test fkey2-20.2.$tn.1 { 1698 catchsql "$insert INTO cc VALUES(1, 2)" 1699 } {1 {FOREIGN KEY constraint failed}} 1700 do_test fkey2-20.2.$tn.2 { 1701 execsql { SELECT * FROM cc } 1702 } {} 1703 do_test fkey2-20.2.$tn.3 { 1704 execsql { 1705 BEGIN; 1706 INSERT INTO pp VALUES(2, 'two'); 1707 INSERT INTO cc VALUES(1, 2); 1708 } 1709 catchsql "$insert INTO cc VALUES(3, 4)" 1710 } {1 {FOREIGN KEY constraint failed}} 1711 do_test fkey2-20.2.$tn.4 { 1712 execsql { COMMIT ; SELECT * FROM cc } 1713 } {1 2} 1714 do_test fkey2-20.2.$tn.5 { 1715 execsql { DELETE FROM cc ; DELETE FROM pp } 1716 } {} 1717} 1718 1719foreach {tn update} { 1720 1 "UPDATE" 1721 2 "UPDATE OR IGNORE" 1722 3 "UPDATE OR ABORT" 1723 4 "UPDATE OR ROLLBACK" 1724 5 "UPDATE OR REPLACE" 1725 6 "UPDATE OR FAIL" 1726} { 1727 do_test fkey2-20.3.$tn.1 { 1728 execsql { 1729 INSERT INTO pp VALUES(2, 'two'); 1730 INSERT INTO cc VALUES(1, 2); 1731 } 1732 } {} 1733 do_test fkey2-20.3.$tn.2 { 1734 catchsql "$update pp SET a = 1" 1735 } {1 {FOREIGN KEY constraint failed}} 1736 do_test fkey2-20.3.$tn.3 { 1737 execsql { SELECT * FROM pp } 1738 } {2 two} 1739 do_test fkey2-20.3.$tn.4 { 1740 catchsql "$update cc SET d = 1" 1741 } {1 {FOREIGN KEY constraint failed}} 1742 do_test fkey2-20.3.$tn.5 { 1743 execsql { SELECT * FROM cc } 1744 } {1 2} 1745 do_test fkey2-20.3.$tn.6 { 1746 execsql { 1747 BEGIN; 1748 INSERT INTO pp VALUES(3, 'three'); 1749 } 1750 catchsql "$update pp SET a = 1 WHERE a = 2" 1751 } {1 {FOREIGN KEY constraint failed}} 1752 do_test fkey2-20.3.$tn.7 { 1753 execsql { COMMIT ; SELECT * FROM pp } 1754 } {2 two 3 three} 1755 do_test fkey2-20.3.$tn.8 { 1756 execsql { 1757 BEGIN; 1758 INSERT INTO cc VALUES(2, 2); 1759 } 1760 catchsql "$update cc SET d = 1 WHERE c = 1" 1761 } {1 {FOREIGN KEY constraint failed}} 1762 do_test fkey2-20.3.$tn.9 { 1763 execsql { COMMIT ; SELECT * FROM cc } 1764 } {1 2 2 2} 1765 do_test fkey2-20.3.$tn.10 { 1766 execsql { DELETE FROM cc ; DELETE FROM pp } 1767 } {} 1768} 1769 1770#------------------------------------------------------------------------- 1771# The following block of tests, those prefixed with "fkey2-genfkey.", are 1772# the same tests that were used to test the ".genfkey" command provided 1773# by the shell tool. So these tests show that the built-in foreign key 1774# implementation is more or less compatible with the triggers generated 1775# by genfkey. 1776# 1777drop_all_tables 1778do_test fkey2-genfkey.1.1 { 1779 execsql { 1780 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); 1781 CREATE TABLE t2(e REFERENCES t1, f); 1782 CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c)); 1783 } 1784} {} 1785do_test fkey2-genfkey.1.2 { 1786 catchsql { INSERT INTO t2 VALUES(1, 2) } 1787} {1 {FOREIGN KEY constraint failed}} 1788do_test fkey2-genfkey.1.3 { 1789 execsql { 1790 INSERT INTO t1 VALUES(1, 2, 3); 1791 INSERT INTO t2 VALUES(1, 2); 1792 } 1793} {} 1794do_test fkey2-genfkey.1.4 { 1795 execsql { INSERT INTO t2 VALUES(NULL, 3) } 1796} {} 1797do_test fkey2-genfkey.1.5 { 1798 catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL } 1799} {1 {FOREIGN KEY constraint failed}} 1800do_test fkey2-genfkey.1.6 { 1801 execsql { UPDATE t2 SET e = 1 WHERE e IS NULL } 1802} {} 1803do_test fkey2-genfkey.1.7 { 1804 execsql { UPDATE t2 SET e = NULL WHERE f = 3 } 1805} {} 1806do_test fkey2-genfkey.1.8 { 1807 catchsql { UPDATE t1 SET a = 10 } 1808} {1 {FOREIGN KEY constraint failed}} 1809do_test fkey2-genfkey.1.9 { 1810 catchsql { UPDATE t1 SET a = NULL } 1811} {1 {datatype mismatch}} 1812do_test fkey2-genfkey.1.10 { 1813 catchsql { DELETE FROM t1 } 1814} {1 {FOREIGN KEY constraint failed}} 1815do_test fkey2-genfkey.1.11 { 1816 execsql { UPDATE t2 SET e = NULL } 1817} {} 1818do_test fkey2-genfkey.1.12 { 1819 execsql { 1820 UPDATE t1 SET a = 10; 1821 DELETE FROM t1; 1822 DELETE FROM t2; 1823 } 1824} {} 1825do_test fkey2-genfkey.1.13 { 1826 execsql { 1827 INSERT INTO t3 VALUES(1, NULL, NULL); 1828 INSERT INTO t3 VALUES(1, 2, NULL); 1829 INSERT INTO t3 VALUES(1, NULL, 3); 1830 } 1831} {} 1832do_test fkey2-genfkey.1.14 { 1833 catchsql { INSERT INTO t3 VALUES(3, 1, 4) } 1834} {1 {FOREIGN KEY constraint failed}} 1835do_test fkey2-genfkey.1.15 { 1836 execsql { 1837 INSERT INTO t1 VALUES(1, 1, 4); 1838 INSERT INTO t3 VALUES(3, 1, 4); 1839 } 1840} {} 1841do_test fkey2-genfkey.1.16 { 1842 catchsql { DELETE FROM t1 } 1843} {1 {FOREIGN KEY constraint failed}} 1844do_test fkey2-genfkey.1.17 { 1845 catchsql { UPDATE t1 SET b = 10} 1846} {1 {FOREIGN KEY constraint failed}} 1847do_test fkey2-genfkey.1.18 { 1848 execsql { UPDATE t1 SET a = 10} 1849} {} 1850do_test fkey2-genfkey.1.19 { 1851 catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3} 1852} {1 {FOREIGN KEY constraint failed}} 1853 1854drop_all_tables 1855do_test fkey2-genfkey.2.1 { 1856 execsql { 1857 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); 1858 CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f); 1859 CREATE TABLE t3(g, h, i, 1860 FOREIGN KEY (h, i) 1861 REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE 1862 ); 1863 } 1864} {} 1865do_test fkey2-genfkey.2.2 { 1866 execsql { 1867 INSERT INTO t1 VALUES(1, 2, 3); 1868 INSERT INTO t1 VALUES(4, 5, 6); 1869 INSERT INTO t2 VALUES(1, 'one'); 1870 INSERT INTO t2 VALUES(4, 'four'); 1871 } 1872} {} 1873do_test fkey2-genfkey.2.3 { 1874 execsql { 1875 UPDATE t1 SET a = 2 WHERE a = 1; 1876 SELECT * FROM t2; 1877 } 1878} {2 one 4 four} 1879do_test fkey2-genfkey.2.4 { 1880 execsql { 1881 DELETE FROM t1 WHERE a = 4; 1882 SELECT * FROM t2; 1883 } 1884} {2 one} 1885 1886do_test fkey2-genfkey.2.5 { 1887 execsql { 1888 INSERT INTO t3 VALUES('hello', 2, 3); 1889 UPDATE t1 SET c = 2; 1890 SELECT * FROM t3; 1891 } 1892} {hello 2 2} 1893do_test fkey2-genfkey.2.6 { 1894 execsql { 1895 DELETE FROM t1; 1896 SELECT * FROM t3; 1897 } 1898} {} 1899 1900drop_all_tables 1901do_test fkey2-genfkey.3.1 { 1902 execsql { 1903 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b)); 1904 CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f); 1905 CREATE TABLE t3(g, h, i, 1906 FOREIGN KEY (h, i) 1907 REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL 1908 ); 1909 } 1910} {} 1911do_test fkey2-genfkey.3.2 { 1912 execsql { 1913 INSERT INTO t1 VALUES(1, 2, 3); 1914 INSERT INTO t1 VALUES(4, 5, 6); 1915 INSERT INTO t2 VALUES(1, 'one'); 1916 INSERT INTO t2 VALUES(4, 'four'); 1917 } 1918} {} 1919do_test fkey2-genfkey.3.3 { 1920 execsql { 1921 UPDATE t1 SET a = 2 WHERE a = 1; 1922 SELECT * FROM t2; 1923 } 1924} {{} one 4 four} 1925do_test fkey2-genfkey.3.4 { 1926 execsql { 1927 DELETE FROM t1 WHERE a = 4; 1928 SELECT * FROM t2; 1929 } 1930} {{} one {} four} 1931do_test fkey2-genfkey.3.5 { 1932 execsql { 1933 INSERT INTO t3 VALUES('hello', 2, 3); 1934 UPDATE t1 SET c = 2; 1935 SELECT * FROM t3; 1936 } 1937} {hello {} {}} 1938do_test fkey2-genfkey.3.6 { 1939 execsql { 1940 UPDATE t3 SET h = 2, i = 2; 1941 DELETE FROM t1; 1942 SELECT * FROM t3; 1943 } 1944} {hello {} {}} 1945 1946#------------------------------------------------------------------------- 1947# Verify that ticket dd08e5a988d00decc4a543daa8dbbfab9c577ad8 has been 1948# fixed. 1949# 1950do_test fkey2-dd08e5.1.1 { 1951 execsql { 1952 PRAGMA foreign_keys=ON; 1953 CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b); 1954 CREATE UNIQUE INDEX idd08 ON tdd08(a,b); 1955 INSERT INTO tdd08 VALUES(200,300); 1956 1957 CREATE TABLE tdd08_b(w,x,y, FOREIGN KEY(x,y) REFERENCES tdd08(a,b)); 1958 INSERT INTO tdd08_b VALUES(100,200,300); 1959 } 1960} {} 1961do_test fkey2-dd08e5.1.2 { 1962 catchsql { 1963 DELETE FROM tdd08; 1964 } 1965} {1 {FOREIGN KEY constraint failed}} 1966do_test fkey2-dd08e5.1.3 { 1967 execsql { 1968 SELECT * FROM tdd08; 1969 } 1970} {200 300} 1971do_test fkey2-dd08e5.1.4 { 1972 catchsql { 1973 INSERT INTO tdd08_b VALUES(400,500,300); 1974 } 1975} {1 {FOREIGN KEY constraint failed}} 1976do_test fkey2-dd08e5.1.5 { 1977 catchsql { 1978 UPDATE tdd08_b SET x=x+1; 1979 } 1980} {1 {FOREIGN KEY constraint failed}} 1981do_test fkey2-dd08e5.1.6 { 1982 catchsql { 1983 UPDATE tdd08 SET a=a+1; 1984 } 1985} {1 {FOREIGN KEY constraint failed}} 1986 1987#------------------------------------------------------------------------- 1988# Verify that ticket ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba 1989# fixed. 1990# 1991do_test fkey2-ce7c13.1.1 { 1992 execsql { 1993 CREATE TABLE tce71(a INTEGER PRIMARY KEY, b); 1994 CREATE UNIQUE INDEX ice71 ON tce71(a,b); 1995 INSERT INTO tce71 VALUES(100,200); 1996 CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b)); 1997 INSERT INTO tce72 VALUES(300,100,200); 1998 UPDATE tce71 set b = 200 where a = 100; 1999 SELECT * FROM tce71, tce72; 2000 } 2001} {100 200 300 100 200} 2002do_test fkey2-ce7c13.1.2 { 2003 catchsql { 2004 UPDATE tce71 set b = 201 where a = 100; 2005 } 2006} {1 {FOREIGN KEY constraint failed}} 2007do_test fkey2-ce7c13.1.3 { 2008 catchsql { 2009 UPDATE tce71 set a = 101 where a = 100; 2010 } 2011} {1 {FOREIGN KEY constraint failed}} 2012do_test fkey2-ce7c13.1.4 { 2013 execsql { 2014 CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b)); 2015 INSERT INTO tce73 VALUES(100,200); 2016 CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b)); 2017 INSERT INTO tce74 VALUES(300,100,200); 2018 UPDATE tce73 set b = 200 where a = 100; 2019 SELECT * FROM tce73, tce74; 2020 } 2021} {100 200 300 100 200} 2022do_test fkey2-ce7c13.1.5 { 2023 catchsql { 2024 UPDATE tce73 set b = 201 where a = 100; 2025 } 2026} {1 {FOREIGN KEY constraint failed}} 2027do_test fkey2-ce7c13.1.6 { 2028 catchsql { 2029 UPDATE tce73 set a = 101 where a = 100; 2030 } 2031} {1 {FOREIGN KEY constraint failed}} 2032 2033# 2015-04-16: Foreign key errors propagate back up to the parser. 2034# 2035do_test fkey2-20150416-100 { 2036 db close 2037 sqlite3 db :memory: 2038 catchsql { 2039 PRAGMA foreign_keys=1; 2040 CREATE TABLE t1(x PRIMARY KEY); 2041 CREATE TABLE t(y REFERENCES t0(x)ON DELETE SET DEFAULT); 2042 CREATE TABLE t0(y REFERENCES t1 ON DELETE SET NULL); 2043 REPLACE INTO t1 SELECT(0);CREATE TABLE t2(x);CREATE TABLE t3; 2044 } 2045} {1 {foreign key mismatch - "t" referencing "t0"}} 2046 2047finish_test 2048