Annotation of embedaddon/sqlite3/test/unique.test, revision 1.1.1.1
1.1 misho 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.9 2009/05/02 15:46:47 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, sixth_column_with_long_name are not unique}}
252:
253: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to unique.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test