| 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 do_execsql_test fkey2-9.3.0 { | |
| 680 CREATE TABLE t3(x PRIMARY KEY REFERENCES t3 ON DELETE SET NULL); | |
| 681 INSERT INTO t3(x) VALUES(12345); | |
| 682 DROP TABLE t3; | |
| 683 } {} | |
| 684 | |
| 685 #------------------------------------------------------------------------- | |
| 686 # The following tests, fkey2-10.*, test "foreign key mismatch" and | |
| 687 # other errors. | |
| 688 # | |
| 689 set tn 0 | |
| 690 foreach zSql [list { | |
| 691 CREATE TABLE p(a PRIMARY KEY, b); | |
| 692 CREATE TABLE c(x REFERENCES p(c)); | |
| 693 } { | |
| 694 CREATE TABLE c(x REFERENCES v(y)); | |
| 695 CREATE VIEW v AS SELECT x AS y FROM c; | |
| 696 } { | |
| 697 CREATE TABLE p(a, b, PRIMARY KEY(a, b)); | |
| 698 CREATE TABLE c(x REFERENCES p); | |
| 699 } { | |
| 700 CREATE TABLE p(a COLLATE binary, b); | |
| 701 CREATE UNIQUE INDEX i ON p(a COLLATE nocase); | |
| 702 CREATE TABLE c(x REFERENCES p(a)); | |
| 703 }] { | |
| 704 drop_all_tables | |
| 705 do_test fkey2-10.1.[incr tn] { | |
| 706 execsql $zSql | |
| 707 catchsql { INSERT INTO c DEFAULT VALUES } | |
| 708 } {/1 {foreign key mismatch - "c" referencing "."}/} | |
| 709 } | |
| 710 | |
| 711 # "rowid" cannot be used as part of a child or parent key definition | |
| 712 # unless it happens to be the name of an explicitly declared column. | |
| 713 # | |
| 714 do_test fkey2-10.2.1 { | |
| 715 drop_all_tables | |
| 716 catchsql { | |
| 717 CREATE TABLE t1(a PRIMARY KEY, b); | |
| 718 CREATE TABLE t2(c, d, FOREIGN KEY(rowid) REFERENCES t1(a)); | |
| 719 } | |
| 720 } {1 {unknown column "rowid" in foreign key definition}} | |
| 721 do_test fkey2-10.2.2 { | |
| 722 drop_all_tables | |
| 723 catchsql { | |
| 724 CREATE TABLE t1(a PRIMARY KEY, b); | |
| 725 CREATE TABLE t2(rowid, d, FOREIGN KEY(rowid) REFERENCES t1(a)); | |
| 726 } | |
| 727 } {0 {}} | |
| 728 do_test fkey2-10.2.1 { | |
| 729 drop_all_tables | |
| 730 catchsql { | |
| 731 CREATE TABLE t1(a, b); | |
| 732 CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid)); | |
| 733 INSERT INTO t1(rowid, a, b) VALUES(1, 1, 1); | |
| 734 INSERT INTO t2 VALUES(1, 1); | |
| 735 } | |
| 736 } {1 {foreign key mismatch - "t2" referencing "t1"}} | |
| 737 do_test fkey2-10.2.2 { | |
| 738 drop_all_tables | |
| 739 catchsql { | |
| 740 CREATE TABLE t1(rowid PRIMARY KEY, b); | |
| 741 CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid)); | |
| 742 INSERT INTO t1(rowid, b) VALUES(1, 1); | |
| 743 INSERT INTO t2 VALUES(1, 1); | |
| 744 } | |
| 745 } {0 {}} | |
| 746 | |
| 747 | |
| 748 #------------------------------------------------------------------------- | |
| 749 # The following tests, fkey2-11.*, test CASCADE actions. | |
| 750 # | |
| 751 drop_all_tables | |
| 752 do_test fkey2-11.1.1 { | |
| 753 execsql { | |
| 754 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, rowid, _rowid_, oid); | |
| 755 CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE); | |
| 756 | |
| 757 INSERT INTO t1 VALUES(10, 100, 'abc', 'def', 'ghi'); | |
| 758 INSERT INTO t2 VALUES(10, 100); | |
| 759 UPDATE t1 SET a = 15; | |
| 760 SELECT * FROM t2; | |
| 761 } | |
| 762 } {15 100} | |
| 763 | |
| 764 #------------------------------------------------------------------------- | |
| 765 # The following tests, fkey2-12.*, test RESTRICT actions. | |
| 766 # | |
| 767 drop_all_tables | |
| 768 do_test fkey2-12.1.1 { | |
| 769 execsql { | |
| 770 CREATE TABLE t1(a, b PRIMARY KEY); | |
| 771 CREATE TABLE t2( | |
| 772 x REFERENCES t1 ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED | |
| 773 ); | |
| 774 INSERT INTO t1 VALUES(1, 'one'); | |
| 775 INSERT INTO t1 VALUES(2, 'two'); | |
| 776 INSERT INTO t1 VALUES(3, 'three'); | |
| 777 } | |
| 778 } {} | |
| 779 do_test fkey2-12.1.2 { | |
| 780 execsql "BEGIN" | |
| 781 execsql "INSERT INTO t2 VALUES('two')" | |
| 782 } {} | |
| 783 do_test fkey2-12.1.3 { | |
| 784 execsql "UPDATE t1 SET b = 'four' WHERE b = 'one'" | |
| 785 } {} | |
| 786 do_test fkey2-12.1.4 { | |
| 787 catchsql "UPDATE t1 SET b = 'five' WHERE b = 'two'" | |
| 788 } {1 {FOREIGN KEY constraint failed}} | |
| 789 do_test fkey2-12.1.5 { | |
| 790 execsql "DELETE FROM t1 WHERE b = 'two'" | |
| 791 } {} | |
| 792 do_test fkey2-12.1.6 { | |
| 793 catchsql "COMMIT" | |
| 794 } {1 {FOREIGN KEY constraint failed}} | |
| 795 do_test fkey2-12.1.7 { | |
| 796 execsql { | |
| 797 INSERT INTO t1 VALUES(2, 'two'); | |
| 798 COMMIT; | |
| 799 } | |
| 800 } {} | |
| 801 | |
| 802 drop_all_tables | |
| 803 do_test fkey2-12.2.1 { | |
| 804 execsql { | |
| 805 CREATE TABLE t1(x COLLATE NOCASE PRIMARY KEY); | |
| 806 CREATE TRIGGER tt1 AFTER DELETE ON t1 | |
| 807 WHEN EXISTS ( SELECT 1 FROM t2 WHERE old.x = y ) | |
| 808 BEGIN | |
| 809 INSERT INTO t1 VALUES(old.x); | |
| 810 END; | |
| 811 CREATE TABLE t2(y REFERENCES t1); | |
| 812 INSERT INTO t1 VALUES('A'); | |
| 813 INSERT INTO t1 VALUES('B'); | |
| 814 INSERT INTO t2 VALUES('a'); | |
| 815 INSERT INTO t2 VALUES('b'); | |
| 816 | |
| 817 SELECT * FROM t1; | |
| 818 SELECT * FROM t2; | |
| 819 } | |
| 820 } {A B a b} | |
| 821 do_test fkey2-12.2.2 { | |
| 822 execsql { DELETE FROM t1 } | |
| 823 execsql { | |
| 824 SELECT * FROM t1; | |
| 825 SELECT * FROM t2; | |
| 826 } | |
| 827 } {A B a b} | |
| 828 do_test fkey2-12.2.3 { | |
| 829 execsql { | |
| 830 DROP TABLE t2; | |
| 831 CREATE TABLE t2(y REFERENCES t1 ON DELETE RESTRICT); | |
| 832 INSERT INTO t2 VALUES('a'); | |
| 833 INSERT INTO t2 VALUES('b'); | |
| 834 } | |
| 835 catchsql { DELETE FROM t1 } | |
| 836 } {1 {FOREIGN KEY constraint failed}} | |
| 837 do_test fkey2-12.2.4 { | |
| 838 execsql { | |
| 839 SELECT * FROM t1; | |
| 840 SELECT * FROM t2; | |
| 841 } | |
| 842 } {A B a b} | |
| 843 | |
| 844 drop_all_tables | |
| 845 do_test fkey2-12.3.1 { | |
| 846 execsql { | |
| 847 CREATE TABLE up( | |
| 848 c00, c01, c02, c03, c04, c05, c06, c07, c08, c09, | |
| 849 c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, | |
| 850 c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, | |
| 851 c30, c31, c32, c33, c34, c35, c36, c37, c38, c39, | |
| 852 PRIMARY KEY(c34, c35) | |
| 853 ); | |
| 854 CREATE TABLE down( | |
| 855 c00, c01, c02, c03, c04, c05, c06, c07, c08, c09, | |
| 856 c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, | |
| 857 c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, | |
| 858 c30, c31, c32, c33, c34, c35, c36, c37, c38, c39, | |
| 859 FOREIGN KEY(c39, c38) REFERENCES up ON UPDATE CASCADE | |
| 860 ); | |
| 861 } | |
| 862 } {} | |
| 863 do_test fkey2-12.3.2 { | |
| 864 execsql { | |
| 865 INSERT INTO up(c34, c35) VALUES('yes', 'no'); | |
| 866 INSERT INTO down(c39, c38) VALUES('yes', 'no'); | |
| 867 UPDATE up SET c34 = 'possibly'; | |
| 868 SELECT c38, c39 FROM down; | |
| 869 DELETE FROM down; | |
| 870 } | |
| 871 } {no possibly} | |
| 872 do_test fkey2-12.3.3 { | |
| 873 catchsql { INSERT INTO down(c39, c38) VALUES('yes', 'no') } | |
| 874 } {1 {FOREIGN KEY constraint failed}} | |
| 875 do_test fkey2-12.3.4 { | |
| 876 execsql { | |
| 877 INSERT INTO up(c34, c35) VALUES('yes', 'no'); | |
| 878 INSERT INTO down(c39, c38) VALUES('yes', 'no'); | |
| 879 } | |
| 880 catchsql { DELETE FROM up WHERE c34 = 'yes' } | |
| 881 } {1 {FOREIGN KEY constraint failed}} | |
| 882 do_test fkey2-12.3.5 { | |
| 883 execsql { | |
| 884 DELETE FROM up WHERE c34 = 'possibly'; | |
| 885 SELECT c34, c35 FROM up; | |
| 886 SELECT c39, c38 FROM down; | |
| 887 } | |
| 888 } {yes no yes no} | |
| 889 | |
| 890 #------------------------------------------------------------------------- | |
| 891 # The following tests, fkey2-13.*, test that FK processing is performed | |
| 892 # when rows are REPLACEd. | |
| 893 # | |
| 894 drop_all_tables | |
| 895 do_test fkey2-13.1.1 { | |
| 896 execsql { | |
| 897 CREATE TABLE pp(a UNIQUE, b, c, PRIMARY KEY(b, c)); | |
| 898 CREATE TABLE cc(d, e, f UNIQUE, FOREIGN KEY(d, e) REFERENCES pp); | |
| 899 INSERT INTO pp VALUES(1, 2, 3); | |
| 900 INSERT INTO cc VALUES(2, 3, 1); | |
| 901 } | |
| 902 } {} | |
| 903 foreach {tn stmt} { | |
| 904 1 "REPLACE INTO pp VALUES(1, 4, 5)" | |
| 905 2 "REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 3, 4)" | |
| 906 } { | |
| 907 do_test fkey2-13.1.$tn.1 { | |
| 908 catchsql $stmt | |
| 909 } {1 {FOREIGN KEY constraint failed}} | |
| 910 do_test fkey2-13.1.$tn.2 { | |
| 911 execsql { | |
| 912 SELECT * FROM pp; | |
| 913 SELECT * FROM cc; | |
| 914 } | |
| 915 } {1 2 3 2 3 1} | |
| 916 do_test fkey2-13.1.$tn.3 { | |
| 917 execsql BEGIN; | |
| 918 catchsql $stmt | |
| 919 } {1 {FOREIGN KEY constraint failed}} | |
| 920 do_test fkey2-13.1.$tn.4 { | |
| 921 execsql { | |
| 922 COMMIT; | |
| 923 SELECT * FROM pp; | |
| 924 SELECT * FROM cc; | |
| 925 } | |
| 926 } {1 2 3 2 3 1} | |
| 927 } | |
| 928 do_test fkey2-13.1.3 { | |
| 929 execsql { | |
| 930 REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 2, 3); | |
| 931 SELECT rowid, * FROM pp; | |
| 932 SELECT * FROM cc; | |
| 933 } | |
| 934 } {1 2 2 3 2 3 1} | |
| 935 do_test fkey2-13.1.4 { | |
| 936 execsql { | |
| 937 REPLACE INTO pp(rowid, a, b, c) VALUES(2, 2, 2, 3); | |
| 938 SELECT rowid, * FROM pp; | |
| 939 SELECT * FROM cc; | |
| 940 } | |
| 941 } {2 2 2 3 2 3 1} | |
| 942 | |
| 943 #------------------------------------------------------------------------- | |
| 944 # The following tests, fkey2-14.*, test that the "DROP TABLE" and "ALTER | |
| 945 # TABLE" commands work as expected wrt foreign key constraints. | |
| 946 # | |
| 947 # fkey2-14.1*: ALTER TABLE ADD COLUMN | |
| 948 # fkey2-14.2*: ALTER TABLE RENAME TABLE | |
| 949 # fkey2-14.3*: DROP TABLE | |
| 950 # | |
| 951 drop_all_tables | |
| 952 ifcapable altertable { | |
| 953 do_test fkey2-14.1.1 { | |
| 954 # Adding a column with a REFERENCES clause is not supported. | |
| 955 execsql { | |
| 956 CREATE TABLE t1(a PRIMARY KEY); | |
| 957 CREATE TABLE t2(a, b); | |
| 958 } | |
| 959 catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 } | |
| 960 } {0 {}} | |
| 961 do_test fkey2-14.1.2 { | |
| 962 catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 } | |
| 963 } {0 {}} | |
| 964 do_test fkey2-14.1.3 { | |
| 965 catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL} | |
| 966 } {0 {}} | |
| 967 do_test fkey2-14.1.4 { | |
| 968 catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'} | |
| 969 } {1 {Cannot add a REFERENCES column with non-NULL default value}} | |
| 970 do_test fkey2-14.1.5 { | |
| 971 catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 } | |
| 972 } {1 {Cannot add a REFERENCES column with non-NULL default value}} | |
| 973 do_test fkey2-14.1.6 { | |
| 974 execsql { | |
| 975 PRAGMA foreign_keys = off; | |
| 976 ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1; | |
| 977 PRAGMA foreign_keys = on; | |
| 978 SELECT sql FROM sqlite_master WHERE name='t2'; | |
| 979 } | |
| 980 } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REF
ERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}} | |
| 981 | |
| 982 | |
| 983 # Test the sqlite_rename_parent() function directly. | |
| 984 # | |
| 985 proc test_rename_parent {zCreate zOld zNew} { | |
| 986 db eval {SELECT sqlite_rename_parent($zCreate, $zOld, $zNew)} | |
| 987 } | |
| 988 do_test fkey2-14.2.1.1 { | |
| 989 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3 | |
| 990 } {{CREATE TABLE t1(a REFERENCES "t3")}} | |
| 991 do_test fkey2-14.2.1.2 { | |
| 992 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3 | |
| 993 } {{CREATE TABLE t1(a REFERENCES t2)}} | |
| 994 do_test fkey2-14.2.1.3 { | |
| 995 test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3 | |
| 996 } {{CREATE TABLE t1(a REFERENCES "t3")}} | |
| 997 | |
| 998 # Test ALTER TABLE RENAME TABLE a bit. | |
| 999 # | |
| 1000 do_test fkey2-14.2.2.1 { | |
| 1001 drop_all_tables | |
| 1002 execsql { | |
| 1003 CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1); | |
| 1004 CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2); | |
| 1005 CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1); | |
| 1006 } | |
| 1007 execsql { SELECT sql FROM sqlite_master WHERE type = 'table'} | |
| 1008 } [list \ | |
| 1009 {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \ | |
| 1010 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \ | |
| 1011 {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \ | |
| 1012 ] | |
| 1013 do_test fkey2-14.2.2.2 { | |
| 1014 execsql { ALTER TABLE t1 RENAME TO t4 } | |
| 1015 execsql { SELECT sql FROM sqlite_master WHERE type = 'table'} | |
| 1016 } [list \ | |
| 1017 {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \ | |
| 1018 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \ | |
| 1019 {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \ | |
| 1020 ] | |
| 1021 do_test fkey2-14.2.2.3 { | |
| 1022 catchsql { INSERT INTO t3 VALUES(1, 2, 3) } | |
| 1023 } {1 {FOREIGN KEY constraint failed}} | |
| 1024 do_test fkey2-14.2.2.4 { | |
| 1025 execsql { INSERT INTO t4 VALUES(1, NULL) } | |
| 1026 } {} | |
| 1027 do_test fkey2-14.2.2.5 { | |
| 1028 catchsql { UPDATE t4 SET b = 5 } | |
| 1029 } {1 {FOREIGN KEY constraint failed}} | |
| 1030 do_test fkey2-14.2.2.6 { | |
| 1031 catchsql { UPDATE t4 SET b = 1 } | |
| 1032 } {0 {}} | |
| 1033 do_test fkey2-14.2.2.7 { | |
| 1034 execsql { INSERT INTO t3 VALUES(1, NULL, 1) } | |
| 1035 } {} | |
| 1036 | |
| 1037 # Repeat for TEMP tables | |
| 1038 # | |
| 1039 drop_all_tables | |
| 1040 do_test fkey2-14.1tmp.1 { | |
| 1041 # Adding a column with a REFERENCES clause is not supported. | |
| 1042 execsql { | |
| 1043 CREATE TEMP TABLE t1(a PRIMARY KEY); | |
| 1044 CREATE TEMP TABLE t2(a, b); | |
| 1045 } | |
| 1046 catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 } | |
| 1047 } {0 {}} | |
| 1048 do_test fkey2-14.1tmp.2 { | |
| 1049 catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 } | |
| 1050 } {0 {}} | |
| 1051 do_test fkey2-14.1tmp.3 { | |
| 1052 catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL} | |
| 1053 } {0 {}} | |
| 1054 do_test fkey2-14.1tmp.4 { | |
| 1055 catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'} | |
| 1056 } {1 {Cannot add a REFERENCES column with non-NULL default value}} | |
| 1057 do_test fkey2-14.1tmp.5 { | |
| 1058 catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 } | |
| 1059 } {1 {Cannot add a REFERENCES column with non-NULL default value}} | |
| 1060 do_test fkey2-14.1tmp.6 { | |
| 1061 execsql { | |
| 1062 PRAGMA foreign_keys = off; | |
| 1063 ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1; | |
| 1064 PRAGMA foreign_keys = on; | |
| 1065 SELECT sql FROM sqlite_temp_master WHERE name='t2'; | |
| 1066 } | |
| 1067 } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REF
ERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}} | |
| 1068 | |
| 1069 do_test fkey2-14.2tmp.1.1 { | |
| 1070 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3 | |
| 1071 } {{CREATE TABLE t1(a REFERENCES "t3")}} | |
| 1072 do_test fkey2-14.2tmp.1.2 { | |
| 1073 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3 | |
| 1074 } {{CREATE TABLE t1(a REFERENCES t2)}} | |
| 1075 do_test fkey2-14.2tmp.1.3 { | |
| 1076 test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3 | |
| 1077 } {{CREATE TABLE t1(a REFERENCES "t3")}} | |
| 1078 | |
| 1079 # Test ALTER TABLE RENAME TABLE a bit. | |
| 1080 # | |
| 1081 do_test fkey2-14.2tmp.2.1 { | |
| 1082 drop_all_tables | |
| 1083 execsql { | |
| 1084 CREATE TEMP TABLE t1(a PRIMARY KEY, b REFERENCES t1); | |
| 1085 CREATE TEMP TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2); | |
| 1086 CREATE TEMP TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1); | |
| 1087 } | |
| 1088 execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'} | |
| 1089 } [list \ | |
| 1090 {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \ | |
| 1091 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \ | |
| 1092 {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \ | |
| 1093 ] | |
| 1094 do_test fkey2-14.2tmp.2.2 { | |
| 1095 execsql { ALTER TABLE t1 RENAME TO t4 } | |
| 1096 execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'} | |
| 1097 } [list \ | |
| 1098 {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \ | |
| 1099 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \ | |
| 1100 {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \ | |
| 1101 ] | |
| 1102 do_test fkey2-14.2tmp.2.3 { | |
| 1103 catchsql { INSERT INTO t3 VALUES(1, 2, 3) } | |
| 1104 } {1 {FOREIGN KEY constraint failed}} | |
| 1105 do_test fkey2-14.2tmp.2.4 { | |
| 1106 execsql { INSERT INTO t4 VALUES(1, NULL) } | |
| 1107 } {} | |
| 1108 do_test fkey2-14.2tmp.2.5 { | |
| 1109 catchsql { UPDATE t4 SET b = 5 } | |
| 1110 } {1 {FOREIGN KEY constraint failed}} | |
| 1111 do_test fkey2-14.2tmp.2.6 { | |
| 1112 catchsql { UPDATE t4 SET b = 1 } | |
| 1113 } {0 {}} | |
| 1114 do_test fkey2-14.2tmp.2.7 { | |
| 1115 execsql { INSERT INTO t3 VALUES(1, NULL, 1) } | |
| 1116 } {} | |
| 1117 | |
| 1118 # Repeat for ATTACH-ed tables | |
| 1119 # | |
| 1120 drop_all_tables | |
| 1121 do_test fkey2-14.1aux.1 { | |
| 1122 # Adding a column with a REFERENCES clause is not supported. | |
| 1123 execsql { | |
| 1124 ATTACH ':memory:' AS aux; | |
| 1125 CREATE TABLE aux.t1(a PRIMARY KEY); | |
| 1126 CREATE TABLE aux.t2(a, b); | |
| 1127 } | |
| 1128 catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 } | |
| 1129 } {0 {}} | |
| 1130 do_test fkey2-14.1aux.2 { | |
| 1131 catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 } | |
| 1132 } {0 {}} | |
| 1133 do_test fkey2-14.1aux.3 { | |
| 1134 catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL} | |
| 1135 } {0 {}} | |
| 1136 do_test fkey2-14.1aux.4 { | |
| 1137 catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'} | |
| 1138 } {1 {Cannot add a REFERENCES column with non-NULL default value}} | |
| 1139 do_test fkey2-14.1aux.5 { | |
| 1140 catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 } | |
| 1141 } {1 {Cannot add a REFERENCES column with non-NULL default value}} | |
| 1142 do_test fkey2-14.1aux.6 { | |
| 1143 execsql { | |
| 1144 PRAGMA foreign_keys = off; | |
| 1145 ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1; | |
| 1146 PRAGMA foreign_keys = on; | |
| 1147 SELECT sql FROM aux.sqlite_master WHERE name='t2'; | |
| 1148 } | |
| 1149 } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REF
ERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}} | |
| 1150 | |
| 1151 do_test fkey2-14.2aux.1.1 { | |
| 1152 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3 | |
| 1153 } {{CREATE TABLE t1(a REFERENCES "t3")}} | |
| 1154 do_test fkey2-14.2aux.1.2 { | |
| 1155 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3 | |
| 1156 } {{CREATE TABLE t1(a REFERENCES t2)}} | |
| 1157 do_test fkey2-14.2aux.1.3 { | |
| 1158 test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3 | |
| 1159 } {{CREATE TABLE t1(a REFERENCES "t3")}} | |
| 1160 | |
| 1161 # Test ALTER TABLE RENAME TABLE a bit. | |
| 1162 # | |
| 1163 do_test fkey2-14.2aux.2.1 { | |
| 1164 drop_all_tables | |
| 1165 execsql { | |
| 1166 CREATE TABLE aux.t1(a PRIMARY KEY, b REFERENCES t1); | |
| 1167 CREATE TABLE aux.t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2); | |
| 1168 CREATE TABLE aux.t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1); | |
| 1169 } | |
| 1170 execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'} | |
| 1171 } [list \ | |
| 1172 {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \ | |
| 1173 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \ | |
| 1174 {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \ | |
| 1175 ] | |
| 1176 do_test fkey2-14.2aux.2.2 { | |
| 1177 execsql { ALTER TABLE t1 RENAME TO t4 } | |
| 1178 execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'} | |
| 1179 } [list \ | |
| 1180 {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \ | |
| 1181 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \ | |
| 1182 {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \ | |
| 1183 ] | |
| 1184 do_test fkey2-14.2aux.2.3 { | |
| 1185 catchsql { INSERT INTO t3 VALUES(1, 2, 3) } | |
| 1186 } {1 {FOREIGN KEY constraint failed}} | |
| 1187 do_test fkey2-14.2aux.2.4 { | |
| 1188 execsql { INSERT INTO t4 VALUES(1, NULL) } | |
| 1189 } {} | |
| 1190 do_test fkey2-14.2aux.2.5 { | |
| 1191 catchsql { UPDATE t4 SET b = 5 } | |
| 1192 } {1 {FOREIGN KEY constraint failed}} | |
| 1193 do_test fkey2-14.2aux.2.6 { | |
| 1194 catchsql { UPDATE t4 SET b = 1 } | |
| 1195 } {0 {}} | |
| 1196 do_test fkey2-14.2aux.2.7 { | |
| 1197 execsql { INSERT INTO t3 VALUES(1, NULL, 1) } | |
| 1198 } {} | |
| 1199 } | |
| 1200 | |
| 1201 do_test fkey-2.14.3.1 { | |
| 1202 drop_all_tables | |
| 1203 execsql { | |
| 1204 CREATE TABLE t1(a, b REFERENCES nosuchtable); | |
| 1205 DROP TABLE t1; | |
| 1206 } | |
| 1207 } {} | |
| 1208 do_test fkey-2.14.3.2 { | |
| 1209 execsql { | |
| 1210 CREATE TABLE t1(a PRIMARY KEY, b); | |
| 1211 INSERT INTO t1 VALUES('a', 1); | |
| 1212 CREATE TABLE t2(x REFERENCES t1); | |
| 1213 INSERT INTO t2 VALUES('a'); | |
| 1214 } | |
| 1215 } {} | |
| 1216 do_test fkey-2.14.3.3 { | |
| 1217 catchsql { DROP TABLE t1 } | |
| 1218 } {1 {FOREIGN KEY constraint failed}} | |
| 1219 do_test fkey-2.14.3.4 { | |
| 1220 execsql { | |
| 1221 DELETE FROM t2; | |
| 1222 DROP TABLE t1; | |
| 1223 } | |
| 1224 } {} | |
| 1225 do_test fkey-2.14.3.4 { | |
| 1226 catchsql { INSERT INTO t2 VALUES('x') } | |
| 1227 } {1 {no such table: main.t1}} | |
| 1228 do_test fkey-2.14.3.5 { | |
| 1229 execsql { | |
| 1230 CREATE TABLE t1(x PRIMARY KEY); | |
| 1231 INSERT INTO t1 VALUES('x'); | |
| 1232 } | |
| 1233 execsql { INSERT INTO t2 VALUES('x') } | |
| 1234 } {} | |
| 1235 do_test fkey-2.14.3.6 { | |
| 1236 catchsql { DROP TABLE t1 } | |
| 1237 } {1 {FOREIGN KEY constraint failed}} | |
| 1238 do_test fkey-2.14.3.7 { | |
| 1239 execsql { | |
| 1240 DROP TABLE t2; | |
| 1241 DROP TABLE t1; | |
| 1242 } | |
| 1243 } {} | |
| 1244 do_test fkey-2.14.3.8 { | |
| 1245 execsql { | |
| 1246 CREATE TABLE pp(x, y, PRIMARY KEY(x, y)); | |
| 1247 CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z)); | |
| 1248 } | |
| 1249 catchsql { INSERT INTO cc VALUES(1, 2) } | |
| 1250 } {1 {foreign key mismatch - "cc" referencing "pp"}} | |
| 1251 do_test fkey-2.14.3.9 { | |
| 1252 execsql { DROP TABLE cc } | |
| 1253 } {} | |
| 1254 do_test fkey-2.14.3.10 { | |
| 1255 execsql { | |
| 1256 CREATE TABLE cc(a, b, | |
| 1257 FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED | |
| 1258 ); | |
| 1259 } | |
| 1260 execsql { | |
| 1261 INSERT INTO pp VALUES('a', 'b'); | |
| 1262 INSERT INTO cc VALUES('a', 'b'); | |
| 1263 BEGIN; | |
| 1264 DROP TABLE pp; | |
| 1265 CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c)); | |
| 1266 INSERT INTO pp VALUES(1, 'a', 'b'); | |
| 1267 COMMIT; | |
| 1268 } | |
| 1269 } {} | |
| 1270 do_test fkey-2.14.3.11 { | |
| 1271 execsql { | |
| 1272 BEGIN; | |
| 1273 DROP TABLE cc; | |
| 1274 DROP TABLE pp; | |
| 1275 COMMIT; | |
| 1276 } | |
| 1277 } {} | |
| 1278 do_test fkey-2.14.3.12 { | |
| 1279 execsql { | |
| 1280 CREATE TABLE b1(a, b); | |
| 1281 CREATE TABLE b2(a, b REFERENCES b1); | |
| 1282 DROP TABLE b1; | |
| 1283 } | |
| 1284 } {} | |
| 1285 do_test fkey-2.14.3.13 { | |
| 1286 execsql { | |
| 1287 CREATE TABLE b3(a, b REFERENCES b2 DEFERRABLE INITIALLY DEFERRED); | |
| 1288 DROP TABLE b2; | |
| 1289 } | |
| 1290 } {} | |
| 1291 | |
| 1292 # Test that nothing goes wrong when dropping a table that refers to a view. | |
| 1293 # Or dropping a view that an existing FK (incorrectly) refers to. Or either | |
| 1294 # of the above scenarios with a virtual table. | |
| 1295 drop_all_tables | |
| 1296 do_test fkey-2.14.4.1 { | |
| 1297 execsql { | |
| 1298 CREATE TABLE t1(x REFERENCES v); | |
| 1299 CREATE VIEW v AS SELECT * FROM t1; | |
| 1300 } | |
| 1301 } {} | |
| 1302 do_test fkey-2.14.4.2 { | |
| 1303 execsql { | |
| 1304 DROP VIEW v; | |
| 1305 } | |
| 1306 } {} | |
| 1307 ifcapable vtab { | |
| 1308 register_echo_module db | |
| 1309 do_test fkey-2.14.4.3 { | |
| 1310 execsql { CREATE VIRTUAL TABLE v USING echo(t1) } | |
| 1311 } {} | |
| 1312 do_test fkey-2.14.4.2 { | |
| 1313 execsql { | |
| 1314 DROP TABLE v; | |
| 1315 } | |
| 1316 } {} | |
| 1317 } | |
| 1318 | |
| 1319 #------------------------------------------------------------------------- | |
| 1320 # The following tests, fkey2-15.*, test that unnecessary FK related scans | |
| 1321 # and lookups are avoided when the constraint counters are zero. | |
| 1322 # | |
| 1323 drop_all_tables | |
| 1324 proc execsqlS {zSql} { | |
| 1325 set ::sqlite_search_count 0 | |
| 1326 set ::sqlite_found_count 0 | |
| 1327 set res [uplevel [list execsql $zSql]] | |
| 1328 concat [expr $::sqlite_found_count + $::sqlite_search_count] $res | |
| 1329 } | |
| 1330 do_test fkey2-15.1.1 { | |
| 1331 execsql { | |
| 1332 CREATE TABLE pp(a PRIMARY KEY, b); | |
| 1333 CREATE TABLE cc(x, y REFERENCES pp DEFERRABLE INITIALLY DEFERRED); | |
| 1334 INSERT INTO pp VALUES(1, 'one'); | |
| 1335 INSERT INTO pp VALUES(2, 'two'); | |
| 1336 INSERT INTO cc VALUES('neung', 1); | |
| 1337 INSERT INTO cc VALUES('song', 2); | |
| 1338 } | |
| 1339 } {} | |
| 1340 do_test fkey2-15.1.2 { | |
| 1341 execsqlS { INSERT INTO pp VALUES(3, 'three') } | |
| 1342 } {0} | |
| 1343 do_test fkey2-15.1.3 { | |
| 1344 execsql { | |
| 1345 BEGIN; | |
| 1346 INSERT INTO cc VALUES('see', 4); -- Violates deferred constraint | |
| 1347 } | |
| 1348 execsqlS { INSERT INTO pp VALUES(5, 'five') } | |
| 1349 } {2} | |
| 1350 do_test fkey2-15.1.4 { | |
| 1351 execsql { DELETE FROM cc WHERE x = 'see' } | |
| 1352 execsqlS { INSERT INTO pp VALUES(6, 'six') } | |
| 1353 } {0} | |
| 1354 do_test fkey2-15.1.5 { | |
| 1355 execsql COMMIT | |
| 1356 } {} | |
| 1357 do_test fkey2-15.1.6 { | |
| 1358 execsql BEGIN | |
| 1359 execsqlS { | |
| 1360 DELETE FROM cc WHERE x = 'neung'; | |
| 1361 ROLLBACK; | |
| 1362 } | |
| 1363 } {1} | |
| 1364 do_test fkey2-15.1.7 { | |
| 1365 execsql { | |
| 1366 BEGIN; | |
| 1367 DELETE FROM pp WHERE a = 2; | |
| 1368 } | |
| 1369 execsqlS { | |
| 1370 DELETE FROM cc WHERE x = 'neung'; | |
| 1371 ROLLBACK; | |
| 1372 } | |
| 1373 } {2} | |
| 1374 | |
| 1375 #------------------------------------------------------------------------- | |
| 1376 # This next block of tests, fkey2-16.*, test that rows that refer to | |
| 1377 # themselves may be inserted and deleted. | |
| 1378 # | |
| 1379 foreach {tn zSchema} { | |
| 1380 1 { CREATE TABLE self(a INTEGER PRIMARY KEY, b REFERENCES self(a)) } | |
| 1381 2 { CREATE TABLE self(a PRIMARY KEY, b REFERENCES self(a)) } | |
| 1382 3 { CREATE TABLE self(a UNIQUE, b INTEGER PRIMARY KEY REFERENCES self(a)) } | |
| 1383 } { | |
| 1384 drop_all_tables | |
| 1385 do_test fkey2-16.1.$tn.1 { | |
| 1386 execsql $zSchema | |
| 1387 execsql { INSERT INTO self VALUES(13, 13) } | |
| 1388 } {} | |
| 1389 do_test fkey2-16.1.$tn.2 { | |
| 1390 execsql { UPDATE self SET a = 14, b = 14 } | |
| 1391 } {} | |
| 1392 | |
| 1393 do_test fkey2-16.1.$tn.3 { | |
| 1394 catchsql { UPDATE self SET b = 15 } | |
| 1395 } {1 {FOREIGN KEY constraint failed}} | |
| 1396 | |
| 1397 do_test fkey2-16.1.$tn.4 { | |
| 1398 catchsql { UPDATE self SET a = 15 } | |
| 1399 } {1 {FOREIGN KEY constraint failed}} | |
| 1400 | |
| 1401 do_test fkey2-16.1.$tn.5 { | |
| 1402 catchsql { UPDATE self SET a = 15, b = 16 } | |
| 1403 } {1 {FOREIGN KEY constraint failed}} | |
| 1404 | |
| 1405 do_test fkey2-16.1.$tn.6 { | |
| 1406 catchsql { UPDATE self SET a = 17, b = 17 } | |
| 1407 } {0 {}} | |
| 1408 | |
| 1409 do_test fkey2-16.1.$tn.7 { | |
| 1410 execsql { DELETE FROM self } | |
| 1411 } {} | |
| 1412 do_test fkey2-16.1.$tn.8 { | |
| 1413 catchsql { INSERT INTO self VALUES(20, 21) } | |
| 1414 } {1 {FOREIGN KEY constraint failed}} | |
| 1415 } | |
| 1416 | |
| 1417 #------------------------------------------------------------------------- | |
| 1418 # This next block of tests, fkey2-17.*, tests that if "PRAGMA count_changes" | |
| 1419 # is turned on statements that violate immediate FK constraints return | |
| 1420 # SQLITE_CONSTRAINT immediately, not after returning a number of rows. | |
| 1421 # Whereas statements that violate deferred FK constraints return the number | |
| 1422 # of rows before failing. | |
| 1423 # | |
| 1424 # Also test that rows modified by FK actions are not counted in either the | |
| 1425 # returned row count or the values returned by sqlite3_changes(). Like | |
| 1426 # trigger related changes, they are included in sqlite3_total_changes() though. | |
| 1427 # | |
| 1428 drop_all_tables | |
| 1429 do_test fkey2-17.1.1 { | |
| 1430 execsql { PRAGMA count_changes = 1 } | |
| 1431 execsql { | |
| 1432 CREATE TABLE one(a, b, c, UNIQUE(b, c)); | |
| 1433 CREATE TABLE two(d, e, f, FOREIGN KEY(e, f) REFERENCES one(b, c)); | |
| 1434 INSERT INTO one VALUES(1, 2, 3); | |
| 1435 } | |
| 1436 } {1} | |
| 1437 do_test fkey2-17.1.2 { | |
| 1438 set STMT [sqlite3_prepare_v2 db "INSERT INTO two VALUES(4, 5, 6)" -1 dummy] | |
| 1439 sqlite3_step $STMT | |
| 1440 } {SQLITE_CONSTRAINT} | |
| 1441 verify_ex_errcode fkey2-17.1.2b SQLITE_CONSTRAINT_FOREIGNKEY | |
| 1442 ifcapable autoreset { | |
| 1443 do_test fkey2-17.1.3 { | |
| 1444 sqlite3_step $STMT | |
| 1445 } {SQLITE_CONSTRAINT} | |
| 1446 verify_ex_errcode fkey2-17.1.3b SQLITE_CONSTRAINT_FOREIGNKEY | |
| 1447 } else { | |
| 1448 do_test fkey2-17.1.3 { | |
| 1449 sqlite3_step $STMT | |
| 1450 } {SQLITE_MISUSE} | |
| 1451 } | |
| 1452 do_test fkey2-17.1.4 { | |
| 1453 sqlite3_finalize $STMT | |
| 1454 } {SQLITE_CONSTRAINT} | |
| 1455 verify_ex_errcode fkey2-17.1.4b SQLITE_CONSTRAINT_FOREIGNKEY | |
| 1456 do_test fkey2-17.1.5 { | |
| 1457 execsql { | |
| 1458 INSERT INTO one VALUES(2, 3, 4); | |
| 1459 INSERT INTO one VALUES(3, 4, 5); | |
| 1460 INSERT INTO two VALUES(1, 2, 3); | |
| 1461 INSERT INTO two VALUES(2, 3, 4); | |
| 1462 INSERT INTO two VALUES(3, 4, 5); | |
| 1463 } | |
| 1464 } {1 1 1 1 1} | |
| 1465 do_test fkey2-17.1.6 { | |
| 1466 catchsql { | |
| 1467 BEGIN; | |
| 1468 INSERT INTO one VALUES(0, 0, 0); | |
| 1469 UPDATE two SET e=e+1, f=f+1; | |
| 1470 } | |
| 1471 } {1 {FOREIGN KEY constraint failed}} | |
| 1472 do_test fkey2-17.1.7 { | |
| 1473 execsql { SELECT * FROM one } | |
| 1474 } {1 2 3 2 3 4 3 4 5 0 0 0} | |
| 1475 do_test fkey2-17.1.8 { | |
| 1476 execsql { SELECT * FROM two } | |
| 1477 } {1 2 3 2 3 4 3 4 5} | |
| 1478 do_test fkey2-17.1.9 { | |
| 1479 execsql COMMIT | |
| 1480 } {} | |
| 1481 do_test fkey2-17.1.10 { | |
| 1482 execsql { | |
| 1483 CREATE TABLE three( | |
| 1484 g, h, i, | |
| 1485 FOREIGN KEY(h, i) REFERENCES one(b, c) DEFERRABLE INITIALLY DEFERRED | |
| 1486 ); | |
| 1487 } | |
| 1488 } {} | |
| 1489 do_test fkey2-17.1.11 { | |
| 1490 set STMT [sqlite3_prepare_v2 db "INSERT INTO three VALUES(7, 8, 9)" -1 dummy] | |
| 1491 sqlite3_step $STMT | |
| 1492 } {SQLITE_ROW} | |
| 1493 do_test fkey2-17.1.12 { | |
| 1494 sqlite3_column_text $STMT 0 | |
| 1495 } {1} | |
| 1496 do_test fkey2-17.1.13 { | |
| 1497 sqlite3_step $STMT | |
| 1498 } {SQLITE_CONSTRAINT} | |
| 1499 verify_ex_errcode fkey2-17.1.13b SQLITE_CONSTRAINT_FOREIGNKEY | |
| 1500 do_test fkey2-17.1.14 { | |
| 1501 sqlite3_finalize $STMT | |
| 1502 } {SQLITE_CONSTRAINT} | |
| 1503 verify_ex_errcode fkey2-17.1.14b SQLITE_CONSTRAINT_FOREIGNKEY | |
| 1504 | |
| 1505 drop_all_tables | |
| 1506 do_test fkey2-17.2.1 { | |
| 1507 execsql { | |
| 1508 CREATE TABLE high("a'b!" PRIMARY KEY, b); | |
| 1509 CREATE TABLE low( | |
| 1510 c, | |
| 1511 "d&6" REFERENCES high ON UPDATE CASCADE ON DELETE CASCADE | |
| 1512 ); | |
| 1513 } | |
| 1514 } {} | |
| 1515 do_test fkey2-17.2.2 { | |
| 1516 execsql { | |
| 1517 INSERT INTO high VALUES('a', 'b'); | |
| 1518 INSERT INTO low VALUES('b', 'a'); | |
| 1519 } | |
| 1520 db changes | |
| 1521 } {1} | |
| 1522 set nTotal [db total_changes] | |
| 1523 do_test fkey2-17.2.3 { | |
| 1524 execsql { UPDATE high SET "a'b!" = 'c' } | |
| 1525 } {1} | |
| 1526 do_test fkey2-17.2.4 { | |
| 1527 db changes | |
| 1528 } {1} | |
| 1529 do_test fkey2-17.2.5 { | |
| 1530 expr [db total_changes] - $nTotal | |
| 1531 } {2} | |
| 1532 do_test fkey2-17.2.6 { | |
| 1533 execsql { SELECT * FROM high ; SELECT * FROM low } | |
| 1534 } {c b b c} | |
| 1535 do_test fkey2-17.2.7 { | |
| 1536 execsql { DELETE FROM high } | |
| 1537 } {1} | |
| 1538 do_test fkey2-17.2.8 { | |
| 1539 db changes | |
| 1540 } {1} | |
| 1541 do_test fkey2-17.2.9 { | |
| 1542 expr [db total_changes] - $nTotal | |
| 1543 } {4} | |
| 1544 do_test fkey2-17.2.10 { | |
| 1545 execsql { SELECT * FROM high ; SELECT * FROM low } | |
| 1546 } {} | |
| 1547 execsql { PRAGMA count_changes = 0 } | |
| 1548 | |
| 1549 #------------------------------------------------------------------------- | |
| 1550 # Test that the authorization callback works. | |
| 1551 # | |
| 1552 | |
| 1553 ifcapable auth { | |
| 1554 do_test fkey2-18.1 { | |
| 1555 execsql { | |
| 1556 CREATE TABLE long(a, b PRIMARY KEY, c); | |
| 1557 CREATE TABLE short(d, e, f REFERENCES long); | |
| 1558 CREATE TABLE mid(g, h, i REFERENCES long DEFERRABLE INITIALLY DEFERRED); | |
| 1559 } | |
| 1560 } {} | |
| 1561 | |
| 1562 proc auth {args} {eval lappend ::authargs [lrange $args 0 4]; return SQLITE_OK
} | |
| 1563 db auth auth | |
| 1564 | |
| 1565 # An insert on the parent table must read the child key of any deferred | |
| 1566 # foreign key constraints. But not the child key of immediate constraints. | |
| 1567 set authargs {} | |
| 1568 do_test fkey2-18.2 { | |
| 1569 execsql { INSERT INTO long VALUES(1, 2, 3) } | |
| 1570 set authargs | |
| 1571 } {SQLITE_INSERT long {} main {} SQLITE_READ mid i main {}} | |
| 1572 | |
| 1573 # An insert on the child table of an immediate constraint must read the | |
| 1574 # parent key columns (to see if it is a violation or not). | |
| 1575 set authargs {} | |
| 1576 do_test fkey2-18.3 { | |
| 1577 execsql { INSERT INTO short VALUES(1, 3, 2) } | |
| 1578 set authargs | |
| 1579 } {SQLITE_INSERT short {} main {} SQLITE_READ long b main {}} | |
| 1580 | |
| 1581 # As must an insert on the child table of a deferred constraint. | |
| 1582 set authargs {} | |
| 1583 do_test fkey2-18.4 { | |
| 1584 execsql { INSERT INTO mid VALUES(1, 3, 2) } | |
| 1585 set authargs | |
| 1586 } {SQLITE_INSERT mid {} main {} SQLITE_READ long b main {}} | |
| 1587 | |
| 1588 do_test fkey2-18.5 { | |
| 1589 execsql { | |
| 1590 CREATE TABLE nought(a, b PRIMARY KEY, c); | |
| 1591 CREATE TABLE cross(d, e, f, | |
| 1592 FOREIGN KEY(e) REFERENCES nought(b) ON UPDATE CASCADE | |
| 1593 ); | |
| 1594 } | |
| 1595 execsql { INSERT INTO nought VALUES(2, 1, 2) } | |
| 1596 execsql { INSERT INTO cross VALUES(0, 1, 0) } | |
| 1597 set authargs [list] | |
| 1598 execsql { UPDATE nought SET b = 5 } | |
| 1599 set authargs | |
| 1600 } {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
{}} | |
| 1601 | |
| 1602 do_test fkey2-18.6 { | |
| 1603 execsql {SELECT * FROM cross} | |
| 1604 } {0 5 0} | |
| 1605 | |
| 1606 do_test fkey2-18.7 { | |
| 1607 execsql { | |
| 1608 CREATE TABLE one(a INTEGER PRIMARY KEY, b); | |
| 1609 CREATE TABLE two(b, c REFERENCES one); | |
| 1610 INSERT INTO one VALUES(101, 102); | |
| 1611 } | |
| 1612 set authargs [list] | |
| 1613 execsql { INSERT INTO two VALUES(100, 101); } | |
| 1614 set authargs | |
| 1615 } {SQLITE_INSERT two {} main {} SQLITE_READ one a main {}} | |
| 1616 | |
| 1617 # Return SQLITE_IGNORE to requests to read from the parent table. This | |
| 1618 # causes inserts of non-NULL keys into the child table to fail. | |
| 1619 # | |
| 1620 rename auth {} | |
| 1621 proc auth {args} { | |
| 1622 if {[lindex $args 1] == "long"} {return SQLITE_IGNORE} | |
| 1623 return SQLITE_OK | |
| 1624 } | |
| 1625 do_test fkey2-18.8 { | |
| 1626 catchsql { INSERT INTO short VALUES(1, 3, 2) } | |
| 1627 } {1 {FOREIGN KEY constraint failed}} | |
| 1628 do_test fkey2-18.9 { | |
| 1629 execsql { INSERT INTO short VALUES(1, 3, NULL) } | |
| 1630 } {} | |
| 1631 do_test fkey2-18.10 { | |
| 1632 execsql { SELECT * FROM short } | |
| 1633 } {1 3 2 1 3 {}} | |
| 1634 do_test fkey2-18.11 { | |
| 1635 catchsql { UPDATE short SET f = 2 WHERE f IS NULL } | |
| 1636 } {1 {FOREIGN KEY constraint failed}} | |
| 1637 | |
| 1638 db auth {} | |
| 1639 unset authargs | |
| 1640 } | |
| 1641 | |
| 1642 | |
| 1643 do_test fkey2-19.1 { | |
| 1644 execsql { | |
| 1645 CREATE TABLE main(id INTEGER PRIMARY KEY); | |
| 1646 CREATE TABLE sub(id INT REFERENCES main(id)); | |
| 1647 INSERT INTO main VALUES(1); | |
| 1648 INSERT INTO main VALUES(2); | |
| 1649 INSERT INTO sub VALUES(2); | |
| 1650 } | |
| 1651 } {} | |
| 1652 do_test fkey2-19.2 { | |
| 1653 set S [sqlite3_prepare_v2 db "DELETE FROM main WHERE id = ?" -1 dummy] | |
| 1654 sqlite3_bind_int $S 1 2 | |
| 1655 sqlite3_step $S | |
| 1656 } {SQLITE_CONSTRAINT} | |
| 1657 verify_ex_errcode fkey2-19.2b SQLITE_CONSTRAINT_FOREIGNKEY | |
| 1658 do_test fkey2-19.3 { | |
| 1659 sqlite3_reset $S | |
| 1660 } {SQLITE_CONSTRAINT} | |
| 1661 verify_ex_errcode fkey2-19.3b SQLITE_CONSTRAINT_FOREIGNKEY | |
| 1662 do_test fkey2-19.4 { | |
| 1663 sqlite3_bind_int $S 1 1 | |
| 1664 sqlite3_step $S | |
| 1665 } {SQLITE_DONE} | |
| 1666 do_test fkey2-19.4 { | |
| 1667 sqlite3_finalize $S | |
| 1668 } {SQLITE_OK} | |
| 1669 | |
| 1670 drop_all_tables | |
| 1671 do_test fkey2-20.1 { | |
| 1672 execsql { | |
| 1673 CREATE TABLE pp(a PRIMARY KEY, b); | |
| 1674 CREATE TABLE cc(c PRIMARY KEY, d REFERENCES pp); | |
| 1675 } | |
| 1676 } {} | |
| 1677 | |
| 1678 foreach {tn insert} { | |
| 1679 1 "INSERT" | |
| 1680 2 "INSERT OR IGNORE" | |
| 1681 3 "INSERT OR ABORT" | |
| 1682 4 "INSERT OR ROLLBACK" | |
| 1683 5 "INSERT OR REPLACE" | |
| 1684 6 "INSERT OR FAIL" | |
| 1685 } { | |
| 1686 do_test fkey2-20.2.$tn.1 { | |
| 1687 catchsql "$insert INTO cc VALUES(1, 2)" | |
| 1688 } {1 {FOREIGN KEY constraint failed}} | |
| 1689 do_test fkey2-20.2.$tn.2 { | |
| 1690 execsql { SELECT * FROM cc } | |
| 1691 } {} | |
| 1692 do_test fkey2-20.2.$tn.3 { | |
| 1693 execsql { | |
| 1694 BEGIN; | |
| 1695 INSERT INTO pp VALUES(2, 'two'); | |
| 1696 INSERT INTO cc VALUES(1, 2); | |
| 1697 } | |
| 1698 catchsql "$insert INTO cc VALUES(3, 4)" | |
| 1699 } {1 {FOREIGN KEY constraint failed}} | |
| 1700 do_test fkey2-20.2.$tn.4 { | |
| 1701 execsql { COMMIT ; SELECT * FROM cc } | |
| 1702 } {1 2} | |
| 1703 do_test fkey2-20.2.$tn.5 { | |
| 1704 execsql { DELETE FROM cc ; DELETE FROM pp } | |
| 1705 } {} | |
| 1706 } | |
| 1707 | |
| 1708 foreach {tn update} { | |
| 1709 1 "UPDATE" | |
| 1710 2 "UPDATE OR IGNORE" | |
| 1711 3 "UPDATE OR ABORT" | |
| 1712 4 "UPDATE OR ROLLBACK" | |
| 1713 5 "UPDATE OR REPLACE" | |
| 1714 6 "UPDATE OR FAIL" | |
| 1715 } { | |
| 1716 do_test fkey2-20.3.$tn.1 { | |
| 1717 execsql { | |
| 1718 INSERT INTO pp VALUES(2, 'two'); | |
| 1719 INSERT INTO cc VALUES(1, 2); | |
| 1720 } | |
| 1721 } {} | |
| 1722 do_test fkey2-20.3.$tn.2 { | |
| 1723 catchsql "$update pp SET a = 1" | |
| 1724 } {1 {FOREIGN KEY constraint failed}} | |
| 1725 do_test fkey2-20.3.$tn.3 { | |
| 1726 execsql { SELECT * FROM pp } | |
| 1727 } {2 two} | |
| 1728 do_test fkey2-20.3.$tn.4 { | |
| 1729 catchsql "$update cc SET d = 1" | |
| 1730 } {1 {FOREIGN KEY constraint failed}} | |
| 1731 do_test fkey2-20.3.$tn.5 { | |
| 1732 execsql { SELECT * FROM cc } | |
| 1733 } {1 2} | |
| 1734 do_test fkey2-20.3.$tn.6 { | |
| 1735 execsql { | |
| 1736 BEGIN; | |
| 1737 INSERT INTO pp VALUES(3, 'three'); | |
| 1738 } | |
| 1739 catchsql "$update pp SET a = 1 WHERE a = 2" | |
| 1740 } {1 {FOREIGN KEY constraint failed}} | |
| 1741 do_test fkey2-20.3.$tn.7 { | |
| 1742 execsql { COMMIT ; SELECT * FROM pp } | |
| 1743 } {2 two 3 three} | |
| 1744 do_test fkey2-20.3.$tn.8 { | |
| 1745 execsql { | |
| 1746 BEGIN; | |
| 1747 INSERT INTO cc VALUES(2, 2); | |
| 1748 } | |
| 1749 catchsql "$update cc SET d = 1 WHERE c = 1" | |
| 1750 } {1 {FOREIGN KEY constraint failed}} | |
| 1751 do_test fkey2-20.3.$tn.9 { | |
| 1752 execsql { COMMIT ; SELECT * FROM cc } | |
| 1753 } {1 2 2 2} | |
| 1754 do_test fkey2-20.3.$tn.10 { | |
| 1755 execsql { DELETE FROM cc ; DELETE FROM pp } | |
| 1756 } {} | |
| 1757 } | |
| 1758 | |
| 1759 #------------------------------------------------------------------------- | |
| 1760 # The following block of tests, those prefixed with "fkey2-genfkey.", are | |
| 1761 # the same tests that were used to test the ".genfkey" command provided | |
| 1762 # by the shell tool. So these tests show that the built-in foreign key | |
| 1763 # implementation is more or less compatible with the triggers generated | |
| 1764 # by genfkey. | |
| 1765 # | |
| 1766 drop_all_tables | |
| 1767 do_test fkey2-genfkey.1.1 { | |
| 1768 execsql { | |
| 1769 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); | |
| 1770 CREATE TABLE t2(e REFERENCES t1, f); | |
| 1771 CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c)); | |
| 1772 } | |
| 1773 } {} | |
| 1774 do_test fkey2-genfkey.1.2 { | |
| 1775 catchsql { INSERT INTO t2 VALUES(1, 2) } | |
| 1776 } {1 {FOREIGN KEY constraint failed}} | |
| 1777 do_test fkey2-genfkey.1.3 { | |
| 1778 execsql { | |
| 1779 INSERT INTO t1 VALUES(1, 2, 3); | |
| 1780 INSERT INTO t2 VALUES(1, 2); | |
| 1781 } | |
| 1782 } {} | |
| 1783 do_test fkey2-genfkey.1.4 { | |
| 1784 execsql { INSERT INTO t2 VALUES(NULL, 3) } | |
| 1785 } {} | |
| 1786 do_test fkey2-genfkey.1.5 { | |
| 1787 catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL } | |
| 1788 } {1 {FOREIGN KEY constraint failed}} | |
| 1789 do_test fkey2-genfkey.1.6 { | |
| 1790 execsql { UPDATE t2 SET e = 1 WHERE e IS NULL } | |
| 1791 } {} | |
| 1792 do_test fkey2-genfkey.1.7 { | |
| 1793 execsql { UPDATE t2 SET e = NULL WHERE f = 3 } | |
| 1794 } {} | |
| 1795 do_test fkey2-genfkey.1.8 { | |
| 1796 catchsql { UPDATE t1 SET a = 10 } | |
| 1797 } {1 {FOREIGN KEY constraint failed}} | |
| 1798 do_test fkey2-genfkey.1.9 { | |
| 1799 catchsql { UPDATE t1 SET a = NULL } | |
| 1800 } {1 {datatype mismatch}} | |
| 1801 do_test fkey2-genfkey.1.10 { | |
| 1802 catchsql { DELETE FROM t1 } | |
| 1803 } {1 {FOREIGN KEY constraint failed}} | |
| 1804 do_test fkey2-genfkey.1.11 { | |
| 1805 execsql { UPDATE t2 SET e = NULL } | |
| 1806 } {} | |
| 1807 do_test fkey2-genfkey.1.12 { | |
| 1808 execsql { | |
| 1809 UPDATE t1 SET a = 10; | |
| 1810 DELETE FROM t1; | |
| 1811 DELETE FROM t2; | |
| 1812 } | |
| 1813 } {} | |
| 1814 do_test fkey2-genfkey.1.13 { | |
| 1815 execsql { | |
| 1816 INSERT INTO t3 VALUES(1, NULL, NULL); | |
| 1817 INSERT INTO t3 VALUES(1, 2, NULL); | |
| 1818 INSERT INTO t3 VALUES(1, NULL, 3); | |
| 1819 } | |
| 1820 } {} | |
| 1821 do_test fkey2-genfkey.1.14 { | |
| 1822 catchsql { INSERT INTO t3 VALUES(3, 1, 4) } | |
| 1823 } {1 {FOREIGN KEY constraint failed}} | |
| 1824 do_test fkey2-genfkey.1.15 { | |
| 1825 execsql { | |
| 1826 INSERT INTO t1 VALUES(1, 1, 4); | |
| 1827 INSERT INTO t3 VALUES(3, 1, 4); | |
| 1828 } | |
| 1829 } {} | |
| 1830 do_test fkey2-genfkey.1.16 { | |
| 1831 catchsql { DELETE FROM t1 } | |
| 1832 } {1 {FOREIGN KEY constraint failed}} | |
| 1833 do_test fkey2-genfkey.1.17 { | |
| 1834 catchsql { UPDATE t1 SET b = 10} | |
| 1835 } {1 {FOREIGN KEY constraint failed}} | |
| 1836 do_test fkey2-genfkey.1.18 { | |
| 1837 execsql { UPDATE t1 SET a = 10} | |
| 1838 } {} | |
| 1839 do_test fkey2-genfkey.1.19 { | |
| 1840 catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3} | |
| 1841 } {1 {FOREIGN KEY constraint failed}} | |
| 1842 | |
| 1843 drop_all_tables | |
| 1844 do_test fkey2-genfkey.2.1 { | |
| 1845 execsql { | |
| 1846 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); | |
| 1847 CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f); | |
| 1848 CREATE TABLE t3(g, h, i, | |
| 1849 FOREIGN KEY (h, i) | |
| 1850 REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE | |
| 1851 ); | |
| 1852 } | |
| 1853 } {} | |
| 1854 do_test fkey2-genfkey.2.2 { | |
| 1855 execsql { | |
| 1856 INSERT INTO t1 VALUES(1, 2, 3); | |
| 1857 INSERT INTO t1 VALUES(4, 5, 6); | |
| 1858 INSERT INTO t2 VALUES(1, 'one'); | |
| 1859 INSERT INTO t2 VALUES(4, 'four'); | |
| 1860 } | |
| 1861 } {} | |
| 1862 do_test fkey2-genfkey.2.3 { | |
| 1863 execsql { | |
| 1864 UPDATE t1 SET a = 2 WHERE a = 1; | |
| 1865 SELECT * FROM t2; | |
| 1866 } | |
| 1867 } {2 one 4 four} | |
| 1868 do_test fkey2-genfkey.2.4 { | |
| 1869 execsql { | |
| 1870 DELETE FROM t1 WHERE a = 4; | |
| 1871 SELECT * FROM t2; | |
| 1872 } | |
| 1873 } {2 one} | |
| 1874 | |
| 1875 do_test fkey2-genfkey.2.5 { | |
| 1876 execsql { | |
| 1877 INSERT INTO t3 VALUES('hello', 2, 3); | |
| 1878 UPDATE t1 SET c = 2; | |
| 1879 SELECT * FROM t3; | |
| 1880 } | |
| 1881 } {hello 2 2} | |
| 1882 do_test fkey2-genfkey.2.6 { | |
| 1883 execsql { | |
| 1884 DELETE FROM t1; | |
| 1885 SELECT * FROM t3; | |
| 1886 } | |
| 1887 } {} | |
| 1888 | |
| 1889 drop_all_tables | |
| 1890 do_test fkey2-genfkey.3.1 { | |
| 1891 execsql { | |
| 1892 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b)); | |
| 1893 CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f); | |
| 1894 CREATE TABLE t3(g, h, i, | |
| 1895 FOREIGN KEY (h, i) | |
| 1896 REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL | |
| 1897 ); | |
| 1898 } | |
| 1899 } {} | |
| 1900 do_test fkey2-genfkey.3.2 { | |
| 1901 execsql { | |
| 1902 INSERT INTO t1 VALUES(1, 2, 3); | |
| 1903 INSERT INTO t1 VALUES(4, 5, 6); | |
| 1904 INSERT INTO t2 VALUES(1, 'one'); | |
| 1905 INSERT INTO t2 VALUES(4, 'four'); | |
| 1906 } | |
| 1907 } {} | |
| 1908 do_test fkey2-genfkey.3.3 { | |
| 1909 execsql { | |
| 1910 UPDATE t1 SET a = 2 WHERE a = 1; | |
| 1911 SELECT * FROM t2; | |
| 1912 } | |
| 1913 } {{} one 4 four} | |
| 1914 do_test fkey2-genfkey.3.4 { | |
| 1915 execsql { | |
| 1916 DELETE FROM t1 WHERE a = 4; | |
| 1917 SELECT * FROM t2; | |
| 1918 } | |
| 1919 } {{} one {} four} | |
| 1920 do_test fkey2-genfkey.3.5 { | |
| 1921 execsql { | |
| 1922 INSERT INTO t3 VALUES('hello', 2, 3); | |
| 1923 UPDATE t1 SET c = 2; | |
| 1924 SELECT * FROM t3; | |
| 1925 } | |
| 1926 } {hello {} {}} | |
| 1927 do_test fkey2-genfkey.3.6 { | |
| 1928 execsql { | |
| 1929 UPDATE t3 SET h = 2, i = 2; | |
| 1930 DELETE FROM t1; | |
| 1931 SELECT * FROM t3; | |
| 1932 } | |
| 1933 } {hello {} {}} | |
| 1934 | |
| 1935 #------------------------------------------------------------------------- | |
| 1936 # Verify that ticket dd08e5a988d00decc4a543daa8dbbfab9c577ad8 has been | |
| 1937 # fixed. | |
| 1938 # | |
| 1939 do_test fkey2-dd08e5.1.1 { | |
| 1940 execsql { | |
| 1941 PRAGMA foreign_keys=ON; | |
| 1942 CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b); | |
| 1943 CREATE UNIQUE INDEX idd08 ON tdd08(a,b); | |
| 1944 INSERT INTO tdd08 VALUES(200,300); | |
| 1945 | |
| 1946 CREATE TABLE tdd08_b(w,x,y, FOREIGN KEY(x,y) REFERENCES tdd08(a,b)); | |
| 1947 INSERT INTO tdd08_b VALUES(100,200,300); | |
| 1948 } | |
| 1949 } {} | |
| 1950 do_test fkey2-dd08e5.1.2 { | |
| 1951 catchsql { | |
| 1952 DELETE FROM tdd08; | |
| 1953 } | |
| 1954 } {1 {FOREIGN KEY constraint failed}} | |
| 1955 do_test fkey2-dd08e5.1.3 { | |
| 1956 execsql { | |
| 1957 SELECT * FROM tdd08; | |
| 1958 } | |
| 1959 } {200 300} | |
| 1960 do_test fkey2-dd08e5.1.4 { | |
| 1961 catchsql { | |
| 1962 INSERT INTO tdd08_b VALUES(400,500,300); | |
| 1963 } | |
| 1964 } {1 {FOREIGN KEY constraint failed}} | |
| 1965 do_test fkey2-dd08e5.1.5 { | |
| 1966 catchsql { | |
| 1967 UPDATE tdd08_b SET x=x+1; | |
| 1968 } | |
| 1969 } {1 {FOREIGN KEY constraint failed}} | |
| 1970 do_test fkey2-dd08e5.1.6 { | |
| 1971 catchsql { | |
| 1972 UPDATE tdd08 SET a=a+1; | |
| 1973 } | |
| 1974 } {1 {FOREIGN KEY constraint failed}} | |
| 1975 | |
| 1976 #------------------------------------------------------------------------- | |
| 1977 # Verify that ticket ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba | |
| 1978 # fixed. | |
| 1979 # | |
| 1980 do_test fkey2-ce7c13.1.1 { | |
| 1981 execsql { | |
| 1982 CREATE TABLE tce71(a INTEGER PRIMARY KEY, b); | |
| 1983 CREATE UNIQUE INDEX ice71 ON tce71(a,b); | |
| 1984 INSERT INTO tce71 VALUES(100,200); | |
| 1985 CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b)); | |
| 1986 INSERT INTO tce72 VALUES(300,100,200); | |
| 1987 UPDATE tce71 set b = 200 where a = 100; | |
| 1988 SELECT * FROM tce71, tce72; | |
| 1989 } | |
| 1990 } {100 200 300 100 200} | |
| 1991 do_test fkey2-ce7c13.1.2 { | |
| 1992 catchsql { | |
| 1993 UPDATE tce71 set b = 201 where a = 100; | |
| 1994 } | |
| 1995 } {1 {FOREIGN KEY constraint failed}} | |
| 1996 do_test fkey2-ce7c13.1.3 { | |
| 1997 catchsql { | |
| 1998 UPDATE tce71 set a = 101 where a = 100; | |
| 1999 } | |
| 2000 } {1 {FOREIGN KEY constraint failed}} | |
| 2001 do_test fkey2-ce7c13.1.4 { | |
| 2002 execsql { | |
| 2003 CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b)); | |
| 2004 INSERT INTO tce73 VALUES(100,200); | |
| 2005 CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b)); | |
| 2006 INSERT INTO tce74 VALUES(300,100,200); | |
| 2007 UPDATE tce73 set b = 200 where a = 100; | |
| 2008 SELECT * FROM tce73, tce74; | |
| 2009 } | |
| 2010 } {100 200 300 100 200} | |
| 2011 do_test fkey2-ce7c13.1.5 { | |
| 2012 catchsql { | |
| 2013 UPDATE tce73 set b = 201 where a = 100; | |
| 2014 } | |
| 2015 } {1 {FOREIGN KEY constraint failed}} | |
| 2016 do_test fkey2-ce7c13.1.6 { | |
| 2017 catchsql { | |
| 2018 UPDATE tce73 set a = 101 where a = 100; | |
| 2019 } | |
| 2020 } {1 {FOREIGN KEY constraint failed}} | |
| 2021 | |
| 2022 # 2015-04-16: Foreign key errors propagate back up to the parser. | |
| 2023 # | |
| 2024 do_test fkey2-20150416-100 { | |
| 2025 db close | |
| 2026 sqlite3 db :memory: | |
| 2027 catchsql { | |
| 2028 PRAGMA foreign_keys=1; | |
| 2029 CREATE TABLE t1(x PRIMARY KEY); | |
| 2030 CREATE TABLE t(y REFERENCES t0(x)ON DELETE SET DEFAULT); | |
| 2031 CREATE TABLE t0(y REFERENCES t1 ON DELETE SET NULL); | |
| 2032 REPLACE INTO t1 SELECT(0);CREATE TABLE t2(x);CREATE TABLE t3; | |
| 2033 } | |
| 2034 } {1 {foreign key mismatch - "t" referencing "t0"}} | |
| 2035 | |
| 2036 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2846743003:
[sql] Remove SQLite 3.10.2 reference directory. (Closed)
