/ Check-in [ffa6207d]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Extra test cases mapped to statements in foreignkeys.html.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ffa6207dd72024f01ab6f2715fe628943f178ca5
User & Date: dan 2009-10-12 11:27:01
Context
2009-10-12
15:25
Update e_fkey.test to match the latest version of foreignkeys.html. check-in: a2ca9f1a user: dan tags: trunk
11:27
Extra test cases mapped to statements in foreignkeys.html. check-in: ffa6207d user: dan tags: trunk
08:41
Add further test cases to e_fkey.test. check-in: d61cc0e1 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/e_fkey.test.

8
9
10
11
12
13
14







15

16
17


























































































18

19


























20




21

































































22
23
24

25
26
27
28
29
30
31
...
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file implements tests to verify the "testable statements" in the
# foreignkeys.in document.
#









set testdir [file dirname $argv0]
source $testdir/tester.tcl


























































































ifcapable {!foreignkey} { finish_test ; return }




























execsql "PRAGMA foreign_keys = ON"






































































###########################################################################
### SECTION 1: Introduction to Foreign Key Constraints
###########################################################################


#-------------------------------------------------------------------------
# /* EV: R-04042-24825 */
#
# Verify that the syntax in the first example in section 1 is valid.
#
do_test e_fkey-38.1 {
................................................................................
do_test e_fkey-47.3 {
  execsql { SELECT typeof(a) FROM t1 }
} {integer integer text}
do_test e_fkey-47.4 {
  catchsql { DELETE FROM t1 WHERE rowid = 2 }
} {1 {foreign key constraint failed}}

###########################################################################
### SECTION 2: Enabling Foreign Key Support
###########################################################################

###########################################################################
### SECTION 3: Required and Suggested Database Indexes
###########################################################################

###########################################################################
### SECTION 4.1: Composite Foreign Key Constraints
###########################################################################







>
>
>
>
>
>
>
|
>


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



>







 







<
<
<
<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
...
560
561
562
563
564
565
566




567
568
569
570
571
572
573
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file implements tests to verify the "testable statements" in the
# foreignkeys.in document.
#
# The tests in this file are arranged to mirror the structure of 
# foreignkey.in, with one exception: The statements in section 2, which 
# deals with enabling/disabling foreign key support, is tested first,
# before section 1. This is because some statements in section 2 deal
# with builds that do not include complete foreign key support (because
# either SQLITE_OMIT_TRIGGER or SQLITE_OMIT_FOREIGN_KEY was defined
# at build time).
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

###########################################################################
### SECTION 2: Enabling Foreign Key Support
###########################################################################

#-------------------------------------------------------------------------
# /* EV: R-33710-56344 */
#
# Test builds neither OMIT_FOREIGN_KEY or OMIT_TRIGGER defined have 
# foreign key functionality.
#
ifcapable trigger&&foreignkey {
  do_test e_fkey-49 {
    execsql {
      PRAGMA foreign_keys = ON;
      CREATE TABLE p(i PRIMARY KEY);
      CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);
      INSERT INTO p VALUES('hello');
      INSERT INTO c VALUES('hello');
      UPDATE p SET i = 'world';
      SELECT * FROM c;
    }
  } {world}
}

#-------------------------------------------------------------------------
# /* EV: R-44697-61543 */
#
# Test the effects of defining OMIT_TRIGGER but not OMIT_FOREIGN_KEY.
#
# /* EV: R-22567-44039 */
# /* EV: R-60444-29168 */
#
# Specifically, test that "PRAGMA foreign_keys" is a no-op in this case.
# When using the pragma to query the current setting, 0 rows are returned.
#
reset_db
ifcapable !trigger&&foreignkey {
  do_test e_fkey-51.1 {
    execsql {
      PRAGMA foreign_keys = ON;
      CREATE TABLE p(i PRIMARY KEY);
      CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);
      INSERT INTO p VALUES('hello');
      INSERT INTO c VALUES('hello');
      UPDATE p SET i = 'world';
      SELECT * FROM c;
    }
  } {hello}
  do_test e_fkey-51.2 {
    execsql { PRAGMA foreign_key_list(c) }
  } {0 0 p j {} CASCADE {NO ACTION} NONE}
  do_test e_fkey-51.3 {
    execsql { PRAGMA foreign_keys }
  } {}
}


#-------------------------------------------------------------------------
# /* EV: R-58428-36660 */
#
# Test the effects of defining OMIT_FOREIGN_KEY.
#
# /* EV: R-58428-36660 */
#
# Specifically, test that foreign key constraints cannot even be parsed 
# in such a build.
#
reset_db
ifcapable !foreignkey {
  do_test e_fkey-52.1 {
    execsql { CREATE TABLE p(i PRIMARY KEY) }
    catchsql { CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE) }
  } {1 {near "ON": syntax error}}
  do_test e_fkey-52.2 {
    # This is allowed, as in this build, "REFERENCES" is not a keyword.
    # The declared datatype of column j is "REFERENCES p".
    execsql { CREATE TABLE c(j REFERENCES p) }
  } {}
  do_test e_fkey-52.3 {
    execsql { PRAGMA table_info(c) }
  } {0 j {REFERENCES p} 0 {} 0}
  do_test e_fkey-52.4 {
    execsql { PRAGMA foreign_key_list(c) }
  } {}
  do_test e_fkey-52.5 {
    execsql { PRAGMA foreign_keys }
  } {}
}

ifcapable !foreignkey||!trigger { finish_test ; return }
reset_db


#-------------------------------------------------------------------------
# /* EV: R-07280-60510 */
#
# Test that even if foreign keys are supported by the build, they must
# be enabled using "PRAGMA foreign_keys = ON" (or similar).
#
# /* EV: R-15831-45974 */
#
# This also tests that foreign key constraints are disabled by default.
#
drop_all_tables
do_test e_fkey-53.1 {
  execsql {
    CREATE TABLE p(i PRIMARY KEY);
    CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);
    INSERT INTO p VALUES('hello');
    INSERT INTO c VALUES('hello');
    UPDATE p SET i = 'world';
    SELECT * FROM c;
  } 
} {hello}
do_test e_fkey-53.2 {
  execsql {
    DELETE FROM c;
    DELETE FROM p;
    PRAGMA foreign_keys = ON;
    INSERT INTO p VALUES('hello');
    INSERT INTO c VALUES('hello');
    UPDATE p SET i = 'world';
    SELECT * FROM c;
  } 
} {world}

#-------------------------------------------------------------------------
# /* EV: R-15278-54456 */
# /* EV: R-11255-19907 */
#
# Test that the application can use "PRAGMA foreign_keys" to query for
# whether or not foreign keys are currently enabled. This also tests
# the example code in section 2 of foreignkeys.in.
#
reset_db
do_test e_fkey-54.1 {
  execsql { PRAGMA foreign_keys }
} {0}
do_test e_fkey-54.2 {
  execsql { 
    PRAGMA foreign_keys = ON;
    PRAGMA foreign_keys;
  }
} {1}
do_test e_fkey-54.3 {
  execsql { 
    PRAGMA foreign_keys = OFF;
    PRAGMA foreign_keys;
  }
} {0}

#-------------------------------------------------------------------------
# /* EV: R-07050-54503 */
#
# Test that it is not possible to enable or disable foreign key support
# while not in auto-commit mode.
#
reset_db
do_test e_fkey-55.1 {
  execsql {
    PRAGMA foreign_keys = ON;
    CREATE TABLE t1(a UNIQUE, b);
    CREATE TABLE t2(c, d REFERENCES t1(a));
    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t2 VALUES(2, 1);
    BEGIN;
      PRAGMA foreign_keys = OFF;
  }
  catchsql {
      DELETE FROM t1
  }
} {1 {foreign key constraint failed}}
do_test e_fkey-55.2 {
  execsql { PRAGMA foreign_keys }
} {1}
do_test e_fkey-55.3 {
  execsql {
    COMMIT;
    PRAGMA foreign_keys = OFF;
    BEGIN;
      PRAGMA foreign_keys = ON;
      DELETE FROM t1;
      PRAGMA foreign_keys;
  }
} {0}
do_test e_fkey-55.4 {
  execsql COMMIT
} {}

###########################################################################
### SECTION 1: Introduction to Foreign Key Constraints
###########################################################################
execsql "PRAGMA foreign_keys = ON"

#-------------------------------------------------------------------------
# /* EV: R-04042-24825 */
#
# Verify that the syntax in the first example in section 1 is valid.
#
do_test e_fkey-38.1 {
................................................................................
do_test e_fkey-47.3 {
  execsql { SELECT typeof(a) FROM t1 }
} {integer integer text}
do_test e_fkey-47.4 {
  catchsql { DELETE FROM t1 WHERE rowid = 2 }
} {1 {foreign key constraint failed}}





###########################################################################
### SECTION 3: Required and Suggested Database Indexes
###########################################################################

###########################################################################
### SECTION 4.1: Composite Foreign Key Constraints
###########################################################################