/ Check-in [3ae95293]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Added more tests of unique indices. (CVS 270)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3ae952933997c6422ec53b26391ba362c6e5c44a
User & Date: drh 2001-09-27 23:57:06
Context
2001-09-28
01:34
Put in the new LIBTOOL build system. (CVS 271) check-in: 00575d16 user: drh tags: trunk
2001-09-27
23:57
Added more tests of unique indices. (CVS 270) check-in: 3ae95293 user: drh tags: trunk
15:13
Alpha-3 (CVS 269) check-in: a70d4450 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/unique.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the CREATE UNIQUE INDEX statement,
    13     13   # and primary keys, and the UNIQUE constraint on table columns
    14     14   #
    15         -# $Id: unique.test,v 1.1 2001/09/27 15:11:55 drh Exp $
           15  +# $Id: unique.test,v 1.2 2001/09/27 23:57:06 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # Try to create a table with two primary keys.
    21     21   # (This is allowed in SQLite even that it is not valid SQL)
    22     22   #
................................................................................
    25     25       CREATE TABLE t1(
    26     26          a int PRIMARY KEY,
    27     27          b int PRIMARY KEY,
    28     28          c text
    29     29       );
    30     30     }
    31     31   } {0 {}}
           32  +do_test unique-1.2 {
           33  +  catchsql {
           34  +    INSERT INTO t1(a,b,c) VALUES(1,2,3)
           35  +  }
           36  +} {0 {}}
           37  +do_test unique-1.3 {
           38  +  catchsql {
           39  +    INSERT INTO t1(a,b,c) VALUES(1,3,4)
           40  +  }
           41  +} {1 {constraint failed}}
           42  +do_test unique-1.4 {
           43  +  execsql {
           44  +    SELECT * FROM t1 ORDER BY a;
           45  +  }
           46  +} {1 2 3}
           47  +do_test unique-1.5 {
           48  +  catchsql {
           49  +    INSERT INTO t1(a,b,c) VALUES(3,2,4)
           50  +  }
           51  +} {1 {constraint failed}}
           52  +do_test unique-1.6 {
           53  +  execsql {
           54  +    SELECT * FROM t1 ORDER BY a;
           55  +  }
           56  +} {1 2 3}
           57  +do_test unique-1.7 {
           58  +  catchsql {
           59  +    INSERT INTO t1(a,b,c) VALUES(3,4,5)
           60  +  }
           61  +} {0 {}}
           62  +do_test unique-1.8 {
           63  +  execsql {
           64  +    SELECT * FROM t1 ORDER BY a;
           65  +  }
           66  +} {1 2 3 3 4 5}
           67  +
           68  +do_test unique-2.0 {
           69  +  execsql {
           70  +    DROP TABLE t1;
           71  +    CREATE TABLE t2(a int, b int);
           72  +    INSERT INTO t2(a,b) VALUES(1,2);
           73  +    INSERT INTO t2(a,b) VALUES(3,4);
           74  +    SELECT * FROM t2 ORDER BY a;
           75  +  }
           76  +} {1 2 3 4}
           77  +do_test unique-2.1 {
           78  +  catchsql {
           79  +    CREATE UNIQUE INDEX i2 ON t2(a)
           80  +  }
           81  +} {0 {}}
           82  +do_test unique-2.2 {
           83  +  catchsql {
           84  +    SELECT * FROM t2 ORDER BY a
           85  +  }
           86  +} {0 {1 2 3 4}}
           87  +do_test unique-2.3 {
           88  +  catchsql {
           89  +    INSERT INTO t2 VALUES(1,5);
           90  +  }
           91  +} {1 {constraint failed}}
           92  +do_test unique-2.4 {
           93  +  catchsql {
           94  +    SELECT * FROM t2 ORDER BY a
           95  +  }
           96  +} {0 {1 2 3 4}}
           97  +do_test unique-2.5 {
           98  +  catchsql {
           99  +    DROP INDEX i2;
          100  +    SELECT * FROM t2 ORDER BY a;
          101  +  }
          102  +} {0 {1 2 3 4}}
          103  +do_test unique-2.6 {
          104  +  catchsql {
          105  +    INSERT INTO t2 VALUES(1,5)
          106  +  }
          107  +} {0 {}}
          108  +do_test unique-2.7 {
          109  +  catchsql {
          110  +    SELECT * FROM t2 ORDER BY a, b;
          111  +  }
          112  +} {0 {1 2 1 5 3 4}}
          113  +do_test unique-2.8 {
          114  +  catchsql {
          115  +    CREATE UNIQUE INDEX i2 ON t2(a);
          116  +  }
          117  +} {1 {constraint failed}}
          118  +do_test unique-2.9 {
          119  +  catchsql {
          120  +    CREATE INDEX i2 ON t2(a);
          121  +  }
          122  +} {0 {}}
          123  +
          124  +# Test the UNIQUE keyword as used on two or more fields.
          125  +#
          126  +do_test unique-3.1 {
          127  +  catchsql {
          128  +    CREATE TABLE t3(
          129  +       a int,
          130  +       b int,
          131  +       c int,
          132  +       d int,
          133  +       unique(a,c,d)
          134  +     );
          135  +  }
          136  +} {0 {}}
          137  +do_test unique-3.2 {
          138  +  catchsql {
          139  +    INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4);
          140  +    SELECT * FROM t3 ORDER BY a,b,c,d;
          141  +  }
          142  +} {0 {1 2 3 4}}
          143  +do_test unique-3.3 {
          144  +  catchsql {
          145  +    INSERT INTO t3(a,b,c,d) VALUES(1,2,3,5);
          146  +    SELECT * FROM t3 ORDER BY a,b,c,d;
          147  +  }
          148  +} {0 {1 2 3 4 1 2 3 5}}
          149  +do_test unique-3.4 {
          150  +  catchsql {
          151  +    INSERT INTO t3(a,b,c,d) VALUES(1,4,3,5);
          152  +    SELECT * FROM t3 ORDER BY a,b,c,d;
          153  +  }
          154  +} {1 {constraint failed}}
    32    155   
    33    156   finish_test