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