1 # 2001 September 27

     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.  The

    12 # focus of this file is testing the CREATE UNIQUE INDEX statement,

    13 # and primary keys, and the UNIQUE constraint on table columns

    14 #

    15 # $Id: unique.test,v 1.8 2005/06/24 03:53:06 drh Exp $

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 # Try to create a table with two primary keys.

    21 # (This is allowed in SQLite even that it is not valid SQL)

    22 #

    23 do_test unique-1.1 {

    24   catchsql {

    25     CREATE TABLE t1(

    26        a int PRIMARY KEY,

    27        b int PRIMARY KEY,

    28        c text

    29     );

    30   }

    31 } {1 {table "t1" has more than one primary key}}

    32 do_test unique-1.1b {

    33   catchsql {

    34     CREATE TABLE t1(

    35        a int PRIMARY KEY,

    36        b int UNIQUE,

    37        c text

    38     );

    39   }

    40 } {0 {}}

    41 do_test unique-1.2 {

    42   catchsql {

    43     INSERT INTO t1(a,b,c) VALUES(1,2,3)

    44   }

    45 } {0 {}}

    46 do_test unique-1.3 {

    47   catchsql {

    48     INSERT INTO t1(a,b,c) VALUES(1,3,4)

    49   }

    50 } {1 {column a is not unique}}

    51 do_test unique-1.4 {

    52   execsql {

    53     SELECT * FROM t1 ORDER BY a;

    54   }

    55 } {1 2 3}

    56 do_test unique-1.5 {

    57   catchsql {

    58     INSERT INTO t1(a,b,c) VALUES(3,2,4)

    59   }

    60 } {1 {column b is not unique}}

    61 do_test unique-1.6 {

    62   execsql {

    63     SELECT * FROM t1 ORDER BY a;

    64   }

    65 } {1 2 3}

    66 do_test unique-1.7 {

    67   catchsql {

    68     INSERT INTO t1(a,b,c) VALUES(3,4,5)

    69   }

    70 } {0 {}}

    71 do_test unique-1.8 {

    72   execsql {

    73     SELECT * FROM t1 ORDER BY a;

    74   }

    75 } {1 2 3 3 4 5}

    76 integrity_check unique-1.9

    77 

    78 do_test unique-2.0 {

    79   execsql {

    80     DROP TABLE t1;

    81     CREATE TABLE t2(a int, b int);

    82     INSERT INTO t2(a,b) VALUES(1,2);

    83     INSERT INTO t2(a,b) VALUES(3,4);

    84     SELECT * FROM t2 ORDER BY a;

    85   }

    86 } {1 2 3 4}

    87 do_test unique-2.1 {

    88   catchsql {

    89     CREATE UNIQUE INDEX i2 ON t2(a)

    90   }

    91 } {0 {}}

    92 do_test unique-2.2 {

    93   catchsql {

    94     SELECT * FROM t2 ORDER BY a

    95   }

    96 } {0 {1 2 3 4}}

    97 do_test unique-2.3 {

    98   catchsql {

    99     INSERT INTO t2 VALUES(1,5);

   100   }

   101 } {1 {column a is not unique}}

   102 do_test unique-2.4 {

   103   catchsql {

   104     SELECT * FROM t2 ORDER BY a

   105   }

   106 } {0 {1 2 3 4}}

   107 do_test unique-2.5 {

   108   catchsql {

   109     DROP INDEX i2;

   110     SELECT * FROM t2 ORDER BY a;

   111   }

   112 } {0 {1 2 3 4}}

   113 do_test unique-2.6 {

   114   catchsql {

   115     INSERT INTO t2 VALUES(1,5)

   116   }

   117 } {0 {}}

   118 do_test unique-2.7 {

   119   catchsql {

   120     SELECT * FROM t2 ORDER BY a, b;

   121   }

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

   123 do_test unique-2.8 {

   124   catchsql {

   125     CREATE UNIQUE INDEX i2 ON t2(a);

   126   }

   127 } {1 {indexed columns are not unique}}

   128 do_test unique-2.9 {

   129   catchsql {

   130     CREATE INDEX i2 ON t2(a);

   131   }

   132 } {0 {}}

   133 integrity_check unique-2.10

   134 

   135 # Test the UNIQUE keyword as used on two or more fields.

   136 #

   137 do_test unique-3.1 {

   138   catchsql {

   139     CREATE TABLE t3(

   140        a int,

   141        b int,

   142        c int,

   143        d int,

   144        unique(a,c,d)

   145      );

   146   }

   147 } {0 {}}

   148 do_test unique-3.2 {

   149   catchsql {

   150     INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4);

   151     SELECT * FROM t3 ORDER BY a,b,c,d;

   152   }

   153 } {0 {1 2 3 4}}

   154 do_test unique-3.3 {

   155   catchsql {

   156     INSERT INTO t3(a,b,c,d) VALUES(1,2,3,5);

   157     SELECT * FROM t3 ORDER BY a,b,c,d;

   158   }

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

   160 do_test unique-3.4 {

   161   catchsql {

   162     INSERT INTO t3(a,b,c,d) VALUES(1,4,3,5);

   163     SELECT * FROM t3 ORDER BY a,b,c,d;

   164   }

   165 } {1 {columns a, c, d are not unique}}

   166 integrity_check unique-3.5

   167 

   168 # Make sure NULLs are distinct as far as the UNIQUE tests are

   169 # concerned.

   170 #

   171 do_test unique-4.1 {

   172   execsql {

   173     CREATE TABLE t4(a UNIQUE, b, c, UNIQUE(b,c));

   174     INSERT INTO t4 VALUES(1,2,3);

   175     INSERT INTO t4 VALUES(NULL, 2, NULL);

   176     SELECT * FROM t4;

   177   }

   178 } {1 2 3 {} 2 {}}

   179 do_test unique-4.2 {

   180   catchsql {

   181     INSERT INTO t4 VALUES(NULL, 3, 4);

   182   }

   183 } {0 {}}

   184 do_test unique-4.3 {

   185   execsql {

   186     SELECT * FROM t4

   187   }

   188 } {1 2 3 {} 2 {} {} 3 4}

   189 do_test unique-4.4 {

   190   catchsql {

   191     INSERT INTO t4 VALUES(2, 2, NULL);

   192   }

   193 } {0 {}}

   194 do_test unique-4.5 {

   195   execsql {

   196     SELECT * FROM t4

   197   }

   198 } {1 2 3 {} 2 {} {} 3 4 2 2 {}}

   199 

   200 # Ticket #1301.  Any NULL value in a set of unique columns should

   201 # cause the rows to be distinct.

   202 #

   203 do_test unique-4.6 {

   204   catchsql {

   205     INSERT INTO t4 VALUES(NULL, 2, NULL);

   206   }

   207 } {0 {}}

   208 do_test unique-4.7 {

   209   execsql {SELECT * FROM t4}

   210 } {1 2 3 {} 2 {} {} 3 4 2 2 {} {} 2 {}}

   211 do_test unique-4.8 {

   212   catchsql {CREATE UNIQUE INDEX i4a ON t4(a,b)}

   213 } {0 {}}

   214 do_test unique-4.9 {

   215   catchsql {CREATE UNIQUE INDEX i4b ON t4(a,b,c)}

   216 } {0 {}}

   217 do_test unique-4.10 {

   218   catchsql {CREATE UNIQUE INDEX i4c ON t4(b)}

   219 } {1 {indexed columns are not unique}}

   220 integrity_check unique-4.99

   221 

   222 # Test the error message generation logic.  In particular, make sure we

   223 # do not overflow the static buffer used to generate the error message.

   224 #

   225 do_test unique-5.1 {

   226   execsql {

   227     CREATE TABLE t5(

   228       first_column_with_long_name,

   229       second_column_with_long_name,

   230       third_column_with_long_name,

   231       fourth_column_with_long_name,

   232       fifth_column_with_long_name,

   233       sixth_column_with_long_name,

   234       UNIQUE(

   235         first_column_with_long_name,

   236         second_column_with_long_name,

   237         third_column_with_long_name,

   238         fourth_column_with_long_name,

   239         fifth_column_with_long_name,

   240         sixth_column_with_long_name

   241       )

   242     );

   243     INSERT INTO t5 VALUES(1,2,3,4,5,6);

   244     SELECT * FROM t5;

   245   }

   246 } {1 2 3 4 5 6}

   247 do_test unique-5.2 {

   248   catchsql {

   249     INSERT INTO t5 VALUES(1,2,3,4,5,6);

   250   }

   251 } {1 {columns first_column_with_long_name, second_column_with_long_name, third_column_with_long_name, fourth_column_with_long_name, fifth_column_with_long_name, ... are not unique}}

   252 

   253 finish_test