| 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: e!=5}} |
| 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: e!=5}} |
| 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 |
INSERT INTO t2 VALUES(1,2); |
| 959 |
} |
| 960 |
catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 } |
| 961 |
} {0 {}} |
| 962 |
do_test fkey2-14.1.2 { |
| 963 |
catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 } |
| 964 |
} {0 {}} |
| 965 |
do_test fkey2-14.1.3 { |
| 966 |
catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL} |
| 967 |
} {0 {}} |
| 968 |
do_test fkey2-14.1.4 { |
| 969 |
catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'} |
| 970 |
} {1 {Cannot add a REFERENCES column with non-NULL default value}} |
| 971 |
do_test fkey2-14.1.5 { |
| 972 |
catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 } |
| 973 |
} {1 {Cannot add a REFERENCES column with non-NULL default value}} |
| 974 |
do_test fkey2-14.1.6 { |
| 975 |
execsql { |
| 976 |
PRAGMA foreign_keys = off; |
| 977 |
ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1; |
| 978 |
PRAGMA foreign_keys = on; |
| 979 |
SELECT sql FROM sqlite_master WHERE name='t2'; |
| 980 |
} |
| 981 |
} {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}} |
| 982 |
|
| 983 |
|
| 984 |
# Test the sqlite_rename_parent() function directly. |
| 985 |
# |
| 986 |
proc test_rename_parent {zCreate zOld zNew} { |
| 987 |
db eval {SELECT sqlite_rename_table( |
| 988 |
'main', 'table', 't1', $zCreate, $zOld, $zNew, 0 |
| 989 |
)} |
| 990 |
} |
| 991 |
sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db |
| 992 |
do_test fkey2-14.2.1.1 { |
| 993 |
test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3 |
| 994 |
} {{CREATE TABLE t1(a REFERENCES "t3")}} |
| 995 |
do_test fkey2-14.2.1.2 { |
| 996 |
test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3 |
| 997 |
} {{CREATE TABLE t1(a REFERENCES t2)}} |
| 998 |
do_test fkey2-14.2.1.3 { |
| 999 |
test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3 |
| 1000 |
} {{CREATE TABLE t1(a REFERENCES "t3")}} |
| 1001 |
sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db |
| 1002 |
|
| 1003 |
# Test ALTER TABLE RENAME TABLE a bit. |
| 1004 |
# |
| 1005 |
do_test fkey2-14.2.2.1 { |
| 1006 |
drop_all_tables |
| 1007 |
execsql { |
| 1008 |
CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1); |
| 1009 |
CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2); |
| 1010 |
CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1); |
| 1011 |
} |
| 1012 |
execsql { SELECT sql FROM sqlite_master WHERE type = 'table'} |
| 1013 |
} [list \ |
| 1014 |
{CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \ |
| 1015 |
{CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \ |
| 1016 |
{CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \ |
| 1017 |
] |
| 1018 |
do_test fkey2-14.2.2.2 { |
| 1019 |
execsql { ALTER TABLE t1 RENAME TO t4 } |
| 1020 |
execsql { SELECT sql FROM sqlite_master WHERE type = 'table'} |
| 1021 |
} [list \ |
| 1022 |
{CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \ |
| 1023 |
{CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \ |
| 1024 |
{CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \ |
| 1025 |
] |
| 1026 |
do_test fkey2-14.2.2.3 { |
| 1027 |
catchsql { INSERT INTO t3 VALUES(1, 2, 3) } |
| 1028 |
} {1 {FOREIGN KEY constraint failed}} |
| 1029 |
do_test fkey2-14.2.2.4 { |
| 1030 |
execsql { INSERT INTO t4 VALUES(1, NULL) } |
| 1031 |
} {} |
| 1032 |
do_test fkey2-14.2.2.5 { |
| 1033 |
catchsql { UPDATE t4 SET b = 5 } |
| 1034 |
} {1 {FOREIGN KEY constraint failed}} |
| 1035 |
do_test fkey2-14.2.2.6 { |
| 1036 |
catchsql { UPDATE t4 SET b = 1 } |
| 1037 |
} {0 {}} |
| 1038 |
do_test fkey2-14.2.2.7 { |
| 1039 |
execsql { INSERT INTO t3 VALUES(1, NULL, 1) } |
| 1040 |
} {} |
| 1041 |
|
| 1042 |
# Repeat for TEMP tables |
| 1043 |
# |
| 1044 |
drop_all_tables |
| 1045 |
do_test fkey2-14.1tmp.1 { |
| 1046 |
# Adding a column with a REFERENCES clause is not supported. |
| 1047 |
execsql { |
| 1048 |
CREATE TEMP TABLE t1(a PRIMARY KEY); |
| 1049 |
CREATE TEMP TABLE t2(a, b); |
| 1050 |
INSERT INTO temp.t2 VALUES(1,2); |
| 1051 |
} |
| 1052 |
catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 } |
| 1053 |
} {0 {}} |
| 1054 |
do_test fkey2-14.1tmp.2 { |
| 1055 |
catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 } |
| 1056 |
} {0 {}} |
| 1057 |
do_test fkey2-14.1tmp.3 { |
| 1058 |
catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL} |
| 1059 |
} {0 {}} |
| 1060 |
do_test fkey2-14.1tmp.4 { |
| 1061 |
catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'} |
| 1062 |
} {1 {Cannot add a REFERENCES column with non-NULL default value}} |
| 1063 |
do_test fkey2-14.1tmp.5 { |
| 1064 |
catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 } |
| 1065 |
} {1 {Cannot add a REFERENCES column with non-NULL default value}} |
| 1066 |
do_test fkey2-14.1tmp.6 { |
| 1067 |
execsql { |
| 1068 |
PRAGMA foreign_keys = off; |
| 1069 |
ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1; |
| 1070 |
PRAGMA foreign_keys = on; |
| 1071 |
SELECT sql FROM temp.sqlite_master WHERE name='t2'; |
| 1072 |
} |
| 1073 |
} {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}} |
| 1074 |
|
| 1075 |
sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db |
| 1076 |
do_test fkey2-14.2tmp.1.1 { |
| 1077 |
test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3 |
| 1078 |
} {{CREATE TABLE t1(a REFERENCES "t3")}} |
| 1079 |
do_test fkey2-14.2tmp.1.2 { |
| 1080 |
test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3 |
| 1081 |
} {{CREATE TABLE t1(a REFERENCES t2)}} |
| 1082 |
do_test fkey2-14.2tmp.1.3 { |
| 1083 |
test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3 |
| 1084 |
} {{CREATE TABLE t1(a REFERENCES "t3")}} |
| 1085 |
sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db |
| 1086 |
|
| 1087 |
# Test ALTER TABLE RENAME TABLE a bit. |
| 1088 |
# |
| 1089 |
do_test fkey2-14.2tmp.2.1 { |
| 1090 |
drop_all_tables |
| 1091 |
execsql { |
| 1092 |
CREATE TEMP TABLE t1(a PRIMARY KEY, b REFERENCES t1); |
| 1093 |
CREATE TEMP TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2); |
| 1094 |
CREATE TEMP TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1); |
| 1095 |
} |
| 1096 |
execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'} |
| 1097 |
} [list \ |
| 1098 |
{CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \ |
| 1099 |
{CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \ |
| 1100 |
{CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \ |
| 1101 |
] |
| 1102 |
do_test fkey2-14.2tmp.2.2 { |
| 1103 |
execsql { ALTER TABLE t1 RENAME TO t4 } |
| 1104 |
execsql { SELECT sql FROM temp.sqlite_master WHERE type = 'table'} |
| 1105 |
} [list \ |
| 1106 |
{CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \ |
| 1107 |
{CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \ |
| 1108 |
{CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \ |
| 1109 |
] |
| 1110 |
do_test fkey2-14.2tmp.2.3 { |
| 1111 |
catchsql { INSERT INTO t3 VALUES(1, 2, 3) } |
| 1112 |
} {1 {FOREIGN KEY constraint failed}} |
| 1113 |
do_test fkey2-14.2tmp.2.4 { |
| 1114 |
execsql { INSERT INTO t4 VALUES(1, NULL) } |
| 1115 |
} {} |
| 1116 |
do_test fkey2-14.2tmp.2.5 { |
| 1117 |
catchsql { UPDATE t4 SET b = 5 } |
| 1118 |
} {1 {FOREIGN KEY constraint failed}} |
| 1119 |
do_test fkey2-14.2tmp.2.6 { |
| 1120 |
catchsql { UPDATE t4 SET b = 1 } |
| 1121 |
} {0 {}} |
| 1122 |
do_test fkey2-14.2tmp.2.7 { |
| 1123 |
execsql { INSERT INTO t3 VALUES(1, NULL, 1) } |
| 1124 |
} {} |
| 1125 |
|
| 1126 |
# Repeat for ATTACH-ed tables |
| 1127 |
# |
| 1128 |
drop_all_tables |
| 1129 |
do_test fkey2-14.1aux.1 { |
| 1130 |
# Adding a column with a REFERENCES clause is not supported. |
| 1131 |
execsql { |
| 1132 |
ATTACH ':memory:' AS aux; |
| 1133 |
CREATE TABLE aux.t1(a PRIMARY KEY); |
| 1134 |
CREATE TABLE aux.t2(a, b); |
| 1135 |
INSERT INTO aux.t2(a,b) VALUES(1,2); |
| 1136 |
} |
| 1137 |
catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 } |
| 1138 |
} {0 {}} |
| 1139 |
do_test fkey2-14.1aux.2 { |
| 1140 |
catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 } |
| 1141 |
} {0 {}} |
| 1142 |
do_test fkey2-14.1aux.3 { |
| 1143 |
catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL} |
| 1144 |
} {0 {}} |
| 1145 |
do_test fkey2-14.1aux.4 { |
| 1146 |
catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'} |
| 1147 |
} {1 {Cannot add a REFERENCES column with non-NULL default value}} |
| 1148 |
do_test fkey2-14.1aux.5 { |
| 1149 |
catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 } |
| 1150 |
} {1 {Cannot add a REFERENCES column with non-NULL default value}} |
| 1151 |
do_test fkey2-14.1aux.6 { |
| 1152 |
execsql { |
| 1153 |
PRAGMA foreign_keys = off; |
| 1154 |
ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1; |
| 1155 |
PRAGMA foreign_keys = on; |
| 1156 |
SELECT sql FROM aux.sqlite_master WHERE name='t2'; |
| 1157 |
} |
| 1158 |
} {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}} |
| 1159 |
|
| 1160 |
sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db |
| 1161 |
do_test fkey2-14.2aux.1.1 { |
| 1162 |
test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3 |
| 1163 |
} {{CREATE TABLE t1(a REFERENCES "t3")}} |
| 1164 |
do_test fkey2-14.2aux.1.2 { |
| 1165 |
test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3 |
| 1166 |
} {{CREATE TABLE t1(a REFERENCES t2)}} |
| 1167 |
do_test fkey2-14.2aux.1.3 { |
| 1168 |
test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3 |
| 1169 |
} {{CREATE TABLE t1(a REFERENCES "t3")}} |
| 1170 |
sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db |
| 1171 |
|
| 1172 |
# Test ALTER TABLE RENAME TABLE a bit. |
| 1173 |
# |
| 1174 |
do_test fkey2-14.2aux.2.1 { |
| 1175 |
drop_all_tables |
| 1176 |
execsql { |
| 1177 |
CREATE TABLE aux.t1(a PRIMARY KEY, b REFERENCES t1); |
| 1178 |
CREATE TABLE aux.t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2); |
| 1179 |
CREATE TABLE aux.t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1); |
| 1180 |
} |
| 1181 |
execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'} |
| 1182 |
} [list \ |
| 1183 |
{CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \ |
| 1184 |
{CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \ |
| 1185 |
{CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \ |
| 1186 |
] |
| 1187 |
do_test fkey2-14.2aux.2.2 { |
| 1188 |
execsql { ALTER TABLE t1 RENAME TO t4 } |
| 1189 |
execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'} |
| 1190 |
} [list \ |
| 1191 |
{CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \ |
| 1192 |
{CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \ |
| 1193 |
{CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \ |
| 1194 |
] |
| 1195 |
do_test fkey2-14.2aux.2.3 { |
| 1196 |
catchsql { INSERT INTO t3 VALUES(1, 2, 3) } |
| 1197 |
} {1 {FOREIGN KEY constraint failed}} |
| 1198 |
do_test fkey2-14.2aux.2.4 { |
| 1199 |
execsql { INSERT INTO t4 VALUES(1, NULL) } |
| 1200 |
} {} |
| 1201 |
do_test fkey2-14.2aux.2.5 { |
| 1202 |
catchsql { UPDATE t4 SET b = 5 } |
| 1203 |
} {1 {FOREIGN KEY constraint failed}} |
| 1204 |
do_test fkey2-14.2aux.2.6 { |
| 1205 |
catchsql { UPDATE t4 SET b = 1 } |
| 1206 |
} {0 {}} |
| 1207 |
do_test fkey2-14.2aux.2.7 { |
| 1208 |
execsql { INSERT INTO t3 VALUES(1, NULL, 1) } |
| 1209 |
} {} |
| 1210 |
} |
| 1211 |
|
| 1212 |
do_test fkey-2.14.3.1 { |
| 1213 |
drop_all_tables |
| 1214 |
execsql { |
| 1215 |
CREATE TABLE t1(a, b REFERENCES nosuchtable); |
| 1216 |
DROP TABLE t1; |
| 1217 |
} |
| 1218 |
} {} |
| 1219 |
do_test fkey-2.14.3.2 { |
| 1220 |
execsql { |
| 1221 |
CREATE TABLE t1(a PRIMARY KEY, b); |
| 1222 |
INSERT INTO t1 VALUES('a', 1); |
| 1223 |
CREATE TABLE t2(x REFERENCES t1); |
| 1224 |
INSERT INTO t2 VALUES('a'); |
| 1225 |
} |
| 1226 |
} {} |
| 1227 |
do_test fkey-2.14.3.3 { |
| 1228 |
catchsql { DROP TABLE t1 } |
| 1229 |
} {1 {FOREIGN KEY constraint failed}} |
| 1230 |
do_test fkey-2.14.3.4 { |
| 1231 |
execsql { |
| 1232 |
DELETE FROM t2; |
| 1233 |
DROP TABLE t1; |
| 1234 |
} |
| 1235 |
} {} |
| 1236 |
do_test fkey-2.14.3.4 { |
| 1237 |
catchsql { INSERT INTO t2 VALUES('x') } |
| 1238 |
} {1 {no such table: main.t1}} |
| 1239 |
do_test fkey-2.14.3.5 { |
| 1240 |
execsql { |
| 1241 |
CREATE TABLE t1(x PRIMARY KEY); |
| 1242 |
INSERT INTO t1 VALUES('x'); |
| 1243 |
} |
| 1244 |
execsql { INSERT INTO t2 VALUES('x') } |
| 1245 |
} {} |
| 1246 |
do_test fkey-2.14.3.6 { |
| 1247 |
catchsql { DROP TABLE t1 } |
| 1248 |
} {1 {FOREIGN KEY constraint failed}} |
| 1249 |
do_test fkey-2.14.3.7 { |
| 1250 |
execsql { |
| 1251 |
DROP TABLE t2; |
| 1252 |
DROP TABLE t1; |
| 1253 |
} |
| 1254 |
} {} |
| 1255 |
do_test fkey-2.14.3.8 { |
| 1256 |
execsql { |
| 1257 |
CREATE TABLE pp(x, y, PRIMARY KEY(x, y)); |
| 1258 |
CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z)); |
| 1259 |
} |
| 1260 |
catchsql { INSERT INTO cc VALUES(1, 2) } |
| 1261 |
} {1 {foreign key mismatch - "cc" referencing "pp"}} |
| 1262 |
do_test fkey-2.14.3.9 { |
| 1263 |
execsql { DROP TABLE cc } |
| 1264 |
} {} |
| 1265 |
do_test fkey-2.14.3.10 { |
| 1266 |
execsql { |
| 1267 |
CREATE TABLE cc(a, b, |
| 1268 |
FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED |
| 1269 |
); |
| 1270 |
} |
| 1271 |
execsql { |
| 1272 |
INSERT INTO pp VALUES('a', 'b'); |
| 1273 |
INSERT INTO cc VALUES('a', 'b'); |
| 1274 |
BEGIN; |
| 1275 |
DROP TABLE pp; |
| 1276 |
CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c)); |
| 1277 |
INSERT INTO pp VALUES(1, 'a', 'b'); |
| 1278 |
COMMIT; |
| 1279 |
} |
| 1280 |
} {} |
| 1281 |
do_test fkey-2.14.3.11 { |
| 1282 |
execsql { |
| 1283 |
BEGIN; |
| 1284 |
DROP TABLE cc; |
| 1285 |
DROP TABLE pp; |
| 1286 |
COMMIT; |
| 1287 |
} |
| 1288 |
} {} |
| 1289 |
do_test fkey-2.14.3.12 { |
| 1290 |
execsql { |
| 1291 |
CREATE TABLE b1(a, b); |
| 1292 |
CREATE TABLE b2(a, b REFERENCES b1); |
| 1293 |
DROP TABLE b1; |
| 1294 |
} |
| 1295 |
} {} |
| 1296 |
do_test fkey-2.14.3.13 { |
| 1297 |
execsql { |
| 1298 |
CREATE TABLE b3(a, b REFERENCES b2 DEFERRABLE INITIALLY DEFERRED); |
| 1299 |
DROP TABLE b2; |
| 1300 |
} |
| 1301 |
} {} |
| 1302 |
|
| 1303 |
# Test that nothing goes wrong when dropping a table that refers to a view. |
| 1304 |
# Or dropping a view that an existing FK (incorrectly) refers to. Or either |
| 1305 |
# of the above scenarios with a virtual table. |
| 1306 |
drop_all_tables |
| 1307 |
do_test fkey-2.14.4.1 { |
| 1308 |
execsql { |
| 1309 |
CREATE TABLE t1(x REFERENCES v); |
| 1310 |
CREATE VIEW v AS SELECT * FROM t1; |
| 1311 |
} |
| 1312 |
} {} |
| 1313 |
do_test fkey-2.14.4.2 { |
| 1314 |
execsql { |
| 1315 |
DROP VIEW v; |
| 1316 |
} |
| 1317 |
} {} |
| 1318 |
ifcapable vtab { |
| 1319 |
register_echo_module db |
| 1320 |
do_test fkey-2.14.4.3 { |
| 1321 |
execsql { CREATE VIRTUAL TABLE v USING echo(t1) } |
| 1322 |
} {} |
| 1323 |
do_test fkey-2.14.4.2 { |
| 1324 |
execsql { |
| 1325 |
DROP TABLE v; |
| 1326 |
} |
| 1327 |
} {} |
| 1328 |
} |
| 1329 |
|
| 1330 |
#------------------------------------------------------------------------- |
| 1331 |
# The following tests, fkey2-15.*, test that unnecessary FK related scans |
| 1332 |
# and lookups are avoided when the constraint counters are zero. |
| 1333 |
# |
| 1334 |
drop_all_tables |
| 1335 |
proc execsqlS {zSql} { |
| 1336 |
set ::sqlite_search_count 0 |
| 1337 |
set ::sqlite_found_count 0 |
| 1338 |
set res [uplevel [list execsql $zSql]] |
| 1339 |
concat [expr $::sqlite_found_count + $::sqlite_search_count] $res |
| 1340 |
} |
| 1341 |
do_test fkey2-15.1.1 { |
| 1342 |
execsql { |
| 1343 |
CREATE TABLE pp(a PRIMARY KEY, b); |
| 1344 |
CREATE TABLE cc(x, y REFERENCES pp DEFERRABLE INITIALLY DEFERRED); |
| 1345 |
INSERT INTO pp VALUES(1, 'one'); |
| 1346 |
INSERT INTO pp VALUES(2, 'two'); |
| 1347 |
INSERT INTO cc VALUES('neung', 1); |
| 1348 |
INSERT INTO cc VALUES('song', 2); |
| 1349 |
} |
| 1350 |
} {} |
| 1351 |
do_test fkey2-15.1.2 { |
| 1352 |
execsqlS { INSERT INTO pp VALUES(3, 'three') } |
| 1353 |
} {0} |
| 1354 |
do_test fkey2-15.1.3 { |
| 1355 |
execsql { |
| 1356 |
BEGIN; |
| 1357 |
INSERT INTO cc VALUES('see', 4); -- Violates deferred constraint |
| 1358 |
} |
| 1359 |
execsqlS { INSERT INTO pp VALUES(5, 'five') } |
| 1360 |
} {2} |
| 1361 |
do_test fkey2-15.1.4 { |
| 1362 |
execsql { DELETE FROM cc WHERE x = 'see' } |
| 1363 |
execsqlS { INSERT INTO pp VALUES(6, 'six') } |
| 1364 |
} {0} |
| 1365 |
do_test fkey2-15.1.5 { |
| 1366 |
execsql COMMIT |
| 1367 |
} {} |
| 1368 |
do_test fkey2-15.1.6 { |
| 1369 |
execsql BEGIN |
| 1370 |
execsqlS { |
| 1371 |
DELETE FROM cc WHERE x = 'neung'; |
| 1372 |
ROLLBACK; |
| 1373 |
} |
| 1374 |
} {1} |
| 1375 |
do_test fkey2-15.1.7 { |
| 1376 |
execsql { |
| 1377 |
BEGIN; |
| 1378 |
DELETE FROM pp WHERE a = 2; |
| 1379 |
} |
| 1380 |
execsqlS { |
| 1381 |
DELETE FROM cc WHERE x = 'neung'; |
| 1382 |
ROLLBACK; |
| 1383 |
} |
| 1384 |
} {2} |
| 1385 |
|
| 1386 |
#------------------------------------------------------------------------- |
| 1387 |
# This next block of tests, fkey2-16.*, test that rows that refer to |
| 1388 |
# themselves may be inserted and deleted. |
| 1389 |
# |
| 1390 |
foreach {tn zSchema} { |
| 1391 |
1 { CREATE TABLE self(a INTEGER PRIMARY KEY, b REFERENCES self(a)) } |
| 1392 |
2 { CREATE TABLE self(a PRIMARY KEY, b REFERENCES self(a)) } |
| 1393 |
3 { CREATE TABLE self(a UNIQUE, b INTEGER PRIMARY KEY REFERENCES self(a)) } |
| 1394 |
} { |
| 1395 |
drop_all_tables |
| 1396 |
do_test fkey2-16.1.$tn.1 { |
| 1397 |
execsql $zSchema |
| 1398 |
execsql { INSERT INTO self VALUES(13, 13) } |
| 1399 |
} {} |
| 1400 |
do_test fkey2-16.1.$tn.2 { |
| 1401 |
execsql { UPDATE self SET a = 14, b = 14 } |
| 1402 |
} {} |
| 1403 |
|
| 1404 |
do_test fkey2-16.1.$tn.3 { |
| 1405 |
catchsql { UPDATE self SET b = 15 } |
| 1406 |
} {1 {FOREIGN KEY constraint failed}} |
| 1407 |
|
| 1408 |
do_test fkey2-16.1.$tn.4 { |
| 1409 |
catchsql { UPDATE self SET a = 15 } |
| 1410 |
} {1 {FOREIGN KEY constraint failed}} |
| 1411 |
|
| 1412 |
do_test fkey2-16.1.$tn.5 { |
| 1413 |
catchsql { UPDATE self SET a = 15, b = 16 } |
| 1414 |
} {1 {FOREIGN KEY constraint failed}} |
| 1415 |
|
| 1416 |
do_test fkey2-16.1.$tn.6 { |
| 1417 |
catchsql { UPDATE self SET a = 17, b = 17 } |
| 1418 |
} {0 {}} |
| 1419 |
|
| 1420 |
do_test fkey2-16.1.$tn.7 { |
| 1421 |
execsql { DELETE FROM self } |
| 1422 |
} {} |
| 1423 |
do_test fkey2-16.1.$tn.8 { |
| 1424 |
catchsql { INSERT INTO self VALUES(20, 21) } |
| 1425 |
} {1 {FOREIGN KEY constraint failed}} |
| 1426 |
} |
| 1427 |
|
| 1428 |
#------------------------------------------------------------------------- |
| 1429 |
# This next block of tests, fkey2-17.*, tests that if "PRAGMA count_changes" |
| 1430 |
# is turned on statements that violate immediate FK constraints return |
| 1431 |
# SQLITE_CONSTRAINT immediately, not after returning a number of rows. |
| 1432 |
# Whereas statements that violate deferred FK constraints return the number |
| 1433 |
# of rows before failing. |
| 1434 |
# |
| 1435 |
# Also test that rows modified by FK actions are not counted in either the |
| 1436 |
# returned row count or the values returned by sqlite3_changes(). Like |
| 1437 |
# trigger related changes, they are included in sqlite3_total_changes() though. |
| 1438 |
# |
| 1439 |
drop_all_tables |
| 1440 |
do_test fkey2-17.1.1 { |
| 1441 |
execsql { PRAGMA count_changes = 1 } |
| 1442 |
execsql { |
| 1443 |
CREATE TABLE one(a, b, c, UNIQUE(b, c)); |
| 1444 |
CREATE TABLE two(d, e, f, FOREIGN KEY(e, f) REFERENCES one(b, c)); |
| 1445 |
INSERT INTO one VALUES(1, 2, 3); |
| 1446 |
} |
| 1447 |
} {1} |
| 1448 |
do_test fkey2-17.1.2 { |
| 1449 |
set STMT [sqlite3_prepare_v2 db "INSERT INTO two VALUES(4, 5, 6)" -1 dummy] |
| 1450 |
sqlite3_step $STMT |
| 1451 |
} {SQLITE_CONSTRAINT} |
| 1452 |
verify_ex_errcode fkey2-17.1.2b SQLITE_CONSTRAINT_FOREIGNKEY |
| 1453 |
ifcapable autoreset { |
| 1454 |
do_test fkey2-17.1.3 { |
| 1455 |
sqlite3_step $STMT |
| 1456 |
} {SQLITE_CONSTRAINT} |
| 1457 |
verify_ex_errcode fkey2-17.1.3b SQLITE_CONSTRAINT_FOREIGNKEY |
| 1458 |
} else { |
| 1459 |
do_test fkey2-17.1.3 { |
| 1460 |
sqlite3_step $STMT |
| 1461 |
} {SQLITE_MISUSE} |
| 1462 |
} |
| 1463 |
do_test fkey2-17.1.4 { |
| 1464 |
sqlite3_finalize $STMT |
| 1465 |
} {SQLITE_CONSTRAINT} |
| 1466 |
verify_ex_errcode fkey2-17.1.4b SQLITE_CONSTRAINT_FOREIGNKEY |
| 1467 |
do_test fkey2-17.1.5 { |
| 1468 |
execsql { |
| 1469 |
INSERT INTO one VALUES(2, 3, 4); |
| 1470 |
INSERT INTO one VALUES(3, 4, 5); |
| 1471 |
INSERT INTO two VALUES(1, 2, 3); |
| 1472 |
INSERT INTO two VALUES(2, 3, 4); |
| 1473 |
INSERT INTO two VALUES(3, 4, 5); |
| 1474 |
} |
| 1475 |
} {1 1 1 1 1} |
| 1476 |
do_test fkey2-17.1.6 { |
| 1477 |
catchsql { |
| 1478 |
BEGIN; |
| 1479 |
INSERT INTO one VALUES(0, 0, 0); |
| 1480 |
UPDATE two SET e=e+1, f=f+1; |
| 1481 |
} |
| 1482 |
} {1 {FOREIGN KEY constraint failed}} |
| 1483 |
do_test fkey2-17.1.7 { |
| 1484 |
execsql { SELECT * FROM one } |
| 1485 |
} {1 2 3 2 3 4 3 4 5 0 0 0} |
| 1486 |
do_test fkey2-17.1.8 { |
| 1487 |
execsql { SELECT * FROM two } |
| 1488 |
} {1 2 3 2 3 4 3 4 5} |
| 1489 |
do_test fkey2-17.1.9 { |
| 1490 |
execsql COMMIT |
| 1491 |
} {} |
| 1492 |
do_test fkey2-17.1.10 { |
| 1493 |
execsql { |
| 1494 |
CREATE TABLE three( |
| 1495 |
g, h, i, |
| 1496 |
FOREIGN KEY(h, i) REFERENCES one(b, c) DEFERRABLE INITIALLY DEFERRED |
| 1497 |
); |
| 1498 |
} |
| 1499 |
} {} |
| 1500 |
do_test fkey2-17.1.11 { |
| 1501 |
set STMT [sqlite3_prepare_v2 db "INSERT INTO three VALUES(7, 8, 9)" -1 dummy] |
| 1502 |
sqlite3_step $STMT |
| 1503 |
} {SQLITE_ROW} |
| 1504 |
do_test fkey2-17.1.12 { |
| 1505 |
sqlite3_column_text $STMT 0 |
| 1506 |
} {1} |
| 1507 |
do_test fkey2-17.1.13 { |
| 1508 |
sqlite3_step $STMT |
| 1509 |
} {SQLITE_CONSTRAINT} |
| 1510 |
verify_ex_errcode fkey2-17.1.13b SQLITE_CONSTRAINT_FOREIGNKEY |
| 1511 |
do_test fkey2-17.1.14 { |
| 1512 |
sqlite3_finalize $STMT |
| 1513 |
} {SQLITE_CONSTRAINT} |
| 1514 |
verify_ex_errcode fkey2-17.1.14b SQLITE_CONSTRAINT_FOREIGNKEY |
| 1515 |
|
| 1516 |
drop_all_tables |
| 1517 |
do_test fkey2-17.2.1 { |
| 1518 |
execsql { |
| 1519 |
CREATE TABLE high("a'b!" PRIMARY KEY, b); |
| 1520 |
CREATE TABLE low( |
| 1521 |
c, |
| 1522 |
"d&6" REFERENCES high ON UPDATE CASCADE ON DELETE CASCADE |
| 1523 |
); |
| 1524 |
} |
| 1525 |
} {} |
| 1526 |
do_test fkey2-17.2.2 { |
| 1527 |
execsql { |
| 1528 |
INSERT INTO high VALUES('a', 'b'); |
| 1529 |
INSERT INTO low VALUES('b', 'a'); |
| 1530 |
} |
| 1531 |
db changes |
| 1532 |
} {1} |
| 1533 |
set nTotal [db total_changes] |
| 1534 |
do_test fkey2-17.2.3 { |
| 1535 |
execsql { UPDATE high SET "a'b!" = 'c' } |
| 1536 |
} {1} |
| 1537 |
do_test fkey2-17.2.4 { |
| 1538 |
db changes |
| 1539 |
} {1} |
| 1540 |
do_test fkey2-17.2.5 { |
| 1541 |
expr [db total_changes] - $nTotal |
| 1542 |
} {2} |
| 1543 |
do_test fkey2-17.2.6 { |
| 1544 |
execsql { SELECT * FROM high ; SELECT * FROM low } |
| 1545 |
} {c b b c} |
| 1546 |
do_test fkey2-17.2.7 { |
| 1547 |
execsql { DELETE FROM high } |
| 1548 |
} {1} |
| 1549 |
do_test fkey2-17.2.8 { |
| 1550 |
db changes |
| 1551 |
} {1} |
| 1552 |
do_test fkey2-17.2.9 { |
| 1553 |
expr [db total_changes] - $nTotal |
| 1554 |
} {4} |
| 1555 |
do_test fkey2-17.2.10 { |
| 1556 |
execsql { SELECT * FROM high ; SELECT * FROM low } |
| 1557 |
} {} |
| 1558 |
execsql { PRAGMA count_changes = 0 } |
| 1559 |
|
| 1560 |
#------------------------------------------------------------------------- |
| 1561 |
# Test that the authorization callback works. |
| 1562 |
# |
| 1563 |
|
| 1564 |
ifcapable auth { |
| 1565 |
do_test fkey2-18.1 { |
| 1566 |
execsql { |
| 1567 |
CREATE TABLE long(a, b PRIMARY KEY, c); |
| 1568 |
CREATE TABLE short(d, e, f REFERENCES long); |
| 1569 |
CREATE TABLE mid(g, h, i REFERENCES long DEFERRABLE INITIALLY DEFERRED); |
| 1570 |
} |
| 1571 |
} {} |
| 1572 |
|
| 1573 |
proc auth {args} {eval lappend ::authargs [lrange $args 0 4]; return SQLITE_OK} |
| 1574 |
db auth auth |
| 1575 |
|
| 1576 |
# An insert on the parent table must read the child key of any deferred |
| 1577 |
# foreign key constraints. But not the child key of immediate constraints. |
| 1578 |
set authargs {} |
| 1579 |
do_test fkey2-18.2 { |
| 1580 |
execsql { INSERT INTO long VALUES(1, 2, 3) } |
| 1581 |
set authargs |
| 1582 |
} {SQLITE_INSERT long {} main {} SQLITE_READ mid i main {}} |
| 1583 |
|
| 1584 |
# An insert on the child table of an immediate constraint must read the |
| 1585 |
# parent key columns (to see if it is a violation or not). |
| 1586 |
set authargs {} |
| 1587 |
do_test fkey2-18.3 { |
| 1588 |
execsql { INSERT INTO short VALUES(1, 3, 2) } |
| 1589 |
set authargs |
| 1590 |
} {SQLITE_INSERT short {} main {} SQLITE_READ long b main {}} |
| 1591 |
|
| 1592 |
# As must an insert on the child table of a deferred constraint. |
| 1593 |
set authargs {} |
| 1594 |
do_test fkey2-18.4 { |
| 1595 |
execsql { INSERT INTO mid VALUES(1, 3, 2) } |
| 1596 |
set authargs |
| 1597 |
} {SQLITE_INSERT mid {} main {} SQLITE_READ long b main {}} |
| 1598 |
|
| 1599 |
do_test fkey2-18.5 { |
| 1600 |
execsql { |
| 1601 |
CREATE TABLE nought(a, b PRIMARY KEY, c); |
| 1602 |
CREATE TABLE cross(d, e, f, |
| 1603 |
FOREIGN KEY(e) REFERENCES nought(b) ON UPDATE CASCADE |
| 1604 |
); |
| 1605 |
} |
| 1606 |
execsql { INSERT INTO nought VALUES(2, 1, 2) } |
| 1607 |
execsql { INSERT INTO cross VALUES(0, 1, 0) } |
| 1608 |
set authargs [list] |
| 1609 |
execsql { UPDATE nought SET b = 5 } |
| 1610 |
set authargs |
| 1611 |
} {SQLITE_UPDATE nought b main {} SQLITE_READ cross e main {} SQLITE_READ cross e main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {} SQLITE_UPDATE cross e main {} SQLITE_READ nought b main {} SQLITE_READ cross e main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {}} |
| 1612 |
|
| 1613 |
do_test fkey2-18.6 { |
| 1614 |
execsql {SELECT * FROM cross} |
| 1615 |
} {0 5 0} |
| 1616 |
|
| 1617 |
do_test fkey2-18.7 { |
| 1618 |
execsql { |
| 1619 |
CREATE TABLE one(a INTEGER PRIMARY KEY, b); |
| 1620 |
CREATE TABLE two(b, c REFERENCES one); |
| 1621 |
INSERT INTO one VALUES(101, 102); |
| 1622 |
} |
| 1623 |
set authargs [list] |
| 1624 |
execsql { INSERT INTO two VALUES(100, 101); } |
| 1625 |
set authargs |
| 1626 |
} {SQLITE_INSERT two {} main {} SQLITE_READ one a main {}} |
| 1627 |
|
| 1628 |
# Return SQLITE_IGNORE to requests to read from the parent table. This |
| 1629 |
# causes inserts of non-NULL keys into the child table to fail. |
| 1630 |
# |
| 1631 |
rename auth {} |
| 1632 |
proc auth {args} { |
| 1633 |
if {[lindex $args 1] == "long"} {return SQLITE_IGNORE} |
| 1634 |
return SQLITE_OK |
| 1635 |
} |
| 1636 |
do_test fkey2-18.8 { |
| 1637 |
catchsql { INSERT INTO short VALUES(1, 3, 2) } |
| 1638 |
} {1 {FOREIGN KEY constraint failed}} |
| 1639 |
do_test fkey2-18.9 { |
| 1640 |
execsql { INSERT INTO short VALUES(1, 3, NULL) } |
| 1641 |
} {} |
| 1642 |
do_test fkey2-18.10 { |
| 1643 |
execsql { SELECT * FROM short } |
| 1644 |
} {1 3 2 1 3 {}} |
| 1645 |
do_test fkey2-18.11 { |
| 1646 |
catchsql { UPDATE short SET f = 2 WHERE f IS NULL } |
| 1647 |
} {1 {FOREIGN KEY constraint failed}} |
| 1648 |
|
| 1649 |
db auth {} |
| 1650 |
unset authargs |
| 1651 |
} |
| 1652 |
|
| 1653 |
|
| 1654 |
do_test fkey2-19.1 { |
| 1655 |
execsql { |
| 1656 |
CREATE TABLE main(id INTEGER PRIMARY KEY); |
| 1657 |
CREATE TABLE sub(id INT REFERENCES main(id)); |
| 1658 |
INSERT INTO main VALUES(1); |
| 1659 |
INSERT INTO main VALUES(2); |
| 1660 |
INSERT INTO sub VALUES(2); |
| 1661 |
} |
| 1662 |
} {} |
| 1663 |
do_test fkey2-19.2 { |
| 1664 |
set S [sqlite3_prepare_v2 db "DELETE FROM main WHERE id = ?" -1 dummy] |
| 1665 |
sqlite3_bind_int $S 1 2 |
| 1666 |
sqlite3_step $S |
| 1667 |
} {SQLITE_CONSTRAINT} |
| 1668 |
verify_ex_errcode fkey2-19.2b SQLITE_CONSTRAINT_FOREIGNKEY |
| 1669 |
do_test fkey2-19.3 { |
| 1670 |
sqlite3_reset $S |
| 1671 |
} {SQLITE_CONSTRAINT} |
| 1672 |
verify_ex_errcode fkey2-19.3b SQLITE_CONSTRAINT_FOREIGNKEY |
| 1673 |
do_test fkey2-19.4 { |
| 1674 |
sqlite3_bind_int $S 1 1 |
| 1675 |
sqlite3_step $S |
| 1676 |
} {SQLITE_DONE} |
| 1677 |
do_test fkey2-19.4 { |
| 1678 |
sqlite3_finalize $S |
| 1679 |
} {SQLITE_OK} |
| 1680 |
|
| 1681 |
drop_all_tables |
| 1682 |
do_test fkey2-20.1 { |
| 1683 |
execsql { |
| 1684 |
CREATE TABLE pp(a PRIMARY KEY, b); |
| 1685 |
CREATE TABLE cc(c PRIMARY KEY, d REFERENCES pp); |
| 1686 |
} |
| 1687 |
} {} |
| 1688 |
|
| 1689 |
foreach {tn insert} { |
| 1690 |
1 "INSERT" |
| 1691 |
2 "INSERT OR IGNORE" |
| 1692 |
3 "INSERT OR ABORT" |
| 1693 |
4 "INSERT OR ROLLBACK" |
| 1694 |
5 "INSERT OR REPLACE" |
| 1695 |
6 "INSERT OR FAIL" |
| 1696 |
} { |
| 1697 |
do_test fkey2-20.2.$tn.1 { |
| 1698 |
catchsql "$insert INTO cc VALUES(1, 2)" |
| 1699 |
} {1 {FOREIGN KEY constraint failed}} |
| 1700 |
do_test fkey2-20.2.$tn.2 { |
| 1701 |
execsql { SELECT * FROM cc } |
| 1702 |
} {} |
| 1703 |
do_test fkey2-20.2.$tn.3 { |
| 1704 |
execsql { |
| 1705 |
BEGIN; |
| 1706 |
INSERT INTO pp VALUES(2, 'two'); |
| 1707 |
INSERT INTO cc VALUES(1, 2); |
| 1708 |
} |
| 1709 |
catchsql "$insert INTO cc VALUES(3, 4)" |
| 1710 |
} {1 {FOREIGN KEY constraint failed}} |
| 1711 |
do_test fkey2-20.2.$tn.4 { |
| 1712 |
execsql { COMMIT ; SELECT * FROM cc } |
| 1713 |
} {1 2} |
| 1714 |
do_test fkey2-20.2.$tn.5 { |
| 1715 |
execsql { DELETE FROM cc ; DELETE FROM pp } |
| 1716 |
} {} |
| 1717 |
} |
| 1718 |
|
| 1719 |
foreach {tn update} { |
| 1720 |
1 "UPDATE" |
| 1721 |
2 "UPDATE OR IGNORE" |
| 1722 |
3 "UPDATE OR ABORT" |
| 1723 |
4 "UPDATE OR ROLLBACK" |
| 1724 |
5 "UPDATE OR REPLACE" |
| 1725 |
6 "UPDATE OR FAIL" |
| 1726 |
} { |
| 1727 |
do_test fkey2-20.3.$tn.1 { |
| 1728 |
execsql { |
| 1729 |
INSERT INTO pp VALUES(2, 'two'); |
| 1730 |
INSERT INTO cc VALUES(1, 2); |
| 1731 |
} |
| 1732 |
} {} |
| 1733 |
do_test fkey2-20.3.$tn.2 { |
| 1734 |
catchsql "$update pp SET a = 1" |
| 1735 |
} {1 {FOREIGN KEY constraint failed}} |
| 1736 |
do_test fkey2-20.3.$tn.3 { |
| 1737 |
execsql { SELECT * FROM pp } |
| 1738 |
} {2 two} |
| 1739 |
do_test fkey2-20.3.$tn.4 { |
| 1740 |
catchsql "$update cc SET d = 1" |
| 1741 |
} {1 {FOREIGN KEY constraint failed}} |
| 1742 |
do_test fkey2-20.3.$tn.5 { |
| 1743 |
execsql { SELECT * FROM cc } |
| 1744 |
} {1 2} |
| 1745 |
do_test fkey2-20.3.$tn.6 { |
| 1746 |
execsql { |
| 1747 |
BEGIN; |
| 1748 |
INSERT INTO pp VALUES(3, 'three'); |
| 1749 |
} |
| 1750 |
catchsql "$update pp SET a = 1 WHERE a = 2" |
| 1751 |
} {1 {FOREIGN KEY constraint failed}} |
| 1752 |
do_test fkey2-20.3.$tn.7 { |
| 1753 |
execsql { COMMIT ; SELECT * FROM pp } |
| 1754 |
} {2 two 3 three} |
| 1755 |
do_test fkey2-20.3.$tn.8 { |
| 1756 |
execsql { |
| 1757 |
BEGIN; |
| 1758 |
INSERT INTO cc VALUES(2, 2); |
| 1759 |
} |
| 1760 |
catchsql "$update cc SET d = 1 WHERE c = 1" |
| 1761 |
} {1 {FOREIGN KEY constraint failed}} |
| 1762 |
do_test fkey2-20.3.$tn.9 { |
| 1763 |
execsql { COMMIT ; SELECT * FROM cc } |
| 1764 |
} {1 2 2 2} |
| 1765 |
do_test fkey2-20.3.$tn.10 { |
| 1766 |
execsql { DELETE FROM cc ; DELETE FROM pp } |
| 1767 |
} {} |
| 1768 |
} |
| 1769 |
|
| 1770 |
#------------------------------------------------------------------------- |
| 1771 |
# The following block of tests, those prefixed with "fkey2-genfkey.", are |
| 1772 |
# the same tests that were used to test the ".genfkey" command provided |
| 1773 |
# by the shell tool. So these tests show that the built-in foreign key |
| 1774 |
# implementation is more or less compatible with the triggers generated |
| 1775 |
# by genfkey. |
| 1776 |
# |
| 1777 |
drop_all_tables |
| 1778 |
do_test fkey2-genfkey.1.1 { |
| 1779 |
execsql { |
| 1780 |
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); |
| 1781 |
CREATE TABLE t2(e REFERENCES t1, f); |
| 1782 |
CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c)); |
| 1783 |
} |
| 1784 |
} {} |
| 1785 |
do_test fkey2-genfkey.1.2 { |
| 1786 |
catchsql { INSERT INTO t2 VALUES(1, 2) } |
| 1787 |
} {1 {FOREIGN KEY constraint failed}} |
| 1788 |
do_test fkey2-genfkey.1.3 { |
| 1789 |
execsql { |
| 1790 |
INSERT INTO t1 VALUES(1, 2, 3); |
| 1791 |
INSERT INTO t2 VALUES(1, 2); |
| 1792 |
} |
| 1793 |
} {} |
| 1794 |
do_test fkey2-genfkey.1.4 { |
| 1795 |
execsql { INSERT INTO t2 VALUES(NULL, 3) } |
| 1796 |
} {} |
| 1797 |
do_test fkey2-genfkey.1.5 { |
| 1798 |
catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL } |
| 1799 |
} {1 {FOREIGN KEY constraint failed}} |
| 1800 |
do_test fkey2-genfkey.1.6 { |
| 1801 |
execsql { UPDATE t2 SET e = 1 WHERE e IS NULL } |
| 1802 |
} {} |
| 1803 |
do_test fkey2-genfkey.1.7 { |
| 1804 |
execsql { UPDATE t2 SET e = NULL WHERE f = 3 } |
| 1805 |
} {} |
| 1806 |
do_test fkey2-genfkey.1.8 { |
| 1807 |
catchsql { UPDATE t1 SET a = 10 } |
| 1808 |
} {1 {FOREIGN KEY constraint failed}} |
| 1809 |
do_test fkey2-genfkey.1.9 { |
| 1810 |
catchsql { UPDATE t1 SET a = NULL } |
| 1811 |
} {1 {datatype mismatch}} |
| 1812 |
do_test fkey2-genfkey.1.10 { |
| 1813 |
catchsql { DELETE FROM t1 } |
| 1814 |
} {1 {FOREIGN KEY constraint failed}} |
| 1815 |
do_test fkey2-genfkey.1.11 { |
| 1816 |
execsql { UPDATE t2 SET e = NULL } |
| 1817 |
} {} |
| 1818 |
do_test fkey2-genfkey.1.12 { |
| 1819 |
execsql { |
| 1820 |
UPDATE t1 SET a = 10; |
| 1821 |
DELETE FROM t1; |
| 1822 |
DELETE FROM t2; |
| 1823 |
} |
| 1824 |
} {} |
| 1825 |
do_test fkey2-genfkey.1.13 { |
| 1826 |
execsql { |
| 1827 |
INSERT INTO t3 VALUES(1, NULL, NULL); |
| 1828 |
INSERT INTO t3 VALUES(1, 2, NULL); |
| 1829 |
INSERT INTO t3 VALUES(1, NULL, 3); |
| 1830 |
} |
| 1831 |
} {} |
| 1832 |
do_test fkey2-genfkey.1.14 { |
| 1833 |
catchsql { INSERT INTO t3 VALUES(3, 1, 4) } |
| 1834 |
} {1 {FOREIGN KEY constraint failed}} |
| 1835 |
do_test fkey2-genfkey.1.15 { |
| 1836 |
execsql { |
| 1837 |
INSERT INTO t1 VALUES(1, 1, 4); |
| 1838 |
INSERT INTO t3 VALUES(3, 1, 4); |
| 1839 |
} |
| 1840 |
} {} |
| 1841 |
do_test fkey2-genfkey.1.16 { |
| 1842 |
catchsql { DELETE FROM t1 } |
| 1843 |
} {1 {FOREIGN KEY constraint failed}} |
| 1844 |
do_test fkey2-genfkey.1.17 { |
| 1845 |
catchsql { UPDATE t1 SET b = 10} |
| 1846 |
} {1 {FOREIGN KEY constraint failed}} |
| 1847 |
do_test fkey2-genfkey.1.18 { |
| 1848 |
execsql { UPDATE t1 SET a = 10} |
| 1849 |
} {} |
| 1850 |
do_test fkey2-genfkey.1.19 { |
| 1851 |
catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3} |
| 1852 |
} {1 {FOREIGN KEY constraint failed}} |
| 1853 |
|
| 1854 |
drop_all_tables |
| 1855 |
do_test fkey2-genfkey.2.1 { |
| 1856 |
execsql { |
| 1857 |
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); |
| 1858 |
CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f); |
| 1859 |
CREATE TABLE t3(g, h, i, |
| 1860 |
FOREIGN KEY (h, i) |
| 1861 |
REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE |
| 1862 |
); |
| 1863 |
} |
| 1864 |
} {} |
| 1865 |
do_test fkey2-genfkey.2.2 { |
| 1866 |
execsql { |
| 1867 |
INSERT INTO t1 VALUES(1, 2, 3); |
| 1868 |
INSERT INTO t1 VALUES(4, 5, 6); |
| 1869 |
INSERT INTO t2 VALUES(1, 'one'); |
| 1870 |
INSERT INTO t2 VALUES(4, 'four'); |
| 1871 |
} |
| 1872 |
} {} |
| 1873 |
do_test fkey2-genfkey.2.3 { |
| 1874 |
execsql { |
| 1875 |
UPDATE t1 SET a = 2 WHERE a = 1; |
| 1876 |
SELECT * FROM t2; |
| 1877 |
} |
| 1878 |
} {2 one 4 four} |
| 1879 |
do_test fkey2-genfkey.2.4 { |
| 1880 |
execsql { |
| 1881 |
DELETE FROM t1 WHERE a = 4; |
| 1882 |
SELECT * FROM t2; |
| 1883 |
} |
| 1884 |
} {2 one} |
| 1885 |
|
| 1886 |
do_test fkey2-genfkey.2.5 { |
| 1887 |
execsql { |
| 1888 |
INSERT INTO t3 VALUES('hello', 2, 3); |
| 1889 |
UPDATE t1 SET c = 2; |
| 1890 |
SELECT * FROM t3; |
| 1891 |
} |
| 1892 |
} {hello 2 2} |
| 1893 |
do_test fkey2-genfkey.2.6 { |
| 1894 |
execsql { |
| 1895 |
DELETE FROM t1; |
| 1896 |
SELECT * FROM t3; |
| 1897 |
} |
| 1898 |
} {} |
| 1899 |
|
| 1900 |
drop_all_tables |
| 1901 |
do_test fkey2-genfkey.3.1 { |
| 1902 |
execsql { |
| 1903 |
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b)); |
| 1904 |
CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f); |
| 1905 |
CREATE TABLE t3(g, h, i, |
| 1906 |
FOREIGN KEY (h, i) |
| 1907 |
REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL |
| 1908 |
); |
| 1909 |
} |
| 1910 |
} {} |
| 1911 |
do_test fkey2-genfkey.3.2 { |
| 1912 |
execsql { |
| 1913 |
INSERT INTO t1 VALUES(1, 2, 3); |
| 1914 |
INSERT INTO t1 VALUES(4, 5, 6); |
| 1915 |
INSERT INTO t2 VALUES(1, 'one'); |
| 1916 |
INSERT INTO t2 VALUES(4, 'four'); |
| 1917 |
} |
| 1918 |
} {} |
| 1919 |
do_test fkey2-genfkey.3.3 { |
| 1920 |
execsql { |
| 1921 |
UPDATE t1 SET a = 2 WHERE a = 1; |
| 1922 |
SELECT * FROM t2; |
| 1923 |
} |
| 1924 |
} {{} one 4 four} |
| 1925 |
do_test fkey2-genfkey.3.4 { |
| 1926 |
execsql { |
| 1927 |
DELETE FROM t1 WHERE a = 4; |
| 1928 |
SELECT * FROM t2; |
| 1929 |
} |
| 1930 |
} {{} one {} four} |
| 1931 |
do_test fkey2-genfkey.3.5 { |
| 1932 |
execsql { |
| 1933 |
INSERT INTO t3 VALUES('hello', 2, 3); |
| 1934 |
UPDATE t1 SET c = 2; |
| 1935 |
SELECT * FROM t3; |
| 1936 |
} |
| 1937 |
} {hello {} {}} |
| 1938 |
do_test fkey2-genfkey.3.6 { |
| 1939 |
execsql { |
| 1940 |
UPDATE t3 SET h = 2, i = 2; |
| 1941 |
DELETE FROM t1; |
| 1942 |
SELECT * FROM t3; |
| 1943 |
} |
| 1944 |
} {hello {} {}} |
| 1945 |
|
| 1946 |
#------------------------------------------------------------------------- |
| 1947 |
# Verify that ticket dd08e5a988d00decc4a543daa8dbbfab9c577ad8 has been |
| 1948 |
# fixed. |
| 1949 |
# |
| 1950 |
do_test fkey2-dd08e5.1.1 { |
| 1951 |
execsql { |
| 1952 |
PRAGMA foreign_keys=ON; |
| 1953 |
CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b); |
| 1954 |
CREATE UNIQUE INDEX idd08 ON tdd08(a,b); |
| 1955 |
INSERT INTO tdd08 VALUES(200,300); |
| 1956 |
|
| 1957 |
CREATE TABLE tdd08_b(w,x,y, FOREIGN KEY(x,y) REFERENCES tdd08(a,b)); |
| 1958 |
INSERT INTO tdd08_b VALUES(100,200,300); |
| 1959 |
} |
| 1960 |
} {} |
| 1961 |
do_test fkey2-dd08e5.1.2 { |
| 1962 |
catchsql { |
| 1963 |
DELETE FROM tdd08; |
| 1964 |
} |
| 1965 |
} {1 {FOREIGN KEY constraint failed}} |
| 1966 |
do_test fkey2-dd08e5.1.3 { |
| 1967 |
execsql { |
| 1968 |
SELECT * FROM tdd08; |
| 1969 |
} |
| 1970 |
} {200 300} |
| 1971 |
do_test fkey2-dd08e5.1.4 { |
| 1972 |
catchsql { |
| 1973 |
INSERT INTO tdd08_b VALUES(400,500,300); |
| 1974 |
} |
| 1975 |
} {1 {FOREIGN KEY constraint failed}} |
| 1976 |
do_test fkey2-dd08e5.1.5 { |
| 1977 |
catchsql { |
| 1978 |
UPDATE tdd08_b SET x=x+1; |
| 1979 |
} |
| 1980 |
} {1 {FOREIGN KEY constraint failed}} |
| 1981 |
do_test fkey2-dd08e5.1.6 { |
| 1982 |
catchsql { |
| 1983 |
UPDATE tdd08 SET a=a+1; |
| 1984 |
} |
| 1985 |
} {1 {FOREIGN KEY constraint failed}} |
| 1986 |
|
| 1987 |
#------------------------------------------------------------------------- |
| 1988 |
# Verify that ticket ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba |
| 1989 |
# fixed. |
| 1990 |
# |
| 1991 |
do_test fkey2-ce7c13.1.1 { |
| 1992 |
execsql { |
| 1993 |
CREATE TABLE tce71(a INTEGER PRIMARY KEY, b); |
| 1994 |
CREATE UNIQUE INDEX ice71 ON tce71(a,b); |
| 1995 |
INSERT INTO tce71 VALUES(100,200); |
| 1996 |
CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b)); |
| 1997 |
INSERT INTO tce72 VALUES(300,100,200); |
| 1998 |
UPDATE tce71 set b = 200 where a = 100; |
| 1999 |
SELECT * FROM tce71, tce72; |
| 2000 |
} |
| 2001 |
} {100 200 300 100 200} |
| 2002 |
do_test fkey2-ce7c13.1.2 { |
| 2003 |
catchsql { |
| 2004 |
UPDATE tce71 set b = 201 where a = 100; |
| 2005 |
} |
| 2006 |
} {1 {FOREIGN KEY constraint failed}} |
| 2007 |
do_test fkey2-ce7c13.1.3 { |
| 2008 |
catchsql { |
| 2009 |
UPDATE tce71 set a = 101 where a = 100; |
| 2010 |
} |
| 2011 |
} {1 {FOREIGN KEY constraint failed}} |
| 2012 |
do_test fkey2-ce7c13.1.4 { |
| 2013 |
execsql { |
| 2014 |
CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b)); |
| 2015 |
INSERT INTO tce73 VALUES(100,200); |
| 2016 |
CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b)); |
| 2017 |
INSERT INTO tce74 VALUES(300,100,200); |
| 2018 |
UPDATE tce73 set b = 200 where a = 100; |
| 2019 |
SELECT * FROM tce73, tce74; |
| 2020 |
} |
| 2021 |
} {100 200 300 100 200} |
| 2022 |
do_test fkey2-ce7c13.1.5 { |
| 2023 |
catchsql { |
| 2024 |
UPDATE tce73 set b = 201 where a = 100; |
| 2025 |
} |
| 2026 |
} {1 {FOREIGN KEY constraint failed}} |
| 2027 |
do_test fkey2-ce7c13.1.6 { |
| 2028 |
catchsql { |
| 2029 |
UPDATE tce73 set a = 101 where a = 100; |
| 2030 |
} |
| 2031 |
} {1 {FOREIGN KEY constraint failed}} |
| 2032 |
|
| 2033 |
# 2015-04-16: Foreign key errors propagate back up to the parser. |
| 2034 |
# |
| 2035 |
do_test fkey2-20150416-100 { |
| 2036 |
db close |
| 2037 |
sqlite3 db :memory: |
| 2038 |
catchsql { |
| 2039 |
PRAGMA foreign_keys=1; |
| 2040 |
CREATE TABLE t1(x PRIMARY KEY); |
| 2041 |
CREATE TABLE t(y REFERENCES t0(x)ON DELETE SET DEFAULT); |
| 2042 |
CREATE TABLE t0(y REFERENCES t1 ON DELETE SET NULL); |
| 2043 |
REPLACE INTO t1 SELECT(0);CREATE TABLE t2(x);CREATE TABLE t3; |
| 2044 |
} |
| 2045 |
} {1 {foreign key mismatch - "t" referencing "t0"}} |
| 2046 |
|
| 2047 |
finish_test |