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