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