xref: /sqlite-3.40.0/test/unique.test (revision 45f31be8) 
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
17set testdir [file dirname $argv0]
18source $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#
23do_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}}
32do_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 {}}
41do_test unique-1.2 {
42  catchsql {
43    INSERT INTO t1(a,b,c) VALUES(1,2,3)
44  }
45} {0 {}}
46do_test unique-1.3 {
47  catchsql {
48    INSERT INTO t1(a,b,c) VALUES(1,3,4)
49  }
50} {1 {UNIQUE constraint failed: t1.a}}
51verify_ex_errcode unique-1.3b SQLITE_CONSTRAINT_PRIMARYKEY
52do_test unique-1.4 {
53  execsql {
54    SELECT * FROM t1 ORDER BY a;
55  }
56} {1 2 3}
57do_test unique-1.5 {
58  catchsql {
59    INSERT INTO t1(a,b,c) VALUES(3,2,4)
60  }
61} {1 {UNIQUE constraint failed: t1.b}}
62verify_ex_errcode unique-1.5b SQLITE_CONSTRAINT_UNIQUE
63do_test unique-1.6 {
64  execsql {
65    SELECT * FROM t1 ORDER BY a;
66  }
67} {1 2 3}
68do_test unique-1.7 {
69  catchsql {
70    INSERT INTO t1(a,b,c) VALUES(3,4,5)
71  }
72} {0 {}}
73do_test unique-1.8 {
74  execsql {
75    SELECT * FROM t1 ORDER BY a;
76  }
77} {1 2 3 3 4 5}
78integrity_check unique-1.9
79
80do_test unique-2.0 {
81  execsql {
82    DROP TABLE t1;
83    CREATE TABLE t2(a int, b int);
84    INSERT INTO t2(a,b) VALUES(1,2);
85    INSERT INTO t2(a,b) VALUES(3,4);
86    SELECT * FROM t2 ORDER BY a;
87  }
88} {1 2 3 4}
89do_test unique-2.1 {
90  catchsql {
91    CREATE UNIQUE INDEX i2 ON t2(a)
92  }
93} {0 {}}
94do_test unique-2.2 {
95  catchsql {
96    SELECT * FROM t2 ORDER BY a
97  }
98} {0 {1 2 3 4}}
99do_test unique-2.3 {
100  catchsql {
101    INSERT INTO t2 VALUES(1,5);
102  }
103} {1 {UNIQUE constraint failed: t2.a}}
104verify_ex_errcode unique-2.3b SQLITE_CONSTRAINT_UNIQUE
105do_test unique-2.4 {
106  catchsql {
107    SELECT * FROM t2 ORDER BY a
108  }
109} {0 {1 2 3 4}}
110do_test unique-2.5 {
111  catchsql {
112    DROP INDEX i2;
113    SELECT * FROM t2 ORDER BY a;
114  }
115} {0 {1 2 3 4}}
116do_test unique-2.6 {
117  catchsql {
118    INSERT INTO t2 VALUES(1,5)
119  }
120} {0 {}}
121do_test unique-2.7 {
122  catchsql {
123    SELECT * FROM t2 ORDER BY a, b;
124  }
125} {0 {1 2 1 5 3 4}}
126do_test unique-2.8 {
127  catchsql {
128    CREATE UNIQUE INDEX i2 ON t2(a);
129  }
130} {1 {UNIQUE constraint failed: t2.a}}
131verify_ex_errcode unique-2.8b SQLITE_CONSTRAINT_UNIQUE
132do_test unique-2.9 {
133  catchsql {
134    CREATE INDEX i2 ON t2(a);
135  }
136} {0 {}}
137integrity_check unique-2.10
138
139# Test the UNIQUE keyword as used on two or more fields.
140#
141do_test unique-3.1 {
142  catchsql {
143    CREATE TABLE t3(
144       a int,
145       b int,
146       c int,
147       d int,
148       unique(a,c,d)
149     );
150  }
151} {0 {}}
152do_test unique-3.2 {
153  catchsql {
154    INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4);
155    SELECT * FROM t3 ORDER BY a,b,c,d;
156  }
157} {0 {1 2 3 4}}
158do_test unique-3.3 {
159  catchsql {
160    INSERT INTO t3(a,b,c,d) VALUES(1,2,3,5);
161    SELECT * FROM t3 ORDER BY a,b,c,d;
162  }
163} {0 {1 2 3 4 1 2 3 5}}
164do_test unique-3.4 {
165  catchsql {
166    INSERT INTO t3(a,b,c,d) VALUES(1,4,3,5);
167    SELECT * FROM t3 ORDER BY a,b,c,d;
168  }
169} {1 {UNIQUE constraint failed: t3.a, t3.c, t3.d}}
170verify_ex_errcode unique-3.4b SQLITE_CONSTRAINT_UNIQUE
171integrity_check unique-3.5
172
173# Make sure NULLs are distinct as far as the UNIQUE tests are
174# concerned.
175#
176do_test unique-4.1 {
177  execsql {
178    CREATE TABLE t4(a UNIQUE, b, c, UNIQUE(b,c));
179    INSERT INTO t4 VALUES(1,2,3);
180    INSERT INTO t4 VALUES(NULL, 2, NULL);
181    SELECT * FROM t4;
182  }
183} {1 2 3 {} 2 {}}
184do_test unique-4.2 {
185  catchsql {
186    INSERT INTO t4 VALUES(NULL, 3, 4);
187  }
188} {0 {}}
189do_test unique-4.3 {
190  execsql {
191    SELECT * FROM t4
192  }
193} {1 2 3 {} 2 {} {} 3 4}
194do_test unique-4.4 {
195  catchsql {
196    INSERT INTO t4 VALUES(2, 2, NULL);
197  }
198} {0 {}}
199do_test unique-4.5 {
200  execsql {
201    SELECT * FROM t4
202  }
203} {1 2 3 {} 2 {} {} 3 4 2 2 {}}
204
205# Ticket #1301.  Any NULL value in a set of unique columns should
206# cause the rows to be distinct.
207#
208do_test unique-4.6 {
209  catchsql {
210    INSERT INTO t4 VALUES(NULL, 2, NULL);
211  }
212} {0 {}}
213do_test unique-4.7 {
214  execsql {SELECT * FROM t4}
215} {1 2 3 {} 2 {} {} 3 4 2 2 {} {} 2 {}}
216do_test unique-4.8 {
217  catchsql {CREATE UNIQUE INDEX i4a ON t4(a,b)}
218} {0 {}}
219do_test unique-4.9 {
220  catchsql {CREATE UNIQUE INDEX i4b ON t4(a,b,c)}
221} {0 {}}
222do_test unique-4.10 {
223  catchsql {CREATE UNIQUE INDEX i4c ON t4(b)}
224} {1 {UNIQUE constraint failed: t4.b}}
225verify_ex_errcode unique-4.10b SQLITE_CONSTRAINT_UNIQUE
226integrity_check unique-4.99
227
228# Test the error message generation logic.  In particular, make sure we
229# do not overflow the static buffer used to generate the error message.
230#
231do_test unique-5.1 {
232  execsql {
233    CREATE TABLE t5(
234      first_column_with_long_name,
235      second_column_with_long_name,
236      third_column_with_long_name,
237      fourth_column_with_long_name,
238      fifth_column_with_long_name,
239      sixth_column_with_long_name,
240      UNIQUE(
241        first_column_with_long_name,
242        second_column_with_long_name,
243        third_column_with_long_name,
244        fourth_column_with_long_name,
245        fifth_column_with_long_name,
246        sixth_column_with_long_name
247      )
248    );
249    INSERT INTO t5 VALUES(1,2,3,4,5,6);
250    SELECT * FROM t5;
251  }
252} {1 2 3 4 5 6}
253do_test unique-5.2 {
254  catchsql {
255    INSERT INTO t5 VALUES(1,2,3,4,5,6);
256  }
257} {1 {UNIQUE constraint failed: t5.first_column_with_long_name, t5.second_column_with_long_name, t5.third_column_with_long_name, t5.fourth_column_with_long_name, t5.fifth_column_with_long_name, t5.sixth_column_with_long_name}}
258verify_ex_errcode unique-5.2b SQLITE_CONSTRAINT_UNIQUE
259
260
261finish_test
262