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