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