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 {column a is not unique}} 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 {column b is not unique}} 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} 76integrity_check unique-1.9 77 78do_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} 87do_test unique-2.1 { 88 catchsql { 89 CREATE UNIQUE INDEX i2 ON t2(a) 90 } 91} {0 {}} 92do_test unique-2.2 { 93 catchsql { 94 SELECT * FROM t2 ORDER BY a 95 } 96} {0 {1 2 3 4}} 97do_test unique-2.3 { 98 catchsql { 99 INSERT INTO t2 VALUES(1,5); 100 } 101} {1 {column a is not unique}} 102do_test unique-2.4 { 103 catchsql { 104 SELECT * FROM t2 ORDER BY a 105 } 106} {0 {1 2 3 4}} 107do_test unique-2.5 { 108 catchsql { 109 DROP INDEX i2; 110 SELECT * FROM t2 ORDER BY a; 111 } 112} {0 {1 2 3 4}} 113do_test unique-2.6 { 114 catchsql { 115 INSERT INTO t2 VALUES(1,5) 116 } 117} {0 {}} 118do_test unique-2.7 { 119 catchsql { 120 SELECT * FROM t2 ORDER BY a, b; 121 } 122} {0 {1 2 1 5 3 4}} 123do_test unique-2.8 { 124 catchsql { 125 CREATE UNIQUE INDEX i2 ON t2(a); 126 } 127} {1 {indexed columns are not unique}} 128do_test unique-2.9 { 129 catchsql { 130 CREATE INDEX i2 ON t2(a); 131 } 132} {0 {}} 133integrity_check unique-2.10 134 135# Test the UNIQUE keyword as used on two or more fields. 136# 137do_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 {}} 148do_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}} 154do_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}} 160do_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}} 166integrity_check unique-3.5 167 168# Make sure NULLs are distinct as far as the UNIQUE tests are 169# concerned. 170# 171do_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 {}} 179do_test unique-4.2 { 180 catchsql { 181 INSERT INTO t4 VALUES(NULL, 3, 4); 182 } 183} {0 {}} 184do_test unique-4.3 { 185 execsql { 186 SELECT * FROM t4 187 } 188} {1 2 3 {} 2 {} {} 3 4} 189do_test unique-4.4 { 190 catchsql { 191 INSERT INTO t4 VALUES(2, 2, NULL); 192 } 193} {0 {}} 194do_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# 203do_test unique-4.6 { 204 catchsql { 205 INSERT INTO t4 VALUES(NULL, 2, NULL); 206 } 207} {0 {}} 208do_test unique-4.7 { 209 execsql {SELECT * FROM t4} 210} {1 2 3 {} 2 {} {} 3 4 2 2 {} {} 2 {}} 211do_test unique-4.8 { 212 catchsql {CREATE UNIQUE INDEX i4a ON t4(a,b)} 213} {0 {}} 214do_test unique-4.9 { 215 catchsql {CREATE UNIQUE INDEX i4b ON t4(a,b,c)} 216} {0 {}} 217do_test unique-4.10 { 218 catchsql {CREATE UNIQUE INDEX i4c ON t4(b)} 219} {1 {indexed columns are not unique}} 220integrity_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# 225do_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} 247do_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 253finish_test 254