SQLite

Check-in [3ae9529339]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3ae952933997c6422ec53b26391ba362c6e5c44a
User & Date: drh 2001-09-27 23:57:06.000
Context
2001-09-28
01:34
Put in the new LIBTOOL build system. (CVS 271) (check-in: 00575d167a user: drh tags: trunk)
2001-09-27
23:57
Added more tests of unique indices. (CVS 270) (check-in: 3ae9529339 user: drh tags: trunk)
15:13
Alpha-3 (CVS 269) (check-in: a70d445070 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/unique.test.
︙︙
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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)
#
do_test unique-1.1 {
  catchsql {
    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
23
24
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)
#
do_test unique-1.1 {
  catchsql {
    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