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 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 ifcapable !conflict {

    21   finish_test

    22   return

    23 }

    24 

    25 do_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 } {}

    37 do_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}}

    44 do_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 {t1.a may not be NULL}}

    51 do_test notnull-1.3 {

    52   catchsql {

    53     DELETE FROM t1;

    54     INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5);

    55     SELECT * FROM t1 order by a;

    56   }

    57 } {0 {}}

    58 do_test notnull-1.4 {

    59   catchsql {

    60     DELETE FROM t1;

    61     INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5);

    62     SELECT * FROM t1 order by a;

    63   }

    64 } {1 {t1.a may not be NULL}}

    65 do_test notnull-1.5 {

    66   catchsql {

    67     DELETE FROM t1;

    68     INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5);

    69     SELECT * FROM t1 order by a;

    70   }

    71 } {1 {t1.a may not be NULL}}

    72 do_test notnull-1.6 {

    73   catchsql {

    74     DELETE FROM t1;

    75     INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5);

    76     SELECT * FROM t1 order by a;

    77   }

    78 } {0 {1 5 3 4 5}}

    79 do_test notnull-1.7 {

    80   catchsql {

    81     DELETE FROM t1;

    82     INSERT OR IGNORE INTO t1(a,c,d,e) VALUES(1,3,4,5);

    83     SELECT * FROM t1 order by a;

    84   }

    85 } {0 {1 5 3 4 5}}

    86 do_test notnull-1.8 {

    87   catchsql {

    88     DELETE FROM t1;

    89     INSERT OR REPLACE INTO t1(a,c,d,e) VALUES(1,3,4,5);

    90     SELECT * FROM t1 order by a;

    91   }

    92 } {0 {1 5 3 4 5}}

    93 do_test notnull-1.9 {

    94   catchsql {

    95     DELETE FROM t1;

    96     INSERT OR ABORT INTO t1(a,c,d,e) VALUES(1,3,4,5);

    97     SELECT * FROM t1 order by a;

    98   }

    99 } {0 {1 5 3 4 5}}

   100 do_test notnull-1.10 {

   101   catchsql {

   102     DELETE FROM t1;

   103     INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);

   104     SELECT * FROM t1 order by a;

   105   }

   106 } {1 {t1.b may not be NULL}}

   107 do_test notnull-1.11 {

   108   catchsql {

   109     DELETE FROM t1;

   110     INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);

   111     SELECT * FROM t1 order by a;

   112   }

   113 } {0 {}}

   114 do_test notnull-1.12 {

   115   catchsql {

   116     DELETE FROM t1;

   117     INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);

   118     SELECT * FROM t1 order by a;

   119   }

   120 } {0 {1 5 3 4 5}}

   121 do_test notnull-1.13 {

   122   catchsql {

   123     DELETE FROM t1;

   124     INSERT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);

   125     SELECT * FROM t1 order by a;

   126   }

   127 } {0 {1 2 6 4 5}}

   128 do_test notnull-1.14 {

   129   catchsql {

   130     DELETE FROM t1;

   131     INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);

   132     SELECT * FROM t1 order by a;

   133   }

   134 } {0 {}}

   135 do_test notnull-1.15 {

   136   catchsql {

   137     DELETE FROM t1;

   138     INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);

   139     SELECT * FROM t1 order by a;

   140   }

   141 } {0 {1 2 6 4 5}}

   142 do_test notnull-1.16 {

   143   catchsql {

   144     DELETE FROM t1;

   145     INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);

   146     SELECT * FROM t1 order by a;

   147   }

   148 } {1 {t1.c may not be NULL}}

   149 do_test notnull-1.17 {

   150   catchsql {

   151     DELETE FROM t1;

   152     INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5);

   153     SELECT * FROM t1 order by a;

   154   }

   155 } {1 {t1.d may not be NULL}}

   156 do_test notnull-1.18 {

   157   catchsql {

   158     DELETE FROM t1;

   159     INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5);

   160     SELECT * FROM t1 order by a;

   161   }

   162 } {0 {1 2 3 7 5}}

   163 do_test notnull-1.19 {

   164   catchsql {

   165     DELETE FROM t1;

   166     INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4);

   167     SELECT * FROM t1 order by a;

   168   }

   169 } {0 {1 2 3 4 8}}

   170 do_test notnull-1.20 {

   171   catchsql {

   172     DELETE FROM t1;

   173     INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null);

   174     SELECT * FROM t1 order by a;

   175   }

   176 } {1 {t1.e may not be NULL}}

   177 do_test notnull-1.21 {

   178   catchsql {

   179     DELETE FROM t1;

   180     INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5);

   181     SELECT * FROM t1 order by a;

   182   }

   183 } {0 {5 5 3 2 1}}

   184 

   185 do_test notnull-2.1 {

   186   catchsql {

   187     DELETE FROM t1;

   188     INSERT INTO t1 VALUES(1,2,3,4,5);

   189     UPDATE t1 SET a=null;

   190     SELECT * FROM t1 ORDER BY a;

   191   }

   192 } {1 {t1.a may not be NULL}}

   193 do_test notnull-2.2 {

   194   catchsql {

   195     DELETE FROM t1;

   196     INSERT INTO t1 VALUES(1,2,3,4,5);

   197     UPDATE OR REPLACE t1 SET a=null;

   198     SELECT * FROM t1 ORDER BY a;

   199   }

   200 } {1 {t1.a may not be NULL}}

   201 do_test notnull-2.3 {

   202   catchsql {

   203     DELETE FROM t1;

   204     INSERT INTO t1 VALUES(1,2,3,4,5);

   205     UPDATE OR IGNORE t1 SET a=null;

   206     SELECT * FROM t1 ORDER BY a;

   207   }

   208 } {0 {1 2 3 4 5}}

   209 do_test notnull-2.4 {

   210   catchsql {

   211     DELETE FROM t1;

   212     INSERT INTO t1 VALUES(1,2,3,4,5);

   213     UPDATE OR ABORT t1 SET a=null;

   214     SELECT * FROM t1 ORDER BY a;

   215   }

   216 } {1 {t1.a may not be NULL}}

   217 do_test notnull-2.5 {

   218   catchsql {

   219     DELETE FROM t1;

   220     INSERT INTO t1 VALUES(1,2,3,4,5);

   221     UPDATE t1 SET b=null;

   222     SELECT * FROM t1 ORDER BY a;

   223   }

   224 } {1 {t1.b may not be NULL}}

   225 do_test notnull-2.6 {

   226   catchsql {

   227     DELETE FROM t1;

   228     INSERT INTO t1 VALUES(1,2,3,4,5);

   229     UPDATE OR REPLACE t1 SET b=null, d=e, e=d;

   230     SELECT * FROM t1 ORDER BY a;

   231   }

   232 } {0 {1 5 3 5 4}}

   233 do_test notnull-2.7 {

   234   catchsql {

   235     DELETE FROM t1;

   236     INSERT INTO t1 VALUES(1,2,3,4,5);

   237     UPDATE OR IGNORE t1 SET b=null, d=e, e=d;

   238     SELECT * FROM t1 ORDER BY a;

   239   }

   240 } {0 {1 2 3 4 5}}

   241 do_test notnull-2.8 {

   242   catchsql {

   243     DELETE FROM t1;

   244     INSERT INTO t1 VALUES(1,2,3,4,5);

   245     UPDATE t1 SET c=null, d=e, e=d;

   246     SELECT * FROM t1 ORDER BY a;

   247   }

   248 } {0 {1 2 6 5 4}}

   249 do_test notnull-2.9 {

   250   catchsql {

   251     DELETE FROM t1;

   252     INSERT INTO t1 VALUES(1,2,3,4,5);

   253     UPDATE t1 SET d=null, a=b, b=a;

   254     SELECT * FROM t1 ORDER BY a;

   255   }

   256 } {0 {1 2 3 4 5}}

   257 do_test notnull-2.10 {

   258   catchsql {

   259     DELETE FROM t1;

   260     INSERT INTO t1 VALUES(1,2,3,4,5);

   261     UPDATE t1 SET e=null, a=b, b=a;

   262     SELECT * FROM t1 ORDER BY a;

   263   }

   264 } {1 {t1.e may not be NULL}}

   265 

   266 do_test notnull-3.0 {

   267   execsql {

   268     CREATE INDEX t1a ON t1(a);

   269     CREATE INDEX t1b ON t1(b);

   270     CREATE INDEX t1c ON t1(c);

   271     CREATE INDEX t1d ON t1(d);

   272     CREATE INDEX t1e ON t1(e);

   273     CREATE INDEX t1abc ON t1(a,b,c);

   274   }

   275 } {}

   276 do_test notnull-3.1 {

   277   catchsql {

   278     DELETE FROM t1;

   279     INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);

   280     SELECT * FROM t1 order by a;

   281   }

   282 } {0 {1 2 3 4 5}}

   283 do_test notnull-3.2 {

   284   catchsql {

   285     DELETE FROM t1;

   286     INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5);

   287     SELECT * FROM t1 order by a;

   288   }

   289 } {1 {t1.a may not be NULL}}

   290 do_test notnull-3.3 {

   291   catchsql {

   292     DELETE FROM t1;

   293     INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5);

   294     SELECT * FROM t1 order by a;

   295   }

   296 } {0 {}}

   297 do_test notnull-3.4 {

   298   catchsql {

   299     DELETE FROM t1;

   300     INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5);

   301     SELECT * FROM t1 order by a;

   302   }

   303 } {1 {t1.a may not be NULL}}

   304 do_test notnull-3.5 {

   305   catchsql {

   306     DELETE FROM t1;

   307     INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5);

   308     SELECT * FROM t1 order by a;

   309   }

   310 } {1 {t1.a may not be NULL}}

   311 do_test notnull-3.6 {

   312   catchsql {

   313     DELETE FROM t1;

   314     INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5);

   315     SELECT * FROM t1 order by a;

   316   }

   317 } {0 {1 5 3 4 5}}

   318 do_test notnull-3.7 {

   319   catchsql {

   320     DELETE FROM t1;

   321     INSERT OR IGNORE INTO t1(a,c,d,e) VALUES(1,3,4,5);

   322     SELECT * FROM t1 order by a;

   323   }

   324 } {0 {1 5 3 4 5}}

   325 do_test notnull-3.8 {

   326   catchsql {

   327     DELETE FROM t1;

   328     INSERT OR REPLACE INTO t1(a,c,d,e) VALUES(1,3,4,5);

   329     SELECT * FROM t1 order by a;

   330   }

   331 } {0 {1 5 3 4 5}}

   332 do_test notnull-3.9 {

   333   catchsql {

   334     DELETE FROM t1;

   335     INSERT OR ABORT INTO t1(a,c,d,e) VALUES(1,3,4,5);

   336     SELECT * FROM t1 order by a;

   337   }

   338 } {0 {1 5 3 4 5}}

   339 do_test notnull-3.10 {

   340   catchsql {

   341     DELETE FROM t1;

   342     INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);

   343     SELECT * FROM t1 order by a;

   344   }

   345 } {1 {t1.b may not be NULL}}

   346 do_test notnull-3.11 {

   347   catchsql {

   348     DELETE FROM t1;

   349     INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);

   350     SELECT * FROM t1 order by a;

   351   }

   352 } {0 {}}

   353 do_test notnull-3.12 {

   354   catchsql {

   355     DELETE FROM t1;

   356     INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);

   357     SELECT * FROM t1 order by a;

   358   }

   359 } {0 {1 5 3 4 5}}

   360 do_test notnull-3.13 {

   361   catchsql {

   362     DELETE FROM t1;

   363     INSERT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);

   364     SELECT * FROM t1 order by a;

   365   }

   366 } {0 {1 2 6 4 5}}

   367 do_test notnull-3.14 {

   368   catchsql {

   369     DELETE FROM t1;

   370     INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);

   371     SELECT * FROM t1 order by a;

   372   }

   373 } {0 {}}

   374 do_test notnull-3.15 {

   375   catchsql {

   376     DELETE FROM t1;

   377     INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);

   378     SELECT * FROM t1 order by a;

   379   }

   380 } {0 {1 2 6 4 5}}

   381 do_test notnull-3.16 {

   382   catchsql {

   383     DELETE FROM t1;

   384     INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);

   385     SELECT * FROM t1 order by a;

   386   }

   387 } {1 {t1.c may not be NULL}}

   388 do_test notnull-3.17 {

   389   catchsql {

   390     DELETE FROM t1;

   391     INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5);

   392     SELECT * FROM t1 order by a;

   393   }

   394 } {1 {t1.d may not be NULL}}

   395 do_test notnull-3.18 {

   396   catchsql {

   397     DELETE FROM t1;

   398     INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5);

   399     SELECT * FROM t1 order by a;

   400   }

   401 } {0 {1 2 3 7 5}}

   402 do_test notnull-3.19 {

   403   catchsql {

   404     DELETE FROM t1;

   405     INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4);

   406     SELECT * FROM t1 order by a;

   407   }

   408 } {0 {1 2 3 4 8}}

   409 do_test notnull-3.20 {

   410   catchsql {

   411     DELETE FROM t1;

   412     INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null);

   413     SELECT * FROM t1 order by a;

   414   }

   415 } {1 {t1.e may not be NULL}}

   416 do_test notnull-3.21 {

   417   catchsql {

   418     DELETE FROM t1;

   419     INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5);

   420     SELECT * FROM t1 order by a;

   421   }

   422 } {0 {5 5 3 2 1}}

   423 

   424 do_test notnull-4.1 {

   425   catchsql {

   426     DELETE FROM t1;

   427     INSERT INTO t1 VALUES(1,2,3,4,5);

   428     UPDATE t1 SET a=null;

   429     SELECT * FROM t1 ORDER BY a;

   430   }

   431 } {1 {t1.a may not be NULL}}

   432 do_test notnull-4.2 {

   433   catchsql {

   434     DELETE FROM t1;

   435     INSERT INTO t1 VALUES(1,2,3,4,5);

   436     UPDATE OR REPLACE t1 SET a=null;

   437     SELECT * FROM t1 ORDER BY a;

   438   }

   439 } {1 {t1.a may not be NULL}}

   440 do_test notnull-4.3 {

   441   catchsql {

   442     DELETE FROM t1;

   443     INSERT INTO t1 VALUES(1,2,3,4,5);

   444     UPDATE OR IGNORE t1 SET a=null;

   445     SELECT * FROM t1 ORDER BY a;

   446   }

   447 } {0 {1 2 3 4 5}}

   448 do_test notnull-4.4 {

   449   catchsql {

   450     DELETE FROM t1;

   451     INSERT INTO t1 VALUES(1,2,3,4,5);

   452     UPDATE OR ABORT t1 SET a=null;

   453     SELECT * FROM t1 ORDER BY a;

   454   }

   455 } {1 {t1.a may not be NULL}}

   456 do_test notnull-4.5 {

   457   catchsql {

   458     DELETE FROM t1;

   459     INSERT INTO t1 VALUES(1,2,3,4,5);

   460     UPDATE t1 SET b=null;

   461     SELECT * FROM t1 ORDER BY a;

   462   }

   463 } {1 {t1.b may not be NULL}}

   464 do_test notnull-4.6 {

   465   catchsql {

   466     DELETE FROM t1;

   467     INSERT INTO t1 VALUES(1,2,3,4,5);

   468     UPDATE OR REPLACE t1 SET b=null, d=e, e=d;

   469     SELECT * FROM t1 ORDER BY a;

   470   }

   471 } {0 {1 5 3 5 4}}

   472 do_test notnull-4.7 {

   473   catchsql {

   474     DELETE FROM t1;

   475     INSERT INTO t1 VALUES(1,2,3,4,5);

   476     UPDATE OR IGNORE t1 SET b=null, d=e, e=d;

   477     SELECT * FROM t1 ORDER BY a;

   478   }

   479 } {0 {1 2 3 4 5}}

   480 do_test notnull-4.8 {

   481   catchsql {

   482     DELETE FROM t1;

   483     INSERT INTO t1 VALUES(1,2,3,4,5);

   484     UPDATE t1 SET c=null, d=e, e=d;

   485     SELECT * FROM t1 ORDER BY a;

   486   }

   487 } {0 {1 2 6 5 4}}

   488 do_test notnull-4.9 {

   489   catchsql {

   490     DELETE FROM t1;

   491     INSERT INTO t1 VALUES(1,2,3,4,5);

   492     UPDATE t1 SET d=null, a=b, b=a;

   493     SELECT * FROM t1 ORDER BY a;

   494   }

   495 } {0 {1 2 3 4 5}}

   496 do_test notnull-4.10 {

   497   catchsql {

   498     DELETE FROM t1;

   499     INSERT INTO t1 VALUES(1,2,3,4,5);

   500     UPDATE t1 SET e=null, a=b, b=a;

   501     SELECT * FROM t1 ORDER BY a;

   502   }

   503 } {1 {t1.e may not be NULL}}

   504 

   505 finish_test