gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/unique.test (about) 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 {UNIQUE constraint failed: t1.a}} 51 verify_ex_errcode unique-1.3b SQLITE_CONSTRAINT_PRIMARYKEY 52 do_test unique-1.4 { 53 execsql { 54 SELECT * FROM t1 ORDER BY a; 55 } 56 } {1 2 3} 57 do_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}} 62 verify_ex_errcode unique-1.5b SQLITE_CONSTRAINT_UNIQUE 63 do_test unique-1.6 { 64 execsql { 65 SELECT * FROM t1 ORDER BY a; 66 } 67 } {1 2 3} 68 do_test unique-1.7 { 69 catchsql { 70 INSERT INTO t1(a,b,c) VALUES(3,4,5) 71 } 72 } {0 {}} 73 do_test unique-1.8 { 74 execsql { 75 SELECT * FROM t1 ORDER BY a; 76 } 77 } {1 2 3 3 4 5} 78 integrity_check unique-1.9 79 80 do_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} 89 do_test unique-2.1 { 90 catchsql { 91 CREATE UNIQUE INDEX i2 ON t2(a) 92 } 93 } {0 {}} 94 do_test unique-2.2 { 95 catchsql { 96 SELECT * FROM t2 ORDER BY a 97 } 98 } {0 {1 2 3 4}} 99 do_test unique-2.3 { 100 catchsql { 101 INSERT INTO t2 VALUES(1,5); 102 } 103 } {1 {UNIQUE constraint failed: t2.a}} 104 verify_ex_errcode unique-2.3b SQLITE_CONSTRAINT_UNIQUE 105 do_test unique-2.4 { 106 catchsql { 107 SELECT * FROM t2 ORDER BY a 108 } 109 } {0 {1 2 3 4}} 110 do_test unique-2.5 { 111 catchsql { 112 DROP INDEX i2; 113 SELECT * FROM t2 ORDER BY a; 114 } 115 } {0 {1 2 3 4}} 116 do_test unique-2.6 { 117 catchsql { 118 INSERT INTO t2 VALUES(1,5) 119 } 120 } {0 {}} 121 do_test unique-2.7 { 122 catchsql { 123 SELECT * FROM t2 ORDER BY a, b; 124 } 125 } {0 {1 2 1 5 3 4}} 126 do_test unique-2.8 { 127 catchsql { 128 CREATE UNIQUE INDEX i2 ON t2(a); 129 } 130 } {1 {UNIQUE constraint failed: t2.a}} 131 verify_ex_errcode unique-2.8b SQLITE_CONSTRAINT_UNIQUE 132 do_test unique-2.9 { 133 catchsql { 134 CREATE INDEX i2 ON t2(a); 135 } 136 } {0 {}} 137 integrity_check unique-2.10 138 139 # Test the UNIQUE keyword as used on two or more fields. 140 # 141 do_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 {}} 152 do_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}} 158 do_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}} 164 do_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}} 170 verify_ex_errcode unique-3.4b SQLITE_CONSTRAINT_UNIQUE 171 integrity_check unique-3.5 172 173 # Make sure NULLs are distinct as far as the UNIQUE tests are 174 # concerned. 175 # 176 do_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 {}} 184 do_test unique-4.2 { 185 catchsql { 186 INSERT INTO t4 VALUES(NULL, 3, 4); 187 } 188 } {0 {}} 189 do_test unique-4.3 { 190 execsql { 191 SELECT * FROM t4 192 } 193 } {1 2 3 {} 2 {} {} 3 4} 194 do_test unique-4.4 { 195 catchsql { 196 INSERT INTO t4 VALUES(2, 2, NULL); 197 } 198 } {0 {}} 199 do_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 # 208 do_test unique-4.6 { 209 catchsql { 210 INSERT INTO t4 VALUES(NULL, 2, NULL); 211 } 212 } {0 {}} 213 do_test unique-4.7 { 214 execsql {SELECT * FROM t4} 215 } {1 2 3 {} 2 {} {} 3 4 2 2 {} {} 2 {}} 216 do_test unique-4.8 { 217 catchsql {CREATE UNIQUE INDEX i4a ON t4(a,b)} 218 } {0 {}} 219 do_test unique-4.9 { 220 catchsql {CREATE UNIQUE INDEX i4b ON t4(a,b,c)} 221 } {0 {}} 222 do_test unique-4.10 { 223 catchsql {CREATE UNIQUE INDEX i4c ON t4(b)} 224 } {1 {UNIQUE constraint failed: t4.b}} 225 verify_ex_errcode unique-4.10b SQLITE_CONSTRAINT_UNIQUE 226 integrity_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 # 231 do_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} 253 do_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}} 258 verify_ex_errcode unique-5.2b SQLITE_CONSTRAINT_UNIQUE 259 260 261 finish_test