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