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 254
