| blob | 02b99f2ac9137c18a8507855d083f7393f8da49e |
1 # 2005 November 2
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. The
12 # focus of this file is testing CHECK constraints
13 #
14 # $Id: check.test,v 1.13 2009/06/05 17:09:12 drh Exp $
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 set ::testprefix check
20 # Only run these tests if the build includes support for CHECK constraints
21 ifcapable !check {
22 finish_test
23 return
24 }
26 do_test check-1.1 {
27 execsql {
28 CREATE TABLE t1(
29 x INTEGER CHECK( x<5 ),
30 y REAL CHECK( y>x )
31 );
32 }
33 } {}
34 do_test check-1.2 {
35 execsql {
36 INSERT INTO t1 VALUES(3,4);
37 SELECT * FROM t1;
38 }
39 } {3 4.0}
40 do_test check-1.3 {
41 catchsql {
42 INSERT INTO t1 VALUES(6,7);
43 }
44 } {1 {CHECK constraint failed: t1}}
45 do_test check-1.4 {
46 execsql {
47 SELECT * FROM t1;
48 }
49 } {3 4.0}
50 do_test check-1.5 {
51 catchsql {
52 INSERT INTO t1 VALUES(4,3);
53 }
54 } {1 {CHECK constraint failed: t1}}
55 do_test check-1.6 {
56 execsql {
57 SELECT * FROM t1;
58 }
59 } {3 4.0}
60 do_test check-1.7 {
61 catchsql {
62 INSERT INTO t1 VALUES(NULL,6);
63 }
64 } {0 {}}
65 do_test check-1.8 {
66 execsql {
67 SELECT * FROM t1;
68 }
69 } {3 4.0 {} 6.0}
70 do_test check-1.9 {
71 catchsql {
72 INSERT INTO t1 VALUES(2,NULL);
73 }
74 } {0 {}}
75 do_test check-1.10 {
76 execsql {
77 SELECT * FROM t1;
78 }
79 } {3 4.0 {} 6.0 2 {}}
80 do_test check-1.11 {
81 execsql {
82 DELETE FROM t1 WHERE x IS NULL OR x!=3;
83 UPDATE t1 SET x=2 WHERE x==3;
84 SELECT * FROM t1;
85 }
86 } {2 4.0}
87 do_test check-1.12 {
88 catchsql {
89 UPDATE t1 SET x=7 WHERE x==2
90 }
91 } {1 {CHECK constraint failed: t1}}
92 do_test check-1.13 {
93 execsql {
94 SELECT * FROM t1;
95 }
96 } {2 4.0}
97 do_test check-1.14 {
98 catchsql {
99 UPDATE t1 SET x=5 WHERE x==2
100 }
101 } {1 {CHECK constraint failed: t1}}
102 do_test check-1.15 {
103 execsql {
104 SELECT * FROM t1;
105 }
106 } {2 4.0}
107 do_test check-1.16 {
108 catchsql {
109 UPDATE t1 SET x=4, y=11 WHERE x==2
110 }
111 } {0 {}}
112 do_test check-1.17 {
113 execsql {
114 SELECT * FROM t1;
115 }
116 } {4 11.0}
118 do_test check-2.1 {
119 execsql {
120 CREATE TABLE t2(
121 x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=="integer" ),
122 y REAL CONSTRAINT two CHECK( typeof(coalesce(y,0.1))=='real' ),
123 z TEXT CONSTRAINT three CHECK( typeof(coalesce(z,''))=='text' )
124 );
125 }
126 } {}
127 do_test check-2.2 {
128 execsql {
129 INSERT INTO t2 VALUES(1,2.2,'three');
130 SELECT * FROM t2;
131 }
132 } {1 2.2 three}
133 db close
134 sqlite3 db test.db
135 do_test check-2.3 {
136 execsql {
137 INSERT INTO t2 VALUES(NULL, NULL, NULL);
138 SELECT * FROM t2;
139 }
140 } {1 2.2 three {} {} {}}
141 do_test check-2.4 {
142 catchsql {
143 INSERT INTO t2 VALUES(1.1, NULL, NULL);
144 }
145 } {1 {CHECK constraint failed: one}}
146 do_test check-2.5 {
147 catchsql {
148 INSERT INTO t2 VALUES(NULL, 5, NULL);
149 }
150 } {1 {CHECK constraint failed: two}}
151 do_test check-2.6 {
152 catchsql {
153 INSERT INTO t2 VALUES(NULL, NULL, 3.14159);
154 }
155 } {1 {CHECK constraint failed: three}}
157 # Undocumented behavior: The CONSTRAINT name clause can follow a constraint.
158 # Such a clause is ignored. But the parser must accept it for backwards
159 # compatibility.
160 #
161 do_test check-2.10 {
162 execsql {
163 CREATE TABLE t2b(
164 x INTEGER CHECK( typeof(coalesce(x,0))=='integer' ) CONSTRAINT one,
165 y TEXT PRIMARY KEY constraint two,
166 z INTEGER,
167 UNIQUE(x,z) constraint three
168 );
169 }
170 } {}
171 do_test check-2.11 {
172 catchsql {
173 INSERT INTO t2b VALUES('xyzzy','hi',5);
174 }
175 } {1 {CHECK constraint failed: t2b}}
176 do_test check-2.12 {
177 execsql {
178 CREATE TABLE t2c(
179 x INTEGER CONSTRAINT x_one CONSTRAINT x_two
180 CHECK( typeof(coalesce(x,0))=='integer' )
181 CONSTRAINT x_two CONSTRAINT x_three,
182 y INTEGER, z INTEGER,
183 CONSTRAINT u_one UNIQUE(x,y,z) CONSTRAINT u_two
184 );
185 }
186 } {}
187 do_test check-2.13 {
188 catchsql {
189 INSERT INTO t2c VALUES('xyzzy',7,8);
190 }
191 } {1 {CHECK constraint failed: x_two}}
192 do_test check-2.cleanup {
193 execsql {
194 DROP TABLE IF EXISTS t2b;
195 DROP TABLE IF EXISTS t2c;
196 }
197 } {}
199 ifcapable subquery {
200 do_test check-3.1 {
201 catchsql {
202 CREATE TABLE t3(
203 x, y, z,
204 CHECK( x<(SELECT min(x) FROM t1) )
205 );
206 }
207 } {1 {subqueries prohibited in CHECK constraints}}
208 }
210 do_test check-3.2 {
211 execsql {
212 SELECT name FROM sqlite_master ORDER BY name
213 }
214 } {t1 t2}
215 do_test check-3.3 {
216 catchsql {
217 CREATE TABLE t3(
218 x, y, z,
219 CHECK( q<x )
220 );
221 }
222 } {1 {no such column: q}}
223 do_test check-3.4 {
224 execsql {
225 SELECT name FROM sqlite_master ORDER BY name
226 }
227 } {t1 t2}
228 do_test check-3.5 {
229 catchsql {
230 CREATE TABLE t3(
231 x, y, z,
232 CHECK( t2.x<x )
233 );
234 }
235 } {1 {no such column: t2.x}}
236 do_test check-3.6 {
237 execsql {
238 SELECT name FROM sqlite_master ORDER BY name
239 }
240 } {t1 t2}
241 do_test check-3.7 {
242 catchsql {
243 CREATE TABLE t3(
244 x, y, z,
245 CHECK( t3.x<25 )
246 );
247 }
248 } {0 {}}
249 do_test check-3.8 {
250 execsql {
251 INSERT INTO t3 VALUES(1,2,3);
252 SELECT * FROM t3;
253 }
254 } {1 2 3}
255 do_test check-3.9 {
256 catchsql {
257 INSERT INTO t3 VALUES(111,222,333);
258 }
259 } {1 {CHECK constraint failed: t3}}
261 do_test check-4.1 {
262 execsql {
263 CREATE TABLE t4(x, y,
264 CHECK (
265 x+y==11
266 OR x*y==12
267 OR x/y BETWEEN 5 AND 8
268 OR -x==y+10
269 )
270 );
271 }
272 } {}
273 do_test check-4.2 {
274 execsql {
275 INSERT INTO t4 VALUES(1,10);
276 SELECT * FROM t4
277 }
278 } {1 10}
279 do_test check-4.3 {
280 execsql {
281 UPDATE t4 SET x=4, y=3;
282 SELECT * FROM t4
283 }
284 } {4 3}
285 do_test check-4.4 {
286 execsql {
287 UPDATE t4 SET x=12, y=2;
288 SELECT * FROM t4
289 }
290 } {12 2}
291 do_test check-4.5 {
292 execsql {
293 UPDATE t4 SET x=12, y=-22;
294 SELECT * FROM t4
295 }
296 } {12 -22}
297 do_test check-4.6 {
298 catchsql {
299 UPDATE t4 SET x=0, y=1;
300 }
301 } {1 {CHECK constraint failed: t4}}
302 do_test check-4.7 {
303 execsql {
304 SELECT * FROM t4;
305 }
306 } {12 -22}
307 do_test check-4.8 {
308 execsql {
309 PRAGMA ignore_check_constraints=ON;
310 UPDATE t4 SET x=0, y=1;
311 SELECT * FROM t4;
312 }
313 } {0 1}
314 do_test check-4.9 {
315 catchsql {
316 PRAGMA ignore_check_constraints=OFF;
317 UPDATE t4 SET x=0, y=2;
318 }
319 } {1 {CHECK constraint failed: t4}}
320 ifcapable vacuum {
321 do_test check_4.10 {
322 catchsql {
323 VACUUM
324 }
325 } {0 {}}
326 }
328 do_test check-5.1 {
329 catchsql {
330 CREATE TABLE t5(x, y,
331 CHECK( x*y<:abc )
332 );
333 }
334 } {1 {parameters prohibited in CHECK constraints}}
335 do_test check-5.2 {
336 catchsql {
337 CREATE TABLE t5(x, y,
338 CHECK( x*y<? )
339 );
340 }
341 } {1 {parameters prohibited in CHECK constraints}}
343 ifcapable conflict {
345 do_test check-6.1 {
346 execsql {SELECT * FROM t1}
347 } {4 11.0}
348 do_test check-6.2 {
349 execsql {
350 UPDATE OR IGNORE t1 SET x=5;
351 SELECT * FROM t1;
352 }
353 } {4 11.0}
354 do_test check-6.3 {
355 execsql {
356 INSERT OR IGNORE INTO t1 VALUES(5,4.0);
357 SELECT * FROM t1;
358 }
359 } {4 11.0}
360 do_test check-6.4 {
361 execsql {
362 INSERT OR IGNORE INTO t1 VALUES(2,20.0);
363 SELECT * FROM t1;
364 }
365 } {4 11.0 2 20.0}
366 do_test check-6.5 {
367 catchsql {
368 UPDATE OR FAIL t1 SET x=7-x, y=y+1;
369 }
370 } {1 {CHECK constraint failed: t1}}
371 do_test check-6.6 {
372 execsql {
373 SELECT * FROM t1;
374 }
375 } {3 12.0 2 20.0}
376 do_test check-6.7 {
377 catchsql {
378 BEGIN;
379 INSERT INTO t1 VALUES(1,30.0);
380 INSERT OR ROLLBACK INTO t1 VALUES(8,40.0);
381 }
382 } {1 {CHECK constraint failed: t1}}
383 do_test check-6.8 {
384 catchsql {
385 COMMIT;
386 }
387 } {1 {cannot commit - no transaction is active}}
388 do_test check-6.9 {
389 execsql {
390 SELECT * FROM t1
391 }
392 } {3 12.0 2 20.0}
394 do_test check-6.11 {
395 execsql {SELECT * FROM t1}
396 } {3 12.0 2 20.0}
397 do_test check-6.12 {
398 catchsql {
399 REPLACE INTO t1 VALUES(6,7);
400 }
401 } {1 {CHECK constraint failed: t1}}
402 do_test check-6.13 {
403 execsql {SELECT * FROM t1}
404 } {3 12.0 2 20.0}
405 do_test check-6.14 {
406 catchsql {
407 INSERT OR IGNORE INTO t1 VALUES(6,7);
408 }
409 } {0 {}}
410 do_test check-6.15 {
411 execsql {SELECT * FROM t1}
412 } {3 12.0 2 20.0}
415 }
417 #--------------------------------------------------------------------------
418 # If a connection opens a database that contains a CHECK constraint that
419 # uses an unknown UDF, the schema should not be considered malformed.
420 # Attempting to modify the table should fail (since the CHECK constraint
421 # cannot be tested).
422 #
423 reset_db
424 proc myfunc {x} {expr $x < 10}
425 db func myfunc myfunc
427 do_execsql_test 7.1 { CREATE TABLE t6(a CHECK (myfunc(a))) }
428 do_execsql_test 7.2 { INSERT INTO t6 VALUES(9) }
429 do_catchsql_test 7.3 { INSERT INTO t6 VALUES(11) } \
430 {1 {CHECK constraint failed: t6}}
432 do_test 7.4 {
433 sqlite3 db2 test.db
434 execsql { SELECT * FROM t6 } db2
435 } {9}
437 do_test 7.5 {
438 catchsql { INSERT INTO t6 VALUES(8) } db2
439 } {1 {unknown function: myfunc()}}
441 do_test 7.6 {
442 catchsql { CREATE TABLE t7(a CHECK (myfunc(a))) } db2
443 } {1 {no such function: myfunc}}
445 do_test 7.7 {
446 db2 func myfunc myfunc
447 execsql { INSERT INTO t6 VALUES(8) } db2
448 } {}
450 do_test 7.8 {
451 db2 func myfunc myfunc
452 catchsql { INSERT INTO t6 VALUES(12) } db2
453 } {1 {CHECK constraint failed: t6}}
455 # 2013-08-02: Silently ignore database name qualifiers in CHECK constraints.
456 #
457 do_execsql_test 8.1 {
458 CREATE TABLE t810(a, CHECK( main.t810.a>0 ));
459 CREATE TABLE t811(b, CHECK( xyzzy.t811.b BETWEEN 5 AND 10 ));
460 } {}
462 finish_test