xref: /sqlite-3.40.0/test/unique.test (revision f5905aa7) 
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.4 2002/05/26 20:54:35 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 {constraint failed}}
51do_test unique-1.4 {
52  execsql {
53    SELECT * FROM t1 ORDER BY a;
54  }
55} {1 2 3}
56do_test unique-1.5 {
57  catchsql {
58    INSERT INTO t1(a,b,c) VALUES(3,2,4)
59  }
60} {1 {constraint failed}}
61do_test unique-1.6 {
62  execsql {
63    SELECT * FROM t1 ORDER BY a;
64  }
65} {1 2 3}
66do_test unique-1.7 {
67  catchsql {
68    INSERT INTO t1(a,b,c) VALUES(3,4,5)
69  }
70} {0 {}}
71do_test unique-1.8 {
72  execsql {
73    SELECT * FROM t1 ORDER BY a;
74  }
75} {1 2 3 3 4 5}
76
77do_test unique-2.0 {
78  execsql {
79    DROP TABLE t1;
80    CREATE TABLE t2(a int, b int);
81    INSERT INTO t2(a,b) VALUES(1,2);
82    INSERT INTO t2(a,b) VALUES(3,4);
83    SELECT * FROM t2 ORDER BY a;
84  }
85} {1 2 3 4}
86do_test unique-2.1 {
87  catchsql {
88    CREATE UNIQUE INDEX i2 ON t2(a)
89  }
90} {0 {}}
91do_test unique-2.2 {
92  catchsql {
93    SELECT * FROM t2 ORDER BY a
94  }
95} {0 {1 2 3 4}}
96do_test unique-2.3 {
97  catchsql {
98    INSERT INTO t2 VALUES(1,5);
99  }
100} {1 {constraint failed}}
101do_test unique-2.4 {
102  catchsql {
103    SELECT * FROM t2 ORDER BY a
104  }
105} {0 {1 2 3 4}}
106do_test unique-2.5 {
107  catchsql {
108    DROP INDEX i2;
109    SELECT * FROM t2 ORDER BY a;
110  }
111} {0 {1 2 3 4}}
112do_test unique-2.6 {
113  catchsql {
114    INSERT INTO t2 VALUES(1,5)
115  }
116} {0 {}}
117do_test unique-2.7 {
118  catchsql {
119    SELECT * FROM t2 ORDER BY a, b;
120  }
121} {0 {1 2 1 5 3 4}}
122do_test unique-2.8 {
123  catchsql {
124    CREATE UNIQUE INDEX i2 ON t2(a);
125  }
126} {1 {constraint failed}}
127do_test unique-2.9 {
128  catchsql {
129    CREATE INDEX i2 ON t2(a);
130  }
131} {0 {}}
132
133# Test the UNIQUE keyword as used on two or more fields.
134#
135do_test unique-3.1 {
136  catchsql {
137    CREATE TABLE t3(
138       a int,
139       b int,
140       c int,
141       d int,
142       unique(a,c,d)
143     );
144  }
145} {0 {}}
146do_test unique-3.2 {
147  catchsql {
148    INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4);
149    SELECT * FROM t3 ORDER BY a,b,c,d;
150  }
151} {0 {1 2 3 4}}
152do_test unique-3.3 {
153  catchsql {
154    INSERT INTO t3(a,b,c,d) VALUES(1,2,3,5);
155    SELECT * FROM t3 ORDER BY a,b,c,d;
156  }
157} {0 {1 2 3 4 1 2 3 5}}
158do_test unique-3.4 {
159  catchsql {
160    INSERT INTO t3(a,b,c,d) VALUES(1,4,3,5);
161    SELECT * FROM t3 ORDER BY a,b,c,d;
162  }
163} {1 {constraint failed}}
164
165# Make sure NULLs are distinct as far as the UNIQUE tests are
166# concerned.
167#
168do_test unique-4.1 {
169  execsql {
170    CREATE TABLE t4(a UNIQUE, b, c, UNIQUE(b,c));
171    INSERT INTO t4 VALUES(1,2,3);
172    INSERT INTO t4 VALUES(NULL, 2, NULL);
173    SELECT * FROM t4;
174  }
175} {1 2 3 {} 2 {}}
176do_test unique-4.2 {
177  catchsql {
178    INSERT INTO t4 VALUES(NULL, 3, 4);
179  }
180} {0 {}}
181do_test unique-4.3 {
182  execsql {
183    SELECT * FROM t4
184  }
185} {1 2 3 {} 2 {} {} 3 4}
186do_test unique-4.4 {
187  catchsql {
188    INSERT INTO t4 VALUES(2, 2, NULL);
189  }
190} {0 {}}
191do_test unique-4.5 {
192  execsql {
193    SELECT * FROM t4
194  }
195} {1 2 3 {} 2 {} {} 3 4 2 2 {}}
196
197
198finish_test
199