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