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