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