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