| blob | 9b6977bf71fff93911f981b20e69a4ee510f725e |
2 #pragma ident "%Z%%M% %I% %E% SMI"
4 # 2001 September 27
5 #
6 # The author disclaims copyright to this source code. In place of
7 # a legal notice, here is a blessing:
8 #
9 # May you do good and not evil.
10 # May you find forgiveness for yourself and forgive others.
11 # May you share freely, never taking more than you give.
12 #
13 #***********************************************************************
14 # This file implements regression tests for SQLite library. The
15 # focus of this file is testing the CREATE UNIQUE INDEX statement,
16 # and primary keys, and the UNIQUE constraint on table columns
17 #
18 # $Id: unique.test,v 1.7 2003/08/05 13:13:39 drh Exp $
20 set testdir [file dirname $argv0]
21 source $testdir/tester.tcl
23 # Try to create a table with two primary keys.
24 # (This is allowed in SQLite even that it is not valid SQL)
25 #
26 do_test unique-1.1 {
27 catchsql {
28 CREATE TABLE t1(
29 a int PRIMARY KEY,
30 b int PRIMARY KEY,
31 c text
32 );
33 }
34 } {1 {table "t1" has more than one primary key}}
35 do_test unique-1.1b {
36 catchsql {
37 CREATE TABLE t1(
38 a int PRIMARY KEY,
39 b int UNIQUE,
40 c text
41 );
42 }
43 } {0 {}}
44 do_test unique-1.2 {
45 catchsql {
46 INSERT INTO t1(a,b,c) VALUES(1,2,3)
47 }
48 } {0 {}}
49 do_test unique-1.3 {
50 catchsql {
51 INSERT INTO t1(a,b,c) VALUES(1,3,4)
52 }
53 } {1 {column a is not unique}}
54 do_test unique-1.4 {
55 execsql {
56 SELECT * FROM t1 ORDER BY a;
57 }
58 } {1 2 3}
59 do_test unique-1.5 {
60 catchsql {
61 INSERT INTO t1(a,b,c) VALUES(3,2,4)
62 }
63 } {1 {column b is not unique}}
64 do_test unique-1.6 {
65 execsql {
66 SELECT * FROM t1 ORDER BY a;
67 }
68 } {1 2 3}
69 do_test unique-1.7 {
70 catchsql {
71 INSERT INTO t1(a,b,c) VALUES(3,4,5)
72 }
73 } {0 {}}
74 do_test unique-1.8 {
75 execsql {
76 SELECT * FROM t1 ORDER BY a;
77 }
78 } {1 2 3 3 4 5}
79 integrity_check unique-1.9
81 do_test unique-2.0 {
82 execsql {
83 DROP TABLE t1;
84 CREATE TABLE t2(a int, b int);
85 INSERT INTO t2(a,b) VALUES(1,2);
86 INSERT INTO t2(a,b) VALUES(3,4);
87 SELECT * FROM t2 ORDER BY a;
88 }
89 } {1 2 3 4}
90 do_test unique-2.1 {
91 catchsql {
92 CREATE UNIQUE INDEX i2 ON t2(a)
93 }
94 } {0 {}}
95 do_test unique-2.2 {
96 catchsql {
97 SELECT * FROM t2 ORDER BY a
98 }
99 } {0 {1 2 3 4}}
100 do_test unique-2.3 {
101 catchsql {
102 INSERT INTO t2 VALUES(1,5);
103 }
104 } {1 {column a is not unique}}
105 do_test unique-2.4 {
106 catchsql {
107 SELECT * FROM t2 ORDER BY a
108 }
109 } {0 {1 2 3 4}}
110 do_test unique-2.5 {
111 catchsql {
112 DROP INDEX i2;
113 SELECT * FROM t2 ORDER BY a;
114 }
115 } {0 {1 2 3 4}}
116 do_test unique-2.6 {
117 catchsql {
118 INSERT INTO t2 VALUES(1,5)
119 }
120 } {0 {}}
121 do_test unique-2.7 {
122 catchsql {
123 SELECT * FROM t2 ORDER BY a, b;
124 }
125 } {0 {1 2 1 5 3 4}}
126 do_test unique-2.8 {
127 catchsql {
128 CREATE UNIQUE INDEX i2 ON t2(a);
129 }
130 } {1 {indexed columns are not unique}}
131 do_test unique-2.9 {
132 catchsql {
133 CREATE INDEX i2 ON t2(a);
134 }
135 } {0 {}}
136 integrity_check unique-2.10
138 # Test the UNIQUE keyword as used on two or more fields.
139 #
140 do_test unique-3.1 {
141 catchsql {
142 CREATE TABLE t3(
143 a int,
144 b int,
145 c int,
146 d int,
147 unique(a,c,d)
148 );
149 }
150 } {0 {}}
151 do_test unique-3.2 {
152 catchsql {
153 INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4);
154 SELECT * FROM t3 ORDER BY a,b,c,d;
155 }
156 } {0 {1 2 3 4}}
157 do_test unique-3.3 {
158 catchsql {
159 INSERT INTO t3(a,b,c,d) VALUES(1,2,3,5);
160 SELECT * FROM t3 ORDER BY a,b,c,d;
161 }
162 } {0 {1 2 3 4 1 2 3 5}}
163 do_test unique-3.4 {
164 catchsql {
165 INSERT INTO t3(a,b,c,d) VALUES(1,4,3,5);
166 SELECT * FROM t3 ORDER BY a,b,c,d;
167 }
168 } {1 {columns a, c, d are not unique}}
169 integrity_check unique-3.5
171 # Make sure NULLs are distinct as far as the UNIQUE tests are
172 # concerned.
173 #
174 do_test unique-4.1 {
175 execsql {
176 CREATE TABLE t4(a UNIQUE, b, c, UNIQUE(b,c));
177 INSERT INTO t4 VALUES(1,2,3);
178 INSERT INTO t4 VALUES(NULL, 2, NULL);
179 SELECT * FROM t4;
180 }
181 } {1 2 3 {} 2 {}}
182 do_test unique-4.2 {
183 catchsql {
184 INSERT INTO t4 VALUES(NULL, 3, 4);
185 }
186 } {0 {}}
187 do_test unique-4.3 {
188 execsql {
189 SELECT * FROM t4
190 }
191 } {1 2 3 {} 2 {} {} 3 4}
192 do_test unique-4.4 {
193 catchsql {
194 INSERT INTO t4 VALUES(2, 2, NULL);
195 }
196 } {0 {}}
197 do_test unique-4.5 {
198 execsql {
199 SELECT * FROM t4
200 }
201 } {1 2 3 {} 2 {} {} 3 4 2 2 {}}
202 integrity_check unique-4.6
204 # Test the error message generation logic. In particular, make sure we
205 # do not overflow the static buffer used to generate the error message.
206 #
207 do_test unique-5.1 {
208 execsql {
209 CREATE TABLE t5(
210 first_column_with_long_name,
211 second_column_with_long_name,
212 third_column_with_long_name,
213 fourth_column_with_long_name,
214 fifth_column_with_long_name,
215 sixth_column_with_long_name,
216 UNIQUE(
217 first_column_with_long_name,
218 second_column_with_long_name,
219 third_column_with_long_name,
220 fourth_column_with_long_name,
221 fifth_column_with_long_name,
222 sixth_column_with_long_name
223 )
224 );
225 INSERT INTO t5 VALUES(1,2,3,4,5,6);
226 SELECT * FROM t5;
227 }
228 } {1 2 3 4 5 6}
229 do_test unique-5.2 {
230 catchsql {
231 INSERT INTO t5 VALUES(1,2,3,4,5,6);
232 }
233 } {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}}
235 finish_test