/ Check-in [3ae95293]
Login
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 Unified Diffs Ignore Whitespace Patch

Changes to test/unique.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
25
26
27
28
29
30
31



32
























































































































33
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE UNIQUE INDEX statement,
# and primary keys, and the UNIQUE constraint on table columns
#
# $Id: unique.test,v 1.1 2001/09/27 15:11:55 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Try to create a table with two primary keys.
# (This is allowed in SQLite even that it is not valid SQL)
#
................................................................................
    CREATE TABLE t1(
       a int PRIMARY KEY,
       b int PRIMARY KEY,
       c text
    );
  }
} {0 {}}




























































































































finish_test







|







 







>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE UNIQUE INDEX statement,
# and primary keys, and the UNIQUE constraint on table columns
#
# $Id: unique.test,v 1.2 2001/09/27 23:57:06 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Try to create a table with two primary keys.
# (This is allowed in SQLite even that it is not valid SQL)
#
................................................................................
    CREATE TABLE t1(
       a int PRIMARY KEY,
       b int PRIMARY KEY,
       c text
    );
  }
} {0 {}}
do_test unique-1.2 {
  catchsql {
    INSERT INTO t1(a,b,c) VALUES(1,2,3)
  }
} {0 {}}
do_test unique-1.3 {
  catchsql {
    INSERT INTO t1(a,b,c) VALUES(1,3,4)
  }
} {1 {constraint failed}}
do_test unique-1.4 {
  execsql {
    SELECT * FROM t1 ORDER BY a;
  }
} {1 2 3}
do_test unique-1.5 {
  catchsql {
    INSERT INTO t1(a,b,c) VALUES(3,2,4)
  }
} {1 {constraint failed}}
do_test unique-1.6 {
  execsql {
    SELECT * FROM t1 ORDER BY a;
  }
} {1 2 3}
do_test unique-1.7 {
  catchsql {
    INSERT INTO t1(a,b,c) VALUES(3,4,5)
  }
} {0 {}}
do_test unique-1.8 {
  execsql {
    SELECT * FROM t1 ORDER BY a;
  }
} {1 2 3 3 4 5}

do_test unique-2.0 {
  execsql {
    DROP TABLE t1;
    CREATE TABLE t2(a int, b int);
    INSERT INTO t2(a,b) VALUES(1,2);
    INSERT INTO t2(a,b) VALUES(3,4);
    SELECT * FROM t2 ORDER BY a;
  }
} {1 2 3 4}
do_test unique-2.1 {
  catchsql {
    CREATE UNIQUE INDEX i2 ON t2(a)
  }
} {0 {}}
do_test unique-2.2 {
  catchsql {
    SELECT * FROM t2 ORDER BY a
  }
} {0 {1 2 3 4}}
do_test unique-2.3 {
  catchsql {
    INSERT INTO t2 VALUES(1,5);
  }
} {1 {constraint failed}}
do_test unique-2.4 {
  catchsql {
    SELECT * FROM t2 ORDER BY a
  }
} {0 {1 2 3 4}}
do_test unique-2.5 {
  catchsql {
    DROP INDEX i2;
    SELECT * FROM t2 ORDER BY a;
  }
} {0 {1 2 3 4}}
do_test unique-2.6 {
  catchsql {
    INSERT INTO t2 VALUES(1,5)
  }
} {0 {}}
do_test unique-2.7 {
  catchsql {
    SELECT * FROM t2 ORDER BY a, b;
  }
} {0 {1 2 1 5 3 4}}
do_test unique-2.8 {
  catchsql {
    CREATE UNIQUE INDEX i2 ON t2(a);
  }
} {1 {constraint failed}}
do_test unique-2.9 {
  catchsql {
    CREATE INDEX i2 ON t2(a);
  }
} {0 {}}

# Test the UNIQUE keyword as used on two or more fields.
#
do_test unique-3.1 {
  catchsql {
    CREATE TABLE t3(
       a int,
       b int,
       c int,
       d int,
       unique(a,c,d)
     );
  }
} {0 {}}
do_test unique-3.2 {
  catchsql {
    INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4);
    SELECT * FROM t3 ORDER BY a,b,c,d;
  }
} {0 {1 2 3 4}}
do_test unique-3.3 {
  catchsql {
    INSERT INTO t3(a,b,c,d) VALUES(1,2,3,5);
    SELECT * FROM t3 ORDER BY a,b,c,d;
  }
} {0 {1 2 3 4 1 2 3 5}}
do_test unique-3.4 {
  catchsql {
    INSERT INTO t3(a,b,c,d) VALUES(1,4,3,5);
    SELECT * FROM t3 ORDER BY a,b,c,d;
  }
} {1 {constraint failed}}

finish_test