Debian Codegraph @

test/without_rowid3.test

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