1# 2012 December 17 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. 12# 13# This file tests the PRAGMA foreign_key_check command. 14# 15# EVIDENCE-OF: R-05426-18119 PRAGMA foreign_key_check; PRAGMA 16# foreign_key_check(table-name); 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20set testprefix fkey5 21 22ifcapable {!foreignkey} { 23 finish_test 24 return 25} 26 27do_test fkey5-1.1 { 28 db eval { 29 CREATE TABLE p1(a INTEGER PRIMARY KEY); INSERT INTO p1 VALUES(88),(89); 30 CREATE TABLE p2(a INT PRIMARY KEY); INSERT INTO p2 VALUES(77),(78); 31 CREATE TABLE p3(a TEXT PRIMARY KEY); 32 INSERT INTO p3 VALUES(66),(67),('alpha'),('BRAVO'); 33 CREATE TABLE p4(a TEXT PRIMARY KEY COLLATE nocase); 34 INSERT INTO p4 VALUES('alpha'),('BRAVO'),('55'),('Delta'),('ECHO'); 35 CREATE TABLE p5(a INTEGER PRIMARY KEY, b, c, UNIQUE(b,c)); 36 INSERT INTO p5 VALUES(1,'Alpha','abc'),(2,'beta','def'); 37 CREATE TABLE p6(a INTEGER PRIMARY KEY, b TEXT COLLATE nocase, 38 c TEXT COLLATE rtrim, UNIQUE(b,c)); 39 INSERT INTO p6 VALUES(1,'Alpha','abc '),(2,'bETA','def '); 40 41 CREATE TABLE c1(x INTEGER PRIMARY KEY references p1); 42 CREATE TABLE c2(x INTEGER PRIMARY KEY references p2); 43 CREATE TABLE c3(x INTEGER PRIMARY KEY references p3); 44 CREATE TABLE c4(x INTEGER PRIMARY KEY references p4); 45 CREATE TABLE c5(x INT references p1); 46 CREATE TABLE c6(x INT references p2); 47 CREATE TABLE c7(x INT references p3); 48 CREATE TABLE c8(x INT references p4); 49 CREATE TABLE c9(x TEXT UNIQUE references p1); 50 CREATE TABLE c10(x TEXT UNIQUE references p2); 51 CREATE TABLE c11(x TEXT UNIQUE references p3); 52 CREATE TABLE c12(x TEXT UNIQUE references p4); 53 CREATE TABLE c13(x TEXT COLLATE nocase references p3); 54 CREATE TABLE c14(x TEXT COLLATE nocase references p4); 55 CREATE TABLE c15(x, y, FOREIGN KEY(x,y) REFERENCES p5(b,c)); 56 CREATE TABLE c16(x, y, FOREIGN KEY(x,y) REFERENCES p5(c,b)); 57 CREATE TABLE c17(x, y, FOREIGN KEY(x,y) REFERENCES p6(b,c)); 58 CREATE TABLE c18(x, y, FOREIGN KEY(x,y) REFERENCES p6(c,b)); 59 CREATE TABLE c19(x TEXT COLLATE nocase, y TEXT COLLATE rtrim, 60 FOREIGN KEY(x,y) REFERENCES p5(b,c)); 61 CREATE TABLE c20(x TEXT COLLATE nocase, y TEXT COLLATE rtrim, 62 FOREIGN KEY(x,y) REFERENCES p5(c,b)); 63 CREATE TABLE c21(x TEXT COLLATE nocase, y TEXT COLLATE rtrim, 64 FOREIGN KEY(x,y) REFERENCES p6(b,c)); 65 CREATE TABLE c22(x TEXT COLLATE nocase, y TEXT COLLATE rtrim, 66 FOREIGN KEY(x,y) REFERENCES p6(c,b)); 67 68 PRAGMA foreign_key_check; 69 } 70} {} 71do_test fkey5-1.2 { 72 db eval { 73 INSERT INTO c1 VALUES(90),(87),(88); 74 PRAGMA foreign_key_check; 75 } 76} {c1 87 p1 0 c1 90 p1 0} 77do_test fkey5-1.3 { 78 db eval { 79 PRAGMA foreign_key_check(c1); 80 } 81} {c1 87 p1 0 c1 90 p1 0} 82do_test fkey5-1.4 { 83 db eval { 84 PRAGMA foreign_key_check(c2); 85 } 86} {} 87 88# EVIDENCE-OF: R-45728-08709 There are four columns in each result row. 89# 90# EVIDENCE-OF: R-55672-01620 The first column is the name of the table 91# that contains the REFERENCES clause. 92# 93# EVIDENCE-OF: R-25219-25618 The second column is the rowid of the row 94# that contains the invalid REFERENCES clause. 95# 96# EVIDENCE-OF: R-40482-20265 The third column is the name of the table 97# that is referred to. 98# 99# EVIDENCE-OF: R-62839-07969 The fourth column is the index of the 100# specific foreign key constraint that failed. 101# 102do_test fkey5-2.0 { 103 db eval { 104 INSERT INTO c5 SELECT x FROM c1; 105 DELETE FROM c1; 106 PRAGMA foreign_key_check; 107 } 108} {c5 1 p1 0 c5 3 p1 0} 109do_test fkey5-2.1 { 110 db eval { 111 PRAGMA foreign_key_check(c5); 112 } 113} {c5 1 p1 0 c5 3 p1 0} 114do_test fkey5-2.2 { 115 db eval { 116 PRAGMA foreign_key_check(c1); 117 } 118} {} 119do_execsql_test fkey5-2.3 { 120 PRAGMA foreign_key_list(c5); 121} {0 0 p1 x {} {NO ACTION} {NO ACTION} NONE} 122 123do_test fkey5-3.0 { 124 db eval { 125 INSERT INTO c9 SELECT x FROM c5; 126 DELETE FROM c5; 127 PRAGMA foreign_key_check; 128 } 129} {c9 1 p1 0 c9 3 p1 0} 130do_test fkey5-3.1 { 131 db eval { 132 PRAGMA foreign_key_check(c9); 133 } 134} {c9 1 p1 0 c9 3 p1 0} 135do_test fkey5-3.2 { 136 db eval { 137 PRAGMA foreign_key_check(c5); 138 } 139} {} 140 141do_test fkey5-4.0 { 142 db eval { 143 DELETE FROM c9; 144 INSERT INTO c2 VALUES(79),(77),(76); 145 PRAGMA foreign_key_check; 146 } 147} {c2 76 p2 0 c2 79 p2 0} 148do_test fkey5-4.1 { 149 db eval { 150 PRAGMA foreign_key_check(c2); 151 } 152} {c2 76 p2 0 c2 79 p2 0} 153do_test fkey5-4.2 { 154 db eval { 155 INSERT INTO c6 SELECT x FROM c2; 156 DELETE FROM c2; 157 PRAGMA foreign_key_check; 158 } 159} {c6 1 p2 0 c6 3 p2 0} 160do_test fkey5-4.3 { 161 db eval { 162 PRAGMA foreign_key_check(c6); 163 } 164} {c6 1 p2 0 c6 3 p2 0} 165do_test fkey5-4.4 { 166 db eval { 167 INSERT INTO c10 SELECT x FROM c6; 168 DELETE FROM c6; 169 PRAGMA foreign_key_check; 170 } 171} {c10 1 p2 0 c10 3 p2 0} 172do_test fkey5-4.5 { 173 db eval { 174 PRAGMA foreign_key_check(c10); 175 } 176} {c10 1 p2 0 c10 3 p2 0} 177 178do_test fkey5-5.0 { 179 db eval { 180 DELETE FROM c10; 181 INSERT INTO c3 VALUES(68),(67),(65); 182 PRAGMA foreign_key_check; 183 } 184} {c3 65 p3 0 c3 68 p3 0} 185do_test fkey5-5.1 { 186 db eval { 187 PRAGMA foreign_key_check(c3); 188 } 189} {c3 65 p3 0 c3 68 p3 0} 190do_test fkey5-5.2 { 191 db eval { 192 INSERT INTO c7 SELECT x FROM c3; 193 INSERT INTO c7 VALUES('Alpha'),('alpha'),('foxtrot'); 194 DELETE FROM c3; 195 PRAGMA foreign_key_check; 196 } 197} {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0} 198do_test fkey5-5.3 { 199 db eval { 200 PRAGMA foreign_key_check(c7); 201 } 202} {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0} 203do_test fkey5-5.4 { 204 db eval { 205 INSERT INTO c11 SELECT x FROM c7; 206 DELETE FROM c7; 207 PRAGMA foreign_key_check; 208 } 209} {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0} 210do_test fkey5-5.5 { 211 db eval { 212 PRAGMA foreign_key_check(c11); 213 } 214} {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0} 215 216do_test fkey5-6.0 { 217 db eval { 218 DELETE FROM c11; 219 INSERT INTO c4 VALUES(54),(55),(56); 220 PRAGMA foreign_key_check; 221 } 222} {c4 54 p4 0 c4 56 p4 0} 223do_test fkey5-6.1 { 224 db eval { 225 PRAGMA foreign_key_check(c4); 226 } 227} {c4 54 p4 0 c4 56 p4 0} 228do_test fkey5-6.2 { 229 db eval { 230 INSERT INTO c8 SELECT x FROM c4; 231 INSERT INTO c8 VALUES('Alpha'),('ALPHA'),('foxtrot'); 232 DELETE FROM c4; 233 PRAGMA foreign_key_check; 234 } 235} {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0} 236do_test fkey5-6.3 { 237 db eval { 238 PRAGMA foreign_key_check(c8); 239 } 240} {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0} 241do_test fkey5-6.4 { 242 db eval { 243 INSERT INTO c12 SELECT x FROM c8; 244 DELETE FROM c8; 245 PRAGMA foreign_key_check; 246 } 247} {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0} 248do_test fkey5-6.5 { 249 db eval { 250 PRAGMA foreign_key_check(c12); 251 } 252} {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0} 253 254do_test fkey5-7.1 { 255 db eval { 256 INSERT OR IGNORE INTO c13 SELECT * FROM c12; 257 INSERT OR IGNORE INTO C14 SELECT * FROM c12; 258 DELETE FROM c12; 259 PRAGMA foreign_key_check; 260 } 261} {c14 1 p4 0 c14 3 p4 0 c14 6 p4 0 c13 1 p3 0 c13 2 p3 0 c13 3 p3 0 c13 4 p3 0 c13 5 p3 0 c13 6 p3 0} 262do_test fkey5-7.2 { 263 db eval { 264 PRAGMA foreign_key_check(c14); 265 } 266} {c14 1 p4 0 c14 3 p4 0 c14 6 p4 0} 267do_test fkey5-7.3 { 268 db eval { 269 PRAGMA foreign_key_check(c13); 270 } 271} {c13 1 p3 0 c13 2 p3 0 c13 3 p3 0 c13 4 p3 0 c13 5 p3 0 c13 6 p3 0} 272 273do_test fkey5-8.0 { 274 db eval { 275 DELETE FROM c13; 276 DELETE FROM c14; 277 INSERT INTO c19 VALUES('alpha','abc'); 278 PRAGMA foreign_key_check(c19); 279 } 280} {c19 1 p5 0} 281do_test fkey5-8.1 { 282 db eval { 283 DELETE FROM c19; 284 INSERT INTO c19 VALUES('Alpha','abc'); 285 PRAGMA foreign_key_check(c19); 286 } 287} {} 288do_test fkey5-8.2 { 289 db eval { 290 INSERT INTO c20 VALUES('Alpha','abc'); 291 PRAGMA foreign_key_check(c20); 292 } 293} {c20 1 p5 0} 294do_test fkey5-8.3 { 295 db eval { 296 DELETE FROM c20; 297 INSERT INTO c20 VALUES('abc','Alpha'); 298 PRAGMA foreign_key_check(c20); 299 } 300} {} 301do_test fkey5-8.4 { 302 db eval { 303 INSERT INTO c21 VALUES('alpha','abc '); 304 PRAGMA foreign_key_check(c21); 305 } 306} {} 307do_test fkey5-8.5 { 308 db eval { 309 DELETE FROM c21; 310 INSERT INTO c19 VALUES('Alpha','abc'); 311 PRAGMA foreign_key_check(c21); 312 } 313} {} 314do_test fkey5-8.6 { 315 db eval { 316 INSERT INTO c22 VALUES('Alpha','abc'); 317 PRAGMA foreign_key_check(c22); 318 } 319} {c22 1 p6 0} 320do_test fkey5-8.7 { 321 db eval { 322 DELETE FROM c22; 323 INSERT INTO c22 VALUES('abc ','ALPHA'); 324 PRAGMA foreign_key_check(c22); 325 } 326} {} 327 328 329#------------------------------------------------------------------------- 330# Tests 9.* verify that missing parent tables are handled correctly. 331# 332do_execsql_test 9.1.1 { 333 CREATE TABLE k1(x REFERENCES s1); 334 PRAGMA foreign_key_check(k1); 335} {} 336do_execsql_test 9.1.2 { 337 INSERT INTO k1 VALUES(NULL); 338 PRAGMA foreign_key_check(k1); 339} {} 340do_execsql_test 9.1.3 { 341 INSERT INTO k1 VALUES(1); 342 PRAGMA foreign_key_check(k1); 343} {k1 2 s1 0} 344 345do_execsql_test 9.2.1 { 346 CREATE TABLE k2(x, y, FOREIGN KEY(x, y) REFERENCES s1(a, b)); 347 PRAGMA foreign_key_check(k2); 348} {} 349do_execsql_test 9.2 { 350 INSERT INTO k2 VALUES(NULL, 'five'); 351 PRAGMA foreign_key_check(k2); 352} {} 353do_execsql_test 9.3 { 354 INSERT INTO k2 VALUES('one', NULL); 355 PRAGMA foreign_key_check(k2); 356} {} 357do_execsql_test 9.4 { 358 INSERT INTO k2 VALUES('six', 'seven'); 359 PRAGMA foreign_key_check(k2); 360} {k2 3 s1 0} 361 362 363finish_test 364