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