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 # Repeat for TEMP tables 1008 # 1009 drop_all_tables 1010 do_test fkey2-14.1tmp.1 { 1011 # Adding a column with a REFERENCES clause is not supported. 1012 execsql { 1013 CREATE TEMP TABLE t1(a PRIMARY KEY); 1014 CREATE TEMP TABLE t2(a, b); 1015 } 1016 catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 } 1017 } {0 {}} 1018 do_test fkey2-14.1tmp.2 { 1019 catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 } 1020 } {0 {}} 1021 do_test fkey2-14.1tmp.3 { 1022 catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL} 1023 } {0 {}} 1024 do_test fkey2-14.1tmp.4 { 1025 catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'} 1026 } {1 {Cannot add a REFERENCES column with non-NULL default value}} 1027 do_test fkey2-14.1tmp.5 { 1028 catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 } 1029 } {1 {Cannot add a REFERENCES column with non-NULL default value}} 1030 do_test fkey2-14.1tmp.6 { 1031 execsql { 1032 PRAGMA foreign_keys = off; 1033 ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1; 1034 PRAGMA foreign_keys = on; 1035 SELECT sql FROM sqlite_temp_master WHERE name='t2'; 1036 } 1037 } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}} 1038 1039 do_test fkey2-14.2tmp.1.1 { 1040 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3 1041 } {{CREATE TABLE t1(a REFERENCES "t3")}} 1042 do_test fkey2-14.2tmp.1.2 { 1043 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3 1044 } {{CREATE TABLE t1(a REFERENCES t2)}} 1045 do_test fkey2-14.2tmp.1.3 { 1046 test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3 1047 } {{CREATE TABLE t1(a REFERENCES "t3")}} 1048 1049 # Test ALTER TABLE RENAME TABLE a bit. 1050 # 1051 do_test fkey2-14.2tmp.2.1 { 1052 drop_all_tables 1053 execsql { 1054 CREATE TEMP TABLE t1(a PRIMARY KEY, b REFERENCES t1); 1055 CREATE TEMP TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2); 1056 CREATE TEMP TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1); 1057 } 1058 execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'} 1059 } [list \ 1060 {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \ 1061 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \ 1062 {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \ 1063 ] 1064 do_test fkey2-14.2tmp.2.2 { 1065 execsql { ALTER TABLE t1 RENAME TO t4 } 1066 execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'} 1067 } [list \ 1068 {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \ 1069 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \ 1070 {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \ 1071 ] 1072 do_test fkey2-14.2tmp.2.3 { 1073 catchsql { INSERT INTO t3 VALUES(1, 2, 3) } 1074 } {1 {foreign key constraint failed}} 1075 do_test fkey2-14.2tmp.2.4 { 1076 execsql { INSERT INTO t4 VALUES(1, NULL) } 1077 } {} 1078 do_test fkey2-14.2tmp.2.5 { 1079 catchsql { UPDATE t4 SET b = 5 } 1080 } {1 {foreign key constraint failed}} 1081 do_test fkey2-14.2tmp.2.6 { 1082 catchsql { UPDATE t4 SET b = 1 } 1083 } {0 {}} 1084 do_test fkey2-14.2tmp.2.7 { 1085 execsql { INSERT INTO t3 VALUES(1, NULL, 1) } 1086 } {} 1087 1088 # Repeat for ATTACH-ed tables 1089 # 1090 drop_all_tables 1091 do_test fkey2-14.1aux.1 { 1092 # Adding a column with a REFERENCES clause is not supported. 1093 execsql { 1094 ATTACH ':memory:' AS aux; 1095 CREATE TABLE aux.t1(a PRIMARY KEY); 1096 CREATE TABLE aux.t2(a, b); 1097 } 1098 catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 } 1099 } {0 {}} 1100 do_test fkey2-14.1aux.2 { 1101 catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 } 1102 } {0 {}} 1103 do_test fkey2-14.1aux.3 { 1104 catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL} 1105 } {0 {}} 1106 do_test fkey2-14.1aux.4 { 1107 catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'} 1108 } {1 {Cannot add a REFERENCES column with non-NULL default value}} 1109 do_test fkey2-14.1aux.5 { 1110 catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 } 1111 } {1 {Cannot add a REFERENCES column with non-NULL default value}} 1112 do_test fkey2-14.1aux.6 { 1113 execsql { 1114 PRAGMA foreign_keys = off; 1115 ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1; 1116 PRAGMA foreign_keys = on; 1117 SELECT sql FROM aux.sqlite_master WHERE name='t2'; 1118 } 1119 } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}} 1120 1121 do_test fkey2-14.2aux.1.1 { 1122 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3 1123 } {{CREATE TABLE t1(a REFERENCES "t3")}} 1124 do_test fkey2-14.2aux.1.2 { 1125 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3 1126 } {{CREATE TABLE t1(a REFERENCES t2)}} 1127 do_test fkey2-14.2aux.1.3 { 1128 test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3 1129 } {{CREATE TABLE t1(a REFERENCES "t3")}} 1130 1131 # Test ALTER TABLE RENAME TABLE a bit. 1132 # 1133 do_test fkey2-14.2aux.2.1 { 1134 drop_all_tables 1135 execsql { 1136 CREATE TABLE aux.t1(a PRIMARY KEY, b REFERENCES t1); 1137 CREATE TABLE aux.t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2); 1138 CREATE TABLE aux.t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1); 1139 } 1140 execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'} 1141 } [list \ 1142 {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \ 1143 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \ 1144 {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \ 1145 ] 1146 do_test fkey2-14.2aux.2.2 { 1147 execsql { ALTER TABLE t1 RENAME TO t4 } 1148 execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'} 1149 } [list \ 1150 {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \ 1151 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \ 1152 {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \ 1153 ] 1154 do_test fkey2-14.2aux.2.3 { 1155 catchsql { INSERT INTO t3 VALUES(1, 2, 3) } 1156 } {1 {foreign key constraint failed}} 1157 do_test fkey2-14.2aux.2.4 { 1158 execsql { INSERT INTO t4 VALUES(1, NULL) } 1159 } {} 1160 do_test fkey2-14.2aux.2.5 { 1161 catchsql { UPDATE t4 SET b = 5 } 1162 } {1 {foreign key constraint failed}} 1163 do_test fkey2-14.2aux.2.6 { 1164 catchsql { UPDATE t4 SET b = 1 } 1165 } {0 {}} 1166 do_test fkey2-14.2aux.2.7 { 1167 execsql { INSERT INTO t3 VALUES(1, NULL, 1) } 1168 } {} 1169} 1170 1171do_test fkey-2.14.3.1 { 1172 drop_all_tables 1173 execsql { 1174 CREATE TABLE t1(a, b REFERENCES nosuchtable); 1175 DROP TABLE t1; 1176 } 1177} {} 1178do_test fkey-2.14.3.2 { 1179 execsql { 1180 CREATE TABLE t1(a PRIMARY KEY, b); 1181 INSERT INTO t1 VALUES('a', 1); 1182 CREATE TABLE t2(x REFERENCES t1); 1183 INSERT INTO t2 VALUES('a'); 1184 } 1185} {} 1186do_test fkey-2.14.3.3 { 1187 catchsql { DROP TABLE t1 } 1188} {1 {foreign key constraint failed}} 1189do_test fkey-2.14.3.4 { 1190 execsql { 1191 DELETE FROM t2; 1192 DROP TABLE t1; 1193 } 1194} {} 1195do_test fkey-2.14.3.4 { 1196 catchsql { INSERT INTO t2 VALUES('x') } 1197} {1 {no such table: main.t1}} 1198do_test fkey-2.14.3.5 { 1199 execsql { 1200 CREATE TABLE t1(x PRIMARY KEY); 1201 INSERT INTO t1 VALUES('x'); 1202 } 1203 execsql { INSERT INTO t2 VALUES('x') } 1204} {} 1205do_test fkey-2.14.3.6 { 1206 catchsql { DROP TABLE t1 } 1207} {1 {foreign key constraint failed}} 1208do_test fkey-2.14.3.7 { 1209 execsql { 1210 DROP TABLE t2; 1211 DROP TABLE t1; 1212 } 1213} {} 1214do_test fkey-2.14.3.8 { 1215 execsql { 1216 CREATE TABLE pp(x, y, PRIMARY KEY(x, y)); 1217 CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z)); 1218 } 1219 catchsql { INSERT INTO cc VALUES(1, 2) } 1220} {1 {foreign key mismatch}} 1221do_test fkey-2.14.3.9 { 1222 execsql { DROP TABLE cc } 1223} {} 1224do_test fkey-2.14.3.10 { 1225 execsql { 1226 CREATE TABLE cc(a, b, 1227 FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED 1228 ); 1229 } 1230 execsql { 1231 INSERT INTO pp VALUES('a', 'b'); 1232 INSERT INTO cc VALUES('a', 'b'); 1233 BEGIN; 1234 DROP TABLE pp; 1235 CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c)); 1236 INSERT INTO pp VALUES(1, 'a', 'b'); 1237 COMMIT; 1238 } 1239} {} 1240do_test fkey-2.14.3.11 { 1241 execsql { 1242 BEGIN; 1243 DROP TABLE cc; 1244 DROP TABLE pp; 1245 COMMIT; 1246 } 1247} {} 1248do_test fkey-2.14.3.12 { 1249 execsql { 1250 CREATE TABLE b1(a, b); 1251 CREATE TABLE b2(a, b REFERENCES b1); 1252 DROP TABLE b1; 1253 } 1254} {} 1255do_test fkey-2.14.3.13 { 1256 execsql { 1257 CREATE TABLE b3(a, b REFERENCES b2 DEFERRABLE INITIALLY DEFERRED); 1258 DROP TABLE b2; 1259 } 1260} {} 1261 1262# Test that nothing goes wrong when dropping a table that refers to a view. 1263# Or dropping a view that an existing FK (incorrectly) refers to. Or either 1264# of the above scenarios with a virtual table. 1265drop_all_tables 1266do_test fkey-2.14.4.1 { 1267 execsql { 1268 CREATE TABLE t1(x REFERENCES v); 1269 CREATE VIEW v AS SELECT * FROM t1; 1270 } 1271} {} 1272do_test fkey-2.14.4.2 { 1273 execsql { 1274 DROP VIEW v; 1275 } 1276} {} 1277ifcapable vtab { 1278 register_echo_module db 1279 do_test fkey-2.14.4.3 { 1280 execsql { CREATE VIRTUAL TABLE v USING echo(t1) } 1281 } {} 1282 do_test fkey-2.14.4.2 { 1283 execsql { 1284 DROP TABLE v; 1285 } 1286 } {} 1287} 1288 1289#------------------------------------------------------------------------- 1290# The following tests, fkey2-15.*, test that unnecessary FK related scans 1291# and lookups are avoided when the constraint counters are zero. 1292# 1293drop_all_tables 1294proc execsqlS {zSql} { 1295 set ::sqlite_search_count 0 1296 set ::sqlite_found_count 0 1297 set res [uplevel [list execsql $zSql]] 1298 concat [expr $::sqlite_found_count + $::sqlite_search_count] $res 1299} 1300do_test fkey2-15.1.1 { 1301 execsql { 1302 CREATE TABLE pp(a PRIMARY KEY, b); 1303 CREATE TABLE cc(x, y REFERENCES pp DEFERRABLE INITIALLY DEFERRED); 1304 INSERT INTO pp VALUES(1, 'one'); 1305 INSERT INTO pp VALUES(2, 'two'); 1306 INSERT INTO cc VALUES('neung', 1); 1307 INSERT INTO cc VALUES('song', 2); 1308 } 1309} {} 1310do_test fkey2-15.1.2 { 1311 execsqlS { INSERT INTO pp VALUES(3, 'three') } 1312} {0} 1313do_test fkey2-15.1.3 { 1314 execsql { 1315 BEGIN; 1316 INSERT INTO cc VALUES('see', 4); -- Violates deferred constraint 1317 } 1318 execsqlS { INSERT INTO pp VALUES(5, 'five') } 1319} {2} 1320do_test fkey2-15.1.4 { 1321 execsql { DELETE FROM cc WHERE x = 'see' } 1322 execsqlS { INSERT INTO pp VALUES(6, 'six') } 1323} {0} 1324do_test fkey2-15.1.5 { 1325 execsql COMMIT 1326} {} 1327do_test fkey2-15.1.6 { 1328 execsql BEGIN 1329 execsqlS { 1330 DELETE FROM cc WHERE x = 'neung'; 1331 ROLLBACK; 1332 } 1333} {1} 1334do_test fkey2-15.1.7 { 1335 execsql { 1336 BEGIN; 1337 DELETE FROM pp WHERE a = 2; 1338 } 1339 execsqlS { 1340 DELETE FROM cc WHERE x = 'neung'; 1341 ROLLBACK; 1342 } 1343} {2} 1344 1345#------------------------------------------------------------------------- 1346# This next block of tests, fkey2-16.*, test that rows that refer to 1347# themselves may be inserted and deleted. 1348# 1349foreach {tn zSchema} { 1350 1 { CREATE TABLE self(a INTEGER PRIMARY KEY, b REFERENCES self(a)) } 1351 2 { CREATE TABLE self(a PRIMARY KEY, b REFERENCES self(a)) } 1352 3 { CREATE TABLE self(a UNIQUE, b INTEGER PRIMARY KEY REFERENCES self(a)) } 1353} { 1354 drop_all_tables 1355 do_test fkey2-16.1.$tn.1 { 1356 execsql $zSchema 1357 execsql { INSERT INTO self VALUES(13, 13) } 1358 } {} 1359 do_test fkey2-16.1.$tn.2 { 1360 execsql { UPDATE self SET a = 14, b = 14 } 1361 } {} 1362 1363 do_test fkey2-16.1.$tn.3 { 1364 catchsql { UPDATE self SET b = 15 } 1365 } {1 {foreign key constraint failed}} 1366 1367 do_test fkey2-16.1.$tn.4 { 1368 catchsql { UPDATE self SET a = 15 } 1369 } {1 {foreign key constraint failed}} 1370 1371 do_test fkey2-16.1.$tn.5 { 1372 catchsql { UPDATE self SET a = 15, b = 16 } 1373 } {1 {foreign key constraint failed}} 1374 1375 do_test fkey2-16.1.$tn.6 { 1376 catchsql { UPDATE self SET a = 17, b = 17 } 1377 } {0 {}} 1378 1379 do_test fkey2-16.1.$tn.7 { 1380 execsql { DELETE FROM self } 1381 } {} 1382 do_test fkey2-16.1.$tn.8 { 1383 catchsql { INSERT INTO self VALUES(20, 21) } 1384 } {1 {foreign key constraint failed}} 1385} 1386 1387#------------------------------------------------------------------------- 1388# This next block of tests, fkey2-17.*, tests that if "PRAGMA count_changes" 1389# is turned on statements that violate immediate FK constraints return 1390# SQLITE_CONSTRAINT immediately, not after returning a number of rows. 1391# Whereas statements that violate deferred FK constraints return the number 1392# of rows before failing. 1393# 1394# Also test that rows modified by FK actions are not counted in either the 1395# returned row count or the values returned by sqlite3_changes(). Like 1396# trigger related changes, they are included in sqlite3_total_changes() though. 1397# 1398drop_all_tables 1399do_test fkey2-17.1.1 { 1400 execsql { PRAGMA count_changes = 1 } 1401 execsql { 1402 CREATE TABLE one(a, b, c, UNIQUE(b, c)); 1403 CREATE TABLE two(d, e, f, FOREIGN KEY(e, f) REFERENCES one(b, c)); 1404 INSERT INTO one VALUES(1, 2, 3); 1405 } 1406} {1} 1407do_test fkey2-17.1.2 { 1408 set STMT [sqlite3_prepare_v2 db "INSERT INTO two VALUES(4, 5, 6)" -1 dummy] 1409 sqlite3_step $STMT 1410} {SQLITE_CONSTRAINT} 1411do_test fkey2-17.1.3 { 1412 sqlite3_step $STMT 1413} {SQLITE_MISUSE} 1414do_test fkey2-17.1.4 { 1415 sqlite3_finalize $STMT 1416} {SQLITE_CONSTRAINT} 1417do_test fkey2-17.1.5 { 1418 execsql { 1419 INSERT INTO one VALUES(2, 3, 4); 1420 INSERT INTO one VALUES(3, 4, 5); 1421 INSERT INTO two VALUES(1, 2, 3); 1422 INSERT INTO two VALUES(2, 3, 4); 1423 INSERT INTO two VALUES(3, 4, 5); 1424 } 1425} {1 1 1 1 1} 1426do_test fkey2-17.1.6 { 1427 catchsql { 1428 BEGIN; 1429 INSERT INTO one VALUES(0, 0, 0); 1430 UPDATE two SET e=e+1, f=f+1; 1431 } 1432} {1 {foreign key constraint failed}} 1433do_test fkey2-17.1.7 { 1434 execsql { SELECT * FROM one } 1435} {1 2 3 2 3 4 3 4 5 0 0 0} 1436do_test fkey2-17.1.8 { 1437 execsql { SELECT * FROM two } 1438} {1 2 3 2 3 4 3 4 5} 1439do_test fkey2-17.1.9 { 1440 execsql COMMIT 1441} {} 1442do_test fkey2-17.1.10 { 1443 execsql { 1444 CREATE TABLE three( 1445 g, h, i, 1446 FOREIGN KEY(h, i) REFERENCES one(b, c) DEFERRABLE INITIALLY DEFERRED 1447 ); 1448 } 1449} {} 1450do_test fkey2-17.1.11 { 1451 set STMT [sqlite3_prepare_v2 db "INSERT INTO three VALUES(7, 8, 9)" -1 dummy] 1452 sqlite3_step $STMT 1453} {SQLITE_ROW} 1454do_test fkey2-17.1.12 { 1455 sqlite3_column_text $STMT 0 1456} {1} 1457do_test fkey2-17.1.13 { 1458 sqlite3_step $STMT 1459} {SQLITE_CONSTRAINT} 1460do_test fkey2-17.1.14 { 1461 sqlite3_finalize $STMT 1462} {SQLITE_CONSTRAINT} 1463 1464drop_all_tables 1465do_test fkey2-17.2.1 { 1466 execsql { 1467 CREATE TABLE high("a'b!" PRIMARY KEY, b); 1468 CREATE TABLE low( 1469 c, 1470 "d&6" REFERENCES high ON UPDATE CASCADE ON DELETE CASCADE 1471 ); 1472 } 1473} {} 1474do_test fkey2-17.2.2 { 1475 execsql { 1476 INSERT INTO high VALUES('a', 'b'); 1477 INSERT INTO low VALUES('b', 'a'); 1478 } 1479 db changes 1480} {1} 1481set nTotal [db total_changes] 1482do_test fkey2-17.2.3 { 1483 execsql { UPDATE high SET "a'b!" = 'c' } 1484} {1} 1485do_test fkey2-17.2.4 { 1486 db changes 1487} {1} 1488do_test fkey2-17.2.5 { 1489 expr [db total_changes] - $nTotal 1490} {2} 1491do_test fkey2-17.2.6 { 1492 execsql { SELECT * FROM high ; SELECT * FROM low } 1493} {c b b c} 1494do_test fkey2-17.2.7 { 1495 execsql { DELETE FROM high } 1496} {1} 1497do_test fkey2-17.2.8 { 1498 db changes 1499} {1} 1500do_test fkey2-17.2.9 { 1501 expr [db total_changes] - $nTotal 1502} {4} 1503do_test fkey2-17.2.10 { 1504 execsql { SELECT * FROM high ; SELECT * FROM low } 1505} {} 1506execsql { PRAGMA count_changes = 0 } 1507 1508#------------------------------------------------------------------------- 1509# Test that the authorization callback works. 1510# 1511 1512ifcapable auth { 1513 do_test fkey2-18.1 { 1514 execsql { 1515 CREATE TABLE long(a, b PRIMARY KEY, c); 1516 CREATE TABLE short(d, e, f REFERENCES long); 1517 CREATE TABLE mid(g, h, i REFERENCES long DEFERRABLE INITIALLY DEFERRED); 1518 } 1519 } {} 1520 1521 proc auth {args} {eval lappend ::authargs $args ; return SQLITE_OK} 1522 db auth auth 1523 1524 # An insert on the parent table must read the child key of any deferred 1525 # foreign key constraints. But not the child key of immediate constraints. 1526 set authargs {} 1527 do_test fkey2-18.2 { 1528 execsql { INSERT INTO long VALUES(1, 2, 3) } 1529 set authargs 1530 } {SQLITE_INSERT long {} main {} SQLITE_READ mid i main {}} 1531 1532 # An insert on the child table of an immediate constraint must read the 1533 # parent key columns (to see if it is a violation or not). 1534 set authargs {} 1535 do_test fkey2-18.3 { 1536 execsql { INSERT INTO short VALUES(1, 3, 2) } 1537 set authargs 1538 } {SQLITE_INSERT short {} main {} SQLITE_READ long b main {}} 1539 1540 # As must an insert on the child table of a deferred constraint. 1541 set authargs {} 1542 do_test fkey2-18.4 { 1543 execsql { INSERT INTO mid VALUES(1, 3, 2) } 1544 set authargs 1545 } {SQLITE_INSERT mid {} main {} SQLITE_READ long b main {}} 1546 1547 do_test fkey2-18.5 { 1548 execsql { 1549 CREATE TABLE nought(a, b PRIMARY KEY, c); 1550 CREATE TABLE cross(d, e, f, 1551 FOREIGN KEY(e) REFERENCES nought(b) ON UPDATE CASCADE 1552 ); 1553 } 1554 execsql { INSERT INTO nought VALUES(2, 1, 2) } 1555 execsql { INSERT INTO cross VALUES(0, 1, 0) } 1556 set authargs [list] 1557 execsql { UPDATE nought SET b = 5 } 1558 set authargs 1559 } {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 {}} 1560 1561 do_test fkey2-18.6 { 1562 execsql {SELECT * FROM cross} 1563 } {0 5 0} 1564 1565 do_test fkey2-18.7 { 1566 execsql { 1567 CREATE TABLE one(a INTEGER PRIMARY KEY, b); 1568 CREATE TABLE two(b, c REFERENCES one); 1569 INSERT INTO one VALUES(101, 102); 1570 } 1571 set authargs [list] 1572 execsql { INSERT INTO two VALUES(100, 101); } 1573 set authargs 1574 } {SQLITE_INSERT two {} main {} SQLITE_READ one a main {}} 1575 1576 # Return SQLITE_IGNORE to requests to read from the parent table. This 1577 # causes inserts of non-NULL keys into the child table to fail. 1578 # 1579 rename auth {} 1580 proc auth {args} { 1581 if {[lindex $args 1] == "long"} {return SQLITE_IGNORE} 1582 return SQLITE_OK 1583 } 1584 do_test fkey2-18.8 { 1585 catchsql { INSERT INTO short VALUES(1, 3, 2) } 1586 } {1 {foreign key constraint failed}} 1587 do_test fkey2-18.9 { 1588 execsql { INSERT INTO short VALUES(1, 3, NULL) } 1589 } {} 1590 do_test fkey2-18.10 { 1591 execsql { SELECT * FROM short } 1592 } {1 3 2 1 3 {}} 1593 do_test fkey2-18.11 { 1594 catchsql { UPDATE short SET f = 2 WHERE f IS NULL } 1595 } {1 {foreign key constraint failed}} 1596 1597 db auth {} 1598 unset authargs 1599} 1600 1601#------------------------------------------------------------------------- 1602# The following block of tests, those prefixed with "fkey2-genfkey.", are 1603# the same tests that were used to test the ".genfkey" command provided 1604# by the shell tool. So these tests show that the built-in foreign key 1605# implementation is more or less compatible with the triggers generated 1606# by genfkey. 1607# 1608drop_all_tables 1609do_test fkey2-genfkey.1.1 { 1610 execsql { 1611 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); 1612 CREATE TABLE t2(e REFERENCES t1, f); 1613 CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c)); 1614 } 1615} {} 1616do_test fkey2-genfkey.1.2 { 1617 catchsql { INSERT INTO t2 VALUES(1, 2) } 1618} {1 {foreign key constraint failed}} 1619do_test fkey2-genfkey.1.3 { 1620 execsql { 1621 INSERT INTO t1 VALUES(1, 2, 3); 1622 INSERT INTO t2 VALUES(1, 2); 1623 } 1624} {} 1625do_test fkey2-genfkey.1.4 { 1626 execsql { INSERT INTO t2 VALUES(NULL, 3) } 1627} {} 1628do_test fkey2-genfkey.1.5 { 1629 catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL } 1630} {1 {foreign key constraint failed}} 1631do_test fkey2-genfkey.1.6 { 1632 execsql { UPDATE t2 SET e = 1 WHERE e IS NULL } 1633} {} 1634do_test fkey2-genfkey.1.7 { 1635 execsql { UPDATE t2 SET e = NULL WHERE f = 3 } 1636} {} 1637do_test fkey2-genfkey.1.8 { 1638 catchsql { UPDATE t1 SET a = 10 } 1639} {1 {foreign key constraint failed}} 1640do_test fkey2-genfkey.1.9 { 1641 catchsql { UPDATE t1 SET a = NULL } 1642} {1 {datatype mismatch}} 1643do_test fkey2-genfkey.1.10 { 1644 catchsql { DELETE FROM t1 } 1645} {1 {foreign key constraint failed}} 1646do_test fkey2-genfkey.1.11 { 1647 execsql { UPDATE t2 SET e = NULL } 1648} {} 1649do_test fkey2-genfkey.1.12 { 1650 execsql { 1651 UPDATE t1 SET a = 10; 1652 DELETE FROM t1; 1653 DELETE FROM t2; 1654 } 1655} {} 1656do_test fkey2-genfkey.1.13 { 1657 execsql { 1658 INSERT INTO t3 VALUES(1, NULL, NULL); 1659 INSERT INTO t3 VALUES(1, 2, NULL); 1660 INSERT INTO t3 VALUES(1, NULL, 3); 1661 } 1662} {} 1663do_test fkey2-genfkey.1.14 { 1664 catchsql { INSERT INTO t3 VALUES(3, 1, 4) } 1665} {1 {foreign key constraint failed}} 1666do_test fkey2-genfkey.1.15 { 1667 execsql { 1668 INSERT INTO t1 VALUES(1, 1, 4); 1669 INSERT INTO t3 VALUES(3, 1, 4); 1670 } 1671} {} 1672do_test fkey2-genfkey.1.16 { 1673 catchsql { DELETE FROM t1 } 1674} {1 {foreign key constraint failed}} 1675do_test fkey2-genfkey.1.17 { 1676 catchsql { UPDATE t1 SET b = 10} 1677} {1 {foreign key constraint failed}} 1678do_test fkey2-genfkey.1.18 { 1679 execsql { UPDATE t1 SET a = 10} 1680} {} 1681do_test fkey2-genfkey.1.19 { 1682 catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3} 1683} {1 {foreign key constraint failed}} 1684 1685drop_all_tables 1686do_test fkey2-genfkey.2.1 { 1687 execsql { 1688 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); 1689 CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f); 1690 CREATE TABLE t3(g, h, i, 1691 FOREIGN KEY (h, i) 1692 REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE 1693 ); 1694 } 1695} {} 1696do_test fkey2-genfkey.2.2 { 1697 execsql { 1698 INSERT INTO t1 VALUES(1, 2, 3); 1699 INSERT INTO t1 VALUES(4, 5, 6); 1700 INSERT INTO t2 VALUES(1, 'one'); 1701 INSERT INTO t2 VALUES(4, 'four'); 1702 } 1703} {} 1704do_test fkey2-genfkey.2.3 { 1705 execsql { 1706 UPDATE t1 SET a = 2 WHERE a = 1; 1707 SELECT * FROM t2; 1708 } 1709} {2 one 4 four} 1710do_test fkey2-genfkey.2.4 { 1711 execsql { 1712 DELETE FROM t1 WHERE a = 4; 1713 SELECT * FROM t2; 1714 } 1715} {2 one} 1716 1717do_test fkey2-genfkey.2.5 { 1718 execsql { 1719 INSERT INTO t3 VALUES('hello', 2, 3); 1720 UPDATE t1 SET c = 2; 1721 SELECT * FROM t3; 1722 } 1723} {hello 2 2} 1724do_test fkey2-genfkey.2.6 { 1725 execsql { 1726 DELETE FROM t1; 1727 SELECT * FROM t3; 1728 } 1729} {} 1730 1731drop_all_tables 1732do_test fkey2-genfkey.3.1 { 1733 execsql { 1734 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b)); 1735 CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f); 1736 CREATE TABLE t3(g, h, i, 1737 FOREIGN KEY (h, i) 1738 REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL 1739 ); 1740 } 1741} {} 1742do_test fkey2-genfkey.3.2 { 1743 execsql { 1744 INSERT INTO t1 VALUES(1, 2, 3); 1745 INSERT INTO t1 VALUES(4, 5, 6); 1746 INSERT INTO t2 VALUES(1, 'one'); 1747 INSERT INTO t2 VALUES(4, 'four'); 1748 } 1749} {} 1750do_test fkey2-genfkey.3.3 { 1751 execsql { 1752 UPDATE t1 SET a = 2 WHERE a = 1; 1753 SELECT * FROM t2; 1754 } 1755} {{} one 4 four} 1756do_test fkey2-genfkey.3.4 { 1757 execsql { 1758 DELETE FROM t1 WHERE a = 4; 1759 SELECT * FROM t2; 1760 } 1761} {{} one {} four} 1762do_test fkey2-genfkey.3.5 { 1763 execsql { 1764 INSERT INTO t3 VALUES('hello', 2, 3); 1765 UPDATE t1 SET c = 2; 1766 SELECT * FROM t3; 1767 } 1768} {hello {} {}} 1769do_test fkey2-genfkey.3.6 { 1770 execsql { 1771 UPDATE t3 SET h = 2, i = 2; 1772 DELETE FROM t1; 1773 SELECT * FROM t3; 1774 } 1775} {hello {} {}} 1776 1777finish_test 1778