← Back to branch summary

~ubuntu-branches/ubuntu/precise/sqlite3/precise-updates

« back to all changes in this revision

Viewing changes to test/fkey2.test

  • Committer: Bazaar Package Importer
  • Author(s): Laszlo Boszormenyi (GCS)
  • Date: 2009-12-11 14:34:09 UTC
  • mfrom: (9.1.7 squeeze)
  • Revision ID: james.westby@ubuntu.com-20091211143409-o29fahwmcmyd0vq1
Tags: 3.6.21-2
http://bugs.debian.org/560660
Run autoreconf to prevent FTBFS with new libtool (closes: #560660).

Show diffs side-by-side

added added

removed removed

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