Debian Codegraph @

test/fkey8.test

raw
1 # 2001 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 set testprefix fkey8
19
20 ifcapable {!foreignkey} {
21 finish_test
22 return
23 }
24 do_execsql_test 1.0 { PRAGMA foreign_keys = 1; }
25
26
27 foreach {tn use_stmt sql schema} {
28 1 1 "DELETE FROM p1" {
29 CREATE TABLE p1(a PRIMARY KEY);
30 CREATE TABLE c1(b REFERENCES p1);
31 }
32
33 2.1 0 "DELETE FROM p1" {
34 CREATE TABLE p1(a PRIMARY KEY);
35 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE);
36 }
37 2.2 0 "DELETE FROM p1" {
38 CREATE TABLE p1(a PRIMARY KEY);
39 CREATE TABLE c1(b REFERENCES p1 ON DELETE SET NULL);
40 }
41 2.3 1 "DELETE FROM p1" {
42 CREATE TABLE p1(a PRIMARY KEY);
43 CREATE TABLE c1(b REFERENCES p1 ON DELETE SET DEFAULT);
44 }
45
46 3 1 "DELETE FROM p1" {
47 CREATE TABLE p1(a PRIMARY KEY);
48 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE);
49 CREATE TRIGGER ct1 AFTER DELETE ON c1 BEGIN
50 INSERT INTO p1 VALUES('x');
51 END;
52 }
53
54 4 1 "DELETE FROM p1" {
55 CREATE TABLE p1(a PRIMARY KEY);
56 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY);
57 CREATE TABLE cc1(d REFERENCES c1);
58 }
59
60 5.1 0 "DELETE FROM p1" {
61 CREATE TABLE p1(a PRIMARY KEY);
62 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY);
63 CREATE TABLE cc1(d REFERENCES c1 ON DELETE CASCADE);
64 }
65 5.2 0 "DELETE FROM p1" {
66 CREATE TABLE p1(a PRIMARY KEY);
67 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY);
68 CREATE TABLE cc1(d REFERENCES c1 ON DELETE SET NULL);
69 }
70 5.3 1 "DELETE FROM p1" {
71 CREATE TABLE p1(a PRIMARY KEY);
72 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY);
73 CREATE TABLE cc1(d REFERENCES c1 ON DELETE SET DEFAULT);
74 }
75
76 6.1 1 "UPDATE p1 SET a = ?" {
77 CREATE TABLE p1(a PRIMARY KEY);
78 CREATE TABLE c1(b REFERENCES p1 ON UPDATE SET NULL, c);
79 }
80 6.2 0 "UPDATE OR IGNORE p1 SET a = ?" {
81 CREATE TABLE p1(a PRIMARY KEY);
82 CREATE TABLE c1(b REFERENCES p1 ON UPDATE SET NULL, c);
83 }
84 6.3 1 "UPDATE OR IGNORE p1 SET a = ?" {
85 CREATE TABLE p1(a PRIMARY KEY);
86 CREATE TABLE c1(b REFERENCES p1 ON UPDATE CASCADE, c);
87 }
88 6.4 1 "UPDATE OR IGNORE p1 SET a = ?" {
89 CREATE TABLE p1(a PRIMARY KEY);
90 CREATE TABLE c1(b NOT NULL REFERENCES p1 ON UPDATE SET NULL, c);
91 }
92
93 } {
94 drop_all_tables
95 do_test 1.$tn {
96 execsql $schema
97 set stmt [sqlite3_prepare_v2 db $sql -1 dummy]
98 set ret [uses_stmt_journal $stmt]
99 sqlite3_finalize $stmt
100 set ret
101 } $use_stmt
102 }
103
104 #-------------------------------------------------------------------------
105 # The following tests check that foreign key constaint counters are
106 # correctly updated for any implicit DELETE operations that occur
107 # when a REPLACE command is executed against a WITHOUT ROWID table
108 # that has no triggers or auxiliary indexes.
109 #
110 reset_db
111 do_execsql_test 2.1.0 {
112 PRAGMA foreign_keys = on;
113 CREATE TABLE p1(a PRIMARY KEY, b) WITHOUT ROWID;
114 CREATE TABLE c1(x REFERENCES p1 DEFERRABLE INITIALLY DEFERRED);
115
116 INSERT INTO p1 VALUES(1, 'one');
117 INSERT INTO p1 VALUES(2, 'two');
118 INSERT INTO c1 VALUES(1);
119 INSERT INTO c1 VALUES(2);
120 }
121
122 do_catchsql_test 2.1.2 {
123 BEGIN;
124 DELETE FROM p1 WHERE a=1;
125 INSERT OR REPLACE INTO p1 VALUES(2, 'two');
126 COMMIT;
127 } {1 {FOREIGN KEY constraint failed}}
128
129 reset_db
130 do_execsql_test 2.2.0 {
131 PRAGMA foreign_keys = on;
132 CREATE TABLE p2(a PRIMARY KEY, b);
133 CREATE TABLE c2(
134 x PRIMARY KEY,
135 y REFERENCES p2 DEFERRABLE INITIALLY DEFERRED
136 ) WITHOUT ROWID;
137 }
138
139 do_catchsql_test 2.2.1 {
140 BEGIN;
141 INSERT INTO c2 VALUES(13, 13);
142 INSERT OR REPLACE INTO c2 VALUES(13, 13);
143 DELETE FROM c2;
144 COMMIT;
145 } {0 {}}
146
147 reset_db
148 do_execsql_test 2.3.0 {
149 PRAGMA foreign_keys = on;
150 CREATE TABLE p3(a PRIMARY KEY, b) WITHOUT ROWID;
151 CREATE TABLE c3(x REFERENCES p3);
152
153 INSERT INTO p3 VALUES(1, 'one');
154 INSERT INTO p3 VALUES(2, 'two');
155 INSERT INTO c3 VALUES(1);
156 INSERT INTO c3 VALUES(2);
157
158 CREATE TRIGGER p3d AFTER DELETE ON p3 WHEN old.a=1 BEGIN
159 INSERT OR REPLACE INTO p3 VALUES(2, 'three');
160 END;
161 }
162
163 do_catchsql_test 2.3.1 {
164 DELETE FROM p3 WHERE a=1
165 } {1 {FOREIGN KEY constraint failed}}
166
167
168 do_execsql_test 3.0 {
169 PRAGMA foreign_keys=ON;
170 CREATE TABLE t2(
171 a PRIMARY KEY, b, c, d, e,
172 FOREIGN KEY(b, c) REFERENCES t2(d, e)
173 ) WITHOUT ROWID;
174 CREATE UNIQUE INDEX idx ON t2(d, e);
175
176 INSERT INTO t2 VALUES(1, 'one', 'one', 'one', 'one'); -- row is parent of self
177 INSERT INTO t2 VALUES(2, 'one', 'one', 'one', NULL); -- parent is row 1
178 }
179
180 do_catchsql_test 3.1 {
181 DELETE FROM t2 WHERE a=1;
182 } {1 {FOREIGN KEY constraint failed}}
183
184 do_execsql_test 4.0 {
185 CREATE TABLE t1 (
186 c1 PRIMARY KEY,
187 c2 NUMERIC,
188 FOREIGN KEY(c1) REFERENCES t1(c2)
189 ) WITHOUT ROWID ;
190 CREATE INDEX t1c1 ON t1(c1);
191 CREATE UNIQUE INDEX t1c1unique ON t1(c2);
192 }
193 do_catchsql_test 4.1 {
194 INSERT OR REPLACE INTO t1 VALUES(10000, 20000);
195 } {1 {FOREIGN KEY constraint failed}}
196 do_execsql_test 4.2 {
197 INSERT OR REPLACE INTO t1 VALUES(20000, 20000);
198 }
199
200 #-------------------------------------------------------------------------
201 reset_db
202 do_execsql_test 5.0 {
203 PRAGMA foreign_keys = true;
204 CREATE TABLE parent(
205 p TEXT PRIMARY KEY
206 );
207 CREATE TABLE child(
208 c INTEGER UNIQUE,
209 FOREIGN KEY(c) REFERENCES parent(p) DEFERRABLE INITIALLY DEFERRED
210 );
211 BEGIN;
212 INSERT INTO child VALUES(123);
213 INSERT INTO parent VALUES('123');
214 COMMIT;
215 }
216 do_execsql_test 5.1 {
217 PRAGMA integrity_check;
218 } {ok}
219
220 do_execsql_test 5.2 {
221 INSERT INTO parent VALUES(1200);
222 BEGIN;
223 INSERT INTO child VALUES(456);
224 UPDATE parent SET p = '456' WHERE p=1200;
225 COMMIT;
226 }
227 do_execsql_test 5.3 {
228 PRAGMA integrity_check;
229 } {ok}
230
231 #-------------------------------------------------------------------------
232 reset_db
233 forcedelete test.db2
234 do_execsql_test 6.1 {
235 PRAGMA foreign_keys = on;
236 CREATE TABLE c1(b);
237 INSERT INTO c1 VALUES(123);
238 }
239
240 do_execsql_test 6.2 {
241 ATTACH 'test.db2' AS aux;
242 CREATE TABLE aux.p1(a INTEGER PRIMARY KEY);
243 CREATE TABLE aux.c1(b REFERENCES p1(a) ON DELETE RESTRICT);
244
245 INSERT INTO aux.p1 VALUES(123);
246 }
247
248 do_execsql_test 6.3 {
249 DELETE FROM aux.p1 WHERE a=123;
250 }
251
252 #-------------------------------------------------------------------------
253 # Forum: https://sqlite.org/forum/forumpost/636bd0180a
254 #
255 reset_db
256 do_execsql_test 7.0 {
257 PRAGMA foreign_keys = ON;
258 CREATE TABLE p1 (pid PRIMARY KEY);
259 CREATE TABLE c1 (cid PRIMARY KEY,
260 pid REFERENCES p1(pid) ON UPDATE CASCADE
261 );
262 }
263
264 do_execsql_test 7.1 {
265 ATTACH ':memory:' AS aux;
266 CREATE TABLE aux.p1 (pid PRIMARY KEY);
267 CREATE TABLE aux.c1 (cid PRIMARY KEY,
268 pid REFERENCES p1(pid) ON UPDATE CASCADE);
269
270 INSERT INTO aux.p1 VALUES (10);
271 INSERT INTO aux.p1 VALUES (20);
272
273 INSERT INTO aux.c1 VALUES(11, 10);
274 INSERT INTO aux.c1 VALUES(12, 10);
275 INSERT INTO aux.c1 VALUES(21, 20);
276 INSERT INTO aux.c1 VALUES(22, 20);
277 }
278
279 do_catchsql_test 7.2 {
280 UPDATE aux.p1 SET pid = pid * 10;
281 } {0 {}}
282
283 do_execsql_test 7.3 {
284 SELECT * FROM aux.p1;
285 } {100 200}
286
287 do_execsql_test 7.4 {
288 SELECT * FROM aux.c1;
289 } {11 100 12 100 21 200 22 200}
290
291
292
293 finish_test