drh | adbca9c | 2001-09-27 15:11:53 +0000 | [diff] [blame] | 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 | # |
drh | 098d168 | 2009-05-02 15:46:46 +0000 | [diff] [blame] | 15 | # $Id: unique.test,v 1.9 2009/05/02 15:46:47 drh Exp $ |
drh | adbca9c | 2001-09-27 15:11:53 +0000 | [diff] [blame] | 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 | } |
drh | 4a32431 | 2001-12-21 14:30:42 +0000 | [diff] [blame] | 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 | } |
drh | adbca9c | 2001-09-27 15:11:53 +0000 | [diff] [blame] | 40 | } {0 {}} |
drh | 743daaa | 2001-09-27 23:57:06 +0000 | [diff] [blame] | 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 | } |
drh | f9c8ce3 | 2013-11-05 13:33:55 +0000 | [diff] [blame] | 50 | } {1 {UNIQUE constraint failed: t1.a}} |
drh | 00012df | 2013-11-05 01:59:07 +0000 | [diff] [blame] | 51 | verify_ex_errcode unique-1.3b SQLITE_CONSTRAINT_PRIMARYKEY |
drh | 743daaa | 2001-09-27 23:57:06 +0000 | [diff] [blame] | 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 | } |
drh | f9c8ce3 | 2013-11-05 13:33:55 +0000 | [diff] [blame] | 61 | } {1 {UNIQUE constraint failed: t1.b}} |
drh | 433dccf | 2013-02-09 15:37:11 +0000 | [diff] [blame] | 62 | verify_ex_errcode unique-1.5b SQLITE_CONSTRAINT_UNIQUE |
drh | 743daaa | 2001-09-27 23:57:06 +0000 | [diff] [blame] | 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} |
drh | ed717fe | 2003-06-15 23:42:24 +0000 | [diff] [blame] | 78 | integrity_check unique-1.9 |
drh | 743daaa | 2001-09-27 23:57:06 +0000 | [diff] [blame] | 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 | } |
drh | f9c8ce3 | 2013-11-05 13:33:55 +0000 | [diff] [blame] | 103 | } {1 {UNIQUE constraint failed: t2.a}} |
drh | 433dccf | 2013-02-09 15:37:11 +0000 | [diff] [blame] | 104 | verify_ex_errcode unique-2.3b SQLITE_CONSTRAINT_UNIQUE |
drh | 743daaa | 2001-09-27 23:57:06 +0000 | [diff] [blame] | 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 | } |
drh | f9c8ce3 | 2013-11-05 13:33:55 +0000 | [diff] [blame] | 130 | } {1 {UNIQUE constraint failed: t2.a}} |
drh | 433dccf | 2013-02-09 15:37:11 +0000 | [diff] [blame] | 131 | verify_ex_errcode unique-2.8b SQLITE_CONSTRAINT_UNIQUE |
drh | 743daaa | 2001-09-27 23:57:06 +0000 | [diff] [blame] | 132 | do_test unique-2.9 { |
| 133 | catchsql { |
| 134 | CREATE INDEX i2 ON t2(a); |
| 135 | } |
| 136 | } {0 {}} |
drh | ed717fe | 2003-06-15 23:42:24 +0000 | [diff] [blame] | 137 | integrity_check unique-2.10 |
drh | 743daaa | 2001-09-27 23:57:06 +0000 | [diff] [blame] | 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 | } |
drh | f9c8ce3 | 2013-11-05 13:33:55 +0000 | [diff] [blame] | 169 | } {1 {UNIQUE constraint failed: t3.a, t3.c, t3.d}} |
drh | 433dccf | 2013-02-09 15:37:11 +0000 | [diff] [blame] | 170 | verify_ex_errcode unique-3.4b SQLITE_CONSTRAINT_UNIQUE |
drh | ed717fe | 2003-06-15 23:42:24 +0000 | [diff] [blame] | 171 | integrity_check unique-3.5 |
drh | adbca9c | 2001-09-27 15:11:53 +0000 | [diff] [blame] | 172 | |
drh | f5905aa | 2002-05-26 20:54:33 +0000 | [diff] [blame] | 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 {}} |
drh | 7f057c9 | 2005-06-24 03:53:06 +0000 | [diff] [blame] | 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)} |
drh | f9c8ce3 | 2013-11-05 13:33:55 +0000 | [diff] [blame] | 224 | } {1 {UNIQUE constraint failed: t4.b}} |
drh | 433dccf | 2013-02-09 15:37:11 +0000 | [diff] [blame] | 225 | verify_ex_errcode unique-4.10b SQLITE_CONSTRAINT_UNIQUE |
drh | 7f057c9 | 2005-06-24 03:53:06 +0000 | [diff] [blame] | 226 | integrity_check unique-4.99 |
drh | f5905aa | 2002-05-26 20:54:33 +0000 | [diff] [blame] | 227 | |
drh | 37ed48e | 2003-08-05 13:13:38 +0000 | [diff] [blame] | 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 | } |
drh | f9c8ce3 | 2013-11-05 13:33:55 +0000 | [diff] [blame] | 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}} |
drh | 433dccf | 2013-02-09 15:37:11 +0000 | [diff] [blame] | 258 | verify_ex_errcode unique-5.2b SQLITE_CONSTRAINT_UNIQUE |
| 259 | |
drh | 37ed48e | 2003-08-05 13:13:38 +0000 | [diff] [blame] | 260 | |
drh | adbca9c | 2001-09-27 15:11:53 +0000 | [diff] [blame] | 261 | finish_test |