| blob | cd2691a08409446720cb80410399981a762e27cd |
2 #pragma ident "%Z%%M% %I% %E% SMI"
4 # 2002 January 29
5 #
6 # The author disclaims copyright to this source code. In place of
7 # a legal notice, here is a blessing:
8 #
9 # May you do good and not evil.
10 # May you find forgiveness for yourself and forgive others.
11 # May you share freely, never taking more than you give.
12 #
13 #***********************************************************************
14 # This file implements regression tests for SQLite library.
15 #
16 # This file implements tests for the NOT NULL constraint.
17 #
18 # $Id: notnull.test,v 1.3 2003/01/29 18:46:54 drh Exp $
20 set testdir [file dirname $argv0]
21 source $testdir/tester.tcl
23 do_test notnull-1.0 {
24 execsql {
25 CREATE TABLE t1 (
26 a NOT NULL,
27 b NOT NULL DEFAULT 5,
28 c NOT NULL ON CONFLICT REPLACE DEFAULT 6,
29 d NOT NULL ON CONFLICT IGNORE DEFAULT 7,
30 e NOT NULL ON CONFLICT ABORT DEFAULT 8
31 );
32 SELECT * FROM t1;
33 }
34 } {}
35 do_test notnull-1.1 {
36 catchsql {
37 DELETE FROM t1;
38 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
39 SELECT * FROM t1 order by a;
40 }
41 } {0 {1 2 3 4 5}}
42 do_test notnull-1.2 {
43 catchsql {
44 DELETE FROM t1;
45 INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5);
46 SELECT * FROM t1 order by a;
47 }
48 } {1 {t1.a may not be NULL}}
49 do_test notnull-1.3 {
50 catchsql {
51 DELETE FROM t1;
52 INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5);
53 SELECT * FROM t1 order by a;
54 }
55 } {0 {}}
56 do_test notnull-1.4 {
57 catchsql {
58 DELETE FROM t1;
59 INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5);
60 SELECT * FROM t1 order by a;
61 }
62 } {1 {t1.a may not be NULL}}
63 do_test notnull-1.5 {
64 catchsql {
65 DELETE FROM t1;
66 INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5);
67 SELECT * FROM t1 order by a;
68 }
69 } {1 {t1.a may not be NULL}}
70 do_test notnull-1.6 {
71 catchsql {
72 DELETE FROM t1;
73 INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5);
74 SELECT * FROM t1 order by a;
75 }
76 } {0 {1 5 3 4 5}}
77 do_test notnull-1.7 {
78 catchsql {
79 DELETE FROM t1;
80 INSERT OR IGNORE INTO t1(a,c,d,e) VALUES(1,3,4,5);
81 SELECT * FROM t1 order by a;
82 }
83 } {0 {1 5 3 4 5}}
84 do_test notnull-1.8 {
85 catchsql {
86 DELETE FROM t1;
87 INSERT OR REPLACE INTO t1(a,c,d,e) VALUES(1,3,4,5);
88 SELECT * FROM t1 order by a;
89 }
90 } {0 {1 5 3 4 5}}
91 do_test notnull-1.9 {
92 catchsql {
93 DELETE FROM t1;
94 INSERT OR ABORT INTO t1(a,c,d,e) VALUES(1,3,4,5);
95 SELECT * FROM t1 order by a;
96 }
97 } {0 {1 5 3 4 5}}
98 do_test notnull-1.10 {
99 catchsql {
100 DELETE FROM t1;
101 INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
102 SELECT * FROM t1 order by a;
103 }
104 } {1 {t1.b may not be NULL}}
105 do_test notnull-1.11 {
106 catchsql {
107 DELETE FROM t1;
108 INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
109 SELECT * FROM t1 order by a;
110 }
111 } {0 {}}
112 do_test notnull-1.12 {
113 catchsql {
114 DELETE FROM t1;
115 INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
116 SELECT * FROM t1 order by a;
117 }
118 } {0 {1 5 3 4 5}}
119 do_test notnull-1.13 {
120 catchsql {
121 DELETE FROM t1;
122 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
123 SELECT * FROM t1 order by a;
124 }
125 } {0 {1 2 6 4 5}}
126 do_test notnull-1.14 {
127 catchsql {
128 DELETE FROM t1;
129 INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
130 SELECT * FROM t1 order by a;
131 }
132 } {0 {}}
133 do_test notnull-1.15 {
134 catchsql {
135 DELETE FROM t1;
136 INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
137 SELECT * FROM t1 order by a;
138 }
139 } {0 {1 2 6 4 5}}
140 do_test notnull-1.16 {
141 catchsql {
142 DELETE FROM t1;
143 INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
144 SELECT * FROM t1 order by a;
145 }
146 } {1 {t1.c may not be NULL}}
147 do_test notnull-1.17 {
148 catchsql {
149 DELETE FROM t1;
150 INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5);
151 SELECT * FROM t1 order by a;
152 }
153 } {1 {t1.d may not be NULL}}
154 do_test notnull-1.18 {
155 catchsql {
156 DELETE FROM t1;
157 INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5);
158 SELECT * FROM t1 order by a;
159 }
160 } {0 {1 2 3 7 5}}
161 do_test notnull-1.19 {
162 catchsql {
163 DELETE FROM t1;
164 INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4);
165 SELECT * FROM t1 order by a;
166 }
167 } {0 {1 2 3 4 8}}
168 do_test notnull-1.20 {
169 catchsql {
170 DELETE FROM t1;
171 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null);
172 SELECT * FROM t1 order by a;
173 }
174 } {1 {t1.e may not be NULL}}
175 do_test notnull-1.21 {
176 catchsql {
177 DELETE FROM t1;
178 INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5);
179 SELECT * FROM t1 order by a;
180 }
181 } {0 {5 5 3 2 1}}
183 do_test notnull-2.1 {
184 catchsql {
185 DELETE FROM t1;
186 INSERT INTO t1 VALUES(1,2,3,4,5);
187 UPDATE t1 SET a=null;
188 SELECT * FROM t1 ORDER BY a;
189 }
190 } {1 {t1.a may not be NULL}}
191 do_test notnull-2.2 {
192 catchsql {
193 DELETE FROM t1;
194 INSERT INTO t1 VALUES(1,2,3,4,5);
195 UPDATE OR REPLACE t1 SET a=null;
196 SELECT * FROM t1 ORDER BY a;
197 }
198 } {1 {t1.a may not be NULL}}
199 do_test notnull-2.3 {
200 catchsql {
201 DELETE FROM t1;
202 INSERT INTO t1 VALUES(1,2,3,4,5);
203 UPDATE OR IGNORE t1 SET a=null;
204 SELECT * FROM t1 ORDER BY a;
205 }
206 } {0 {1 2 3 4 5}}
207 do_test notnull-2.4 {
208 catchsql {
209 DELETE FROM t1;
210 INSERT INTO t1 VALUES(1,2,3,4,5);
211 UPDATE OR ABORT t1 SET a=null;
212 SELECT * FROM t1 ORDER BY a;
213 }
214 } {1 {t1.a may not be NULL}}
215 do_test notnull-2.5 {
216 catchsql {
217 DELETE FROM t1;
218 INSERT INTO t1 VALUES(1,2,3,4,5);
219 UPDATE t1 SET b=null;
220 SELECT * FROM t1 ORDER BY a;
221 }
222 } {1 {t1.b may not be NULL}}
223 do_test notnull-2.6 {
224 catchsql {
225 DELETE FROM t1;
226 INSERT INTO t1 VALUES(1,2,3,4,5);
227 UPDATE OR REPLACE t1 SET b=null, d=e, e=d;
228 SELECT * FROM t1 ORDER BY a;
229 }
230 } {0 {1 5 3 5 4}}
231 do_test notnull-2.7 {
232 catchsql {
233 DELETE FROM t1;
234 INSERT INTO t1 VALUES(1,2,3,4,5);
235 UPDATE OR IGNORE t1 SET b=null, d=e, e=d;
236 SELECT * FROM t1 ORDER BY a;
237 }
238 } {0 {1 2 3 4 5}}
239 do_test notnull-2.8 {
240 catchsql {
241 DELETE FROM t1;
242 INSERT INTO t1 VALUES(1,2,3,4,5);
243 UPDATE t1 SET c=null, d=e, e=d;
244 SELECT * FROM t1 ORDER BY a;
245 }
246 } {0 {1 2 6 5 4}}
247 do_test notnull-2.9 {
248 catchsql {
249 DELETE FROM t1;
250 INSERT INTO t1 VALUES(1,2,3,4,5);
251 UPDATE t1 SET d=null, a=b, b=a;
252 SELECT * FROM t1 ORDER BY a;
253 }
254 } {0 {1 2 3 4 5}}
255 do_test notnull-2.10 {
256 catchsql {
257 DELETE FROM t1;
258 INSERT INTO t1 VALUES(1,2,3,4,5);
259 UPDATE t1 SET e=null, a=b, b=a;
260 SELECT * FROM t1 ORDER BY a;
261 }
262 } {1 {t1.e may not be NULL}}
264 do_test notnull-3.0 {
265 execsql {
266 CREATE INDEX t1a ON t1(a);
267 CREATE INDEX t1b ON t1(b);
268 CREATE INDEX t1c ON t1(c);
269 CREATE INDEX t1d ON t1(d);
270 CREATE INDEX t1e ON t1(e);
271 CREATE INDEX t1abc ON t1(a,b,c);
272 }
273 } {}
274 do_test notnull-3.1 {
275 catchsql {
276 DELETE FROM t1;
277 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
278 SELECT * FROM t1 order by a;
279 }
280 } {0 {1 2 3 4 5}}
281 do_test notnull-3.2 {
282 catchsql {
283 DELETE FROM t1;
284 INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5);
285 SELECT * FROM t1 order by a;
286 }
287 } {1 {t1.a may not be NULL}}
288 do_test notnull-3.3 {
289 catchsql {
290 DELETE FROM t1;
291 INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5);
292 SELECT * FROM t1 order by a;
293 }
294 } {0 {}}
295 do_test notnull-3.4 {
296 catchsql {
297 DELETE FROM t1;
298 INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5);
299 SELECT * FROM t1 order by a;
300 }
301 } {1 {t1.a may not be NULL}}
302 do_test notnull-3.5 {
303 catchsql {
304 DELETE FROM t1;
305 INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5);
306 SELECT * FROM t1 order by a;
307 }
308 } {1 {t1.a may not be NULL}}
309 do_test notnull-3.6 {
310 catchsql {
311 DELETE FROM t1;
312 INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5);
313 SELECT * FROM t1 order by a;
314 }
315 } {0 {1 5 3 4 5}}
316 do_test notnull-3.7 {
317 catchsql {
318 DELETE FROM t1;
319 INSERT OR IGNORE INTO t1(a,c,d,e) VALUES(1,3,4,5);
320 SELECT * FROM t1 order by a;
321 }
322 } {0 {1 5 3 4 5}}
323 do_test notnull-3.8 {
324 catchsql {
325 DELETE FROM t1;
326 INSERT OR REPLACE INTO t1(a,c,d,e) VALUES(1,3,4,5);
327 SELECT * FROM t1 order by a;
328 }
329 } {0 {1 5 3 4 5}}
330 do_test notnull-3.9 {
331 catchsql {
332 DELETE FROM t1;
333 INSERT OR ABORT INTO t1(a,c,d,e) VALUES(1,3,4,5);
334 SELECT * FROM t1 order by a;
335 }
336 } {0 {1 5 3 4 5}}
337 do_test notnull-3.10 {
338 catchsql {
339 DELETE FROM t1;
340 INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
341 SELECT * FROM t1 order by a;
342 }
343 } {1 {t1.b may not be NULL}}
344 do_test notnull-3.11 {
345 catchsql {
346 DELETE FROM t1;
347 INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
348 SELECT * FROM t1 order by a;
349 }
350 } {0 {}}
351 do_test notnull-3.12 {
352 catchsql {
353 DELETE FROM t1;
354 INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
355 SELECT * FROM t1 order by a;
356 }
357 } {0 {1 5 3 4 5}}
358 do_test notnull-3.13 {
359 catchsql {
360 DELETE FROM t1;
361 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
362 SELECT * FROM t1 order by a;
363 }
364 } {0 {1 2 6 4 5}}
365 do_test notnull-3.14 {
366 catchsql {
367 DELETE FROM t1;
368 INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
369 SELECT * FROM t1 order by a;
370 }
371 } {0 {}}
372 do_test notnull-3.15 {
373 catchsql {
374 DELETE FROM t1;
375 INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
376 SELECT * FROM t1 order by a;
377 }
378 } {0 {1 2 6 4 5}}
379 do_test notnull-3.16 {
380 catchsql {
381 DELETE FROM t1;
382 INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
383 SELECT * FROM t1 order by a;
384 }
385 } {1 {t1.c may not be NULL}}
386 do_test notnull-3.17 {
387 catchsql {
388 DELETE FROM t1;
389 INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5);
390 SELECT * FROM t1 order by a;
391 }
392 } {1 {t1.d may not be NULL}}
393 do_test notnull-3.18 {
394 catchsql {
395 DELETE FROM t1;
396 INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5);
397 SELECT * FROM t1 order by a;
398 }
399 } {0 {1 2 3 7 5}}
400 do_test notnull-3.19 {
401 catchsql {
402 DELETE FROM t1;
403 INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4);
404 SELECT * FROM t1 order by a;
405 }
406 } {0 {1 2 3 4 8}}
407 do_test notnull-3.20 {
408 catchsql {
409 DELETE FROM t1;
410 INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null);
411 SELECT * FROM t1 order by a;
412 }
413 } {1 {t1.e may not be NULL}}
414 do_test notnull-3.21 {
415 catchsql {
416 DELETE FROM t1;
417 INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5);
418 SELECT * FROM t1 order by a;
419 }
420 } {0 {5 5 3 2 1}}
422 do_test notnull-4.1 {
423 catchsql {
424 DELETE FROM t1;
425 INSERT INTO t1 VALUES(1,2,3,4,5);
426 UPDATE t1 SET a=null;
427 SELECT * FROM t1 ORDER BY a;
428 }
429 } {1 {t1.a may not be NULL}}
430 do_test notnull-4.2 {
431 catchsql {
432 DELETE FROM t1;
433 INSERT INTO t1 VALUES(1,2,3,4,5);
434 UPDATE OR REPLACE t1 SET a=null;
435 SELECT * FROM t1 ORDER BY a;
436 }
437 } {1 {t1.a may not be NULL}}
438 do_test notnull-4.3 {
439 catchsql {
440 DELETE FROM t1;
441 INSERT INTO t1 VALUES(1,2,3,4,5);
442 UPDATE OR IGNORE t1 SET a=null;
443 SELECT * FROM t1 ORDER BY a;
444 }
445 } {0 {1 2 3 4 5}}
446 do_test notnull-4.4 {
447 catchsql {
448 DELETE FROM t1;
449 INSERT INTO t1 VALUES(1,2,3,4,5);
450 UPDATE OR ABORT t1 SET a=null;
451 SELECT * FROM t1 ORDER BY a;
452 }
453 } {1 {t1.a may not be NULL}}
454 do_test notnull-4.5 {
455 catchsql {
456 DELETE FROM t1;
457 INSERT INTO t1 VALUES(1,2,3,4,5);
458 UPDATE t1 SET b=null;
459 SELECT * FROM t1 ORDER BY a;
460 }
461 } {1 {t1.b may not be NULL}}
462 do_test notnull-4.6 {
463 catchsql {
464 DELETE FROM t1;
465 INSERT INTO t1 VALUES(1,2,3,4,5);
466 UPDATE OR REPLACE t1 SET b=null, d=e, e=d;
467 SELECT * FROM t1 ORDER BY a;
468 }
469 } {0 {1 5 3 5 4}}
470 do_test notnull-4.7 {
471 catchsql {
472 DELETE FROM t1;
473 INSERT INTO t1 VALUES(1,2,3,4,5);
474 UPDATE OR IGNORE t1 SET b=null, d=e, e=d;
475 SELECT * FROM t1 ORDER BY a;
476 }
477 } {0 {1 2 3 4 5}}
478 do_test notnull-4.8 {
479 catchsql {
480 DELETE FROM t1;
481 INSERT INTO t1 VALUES(1,2,3,4,5);
482 UPDATE t1 SET c=null, d=e, e=d;
483 SELECT * FROM t1 ORDER BY a;
484 }
485 } {0 {1 2 6 5 4}}
486 do_test notnull-4.9 {
487 catchsql {
488 DELETE FROM t1;
489 INSERT INTO t1 VALUES(1,2,3,4,5);
490 UPDATE t1 SET d=null, a=b, b=a;
491 SELECT * FROM t1 ORDER BY a;
492 }
493 } {0 {1 2 3 4 5}}
494 do_test notnull-4.10 {
495 catchsql {
496 DELETE FROM t1;
497 INSERT INTO t1 VALUES(1,2,3,4,5);
498 UPDATE t1 SET e=null, a=b, b=a;
499 SELECT * FROM t1 ORDER BY a;
500 }
501 } {1 {t1.e may not be NULL}}
503 finish_test