gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/fkey5.test (about) 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-41653-15278 The foreign_key_check pragma checks the 19 # database, or the table called "table-name", for foreign key 20 # constraints that are violated. The foreign_key_check pragma returns 21 # one row output for each foreign key violation. 22 23 set testdir [file dirname $argv0] 24 source $testdir/tester.tcl 25 set testprefix fkey5 26 27 ifcapable {!foreignkey} { 28 finish_test 29 return 30 } 31 32 do_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 } {} 76 do_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} 82 do_test fkey5-1.2b { 83 db eval { 84 PRAGMA main.foreign_key_check; 85 } 86 } {c1 87 p1 0 c1 90 p1 0} 87 do_test fkey5-1.2c { 88 db eval { 89 PRAGMA temp.foreign_key_check; 90 } 91 } {} 92 do_test fkey5-1.3 { 93 db eval { 94 PRAGMA foreign_key_check(c1); 95 } 96 } {c1 87 p1 0 c1 90 p1 0} 97 do_test fkey5-1.4 { 98 db eval { 99 PRAGMA foreign_key_check(c2); 100 } 101 } {} 102 do_test fkey5-1.5 { 103 db eval { 104 PRAGMA main.foreign_key_check(c2); 105 } 106 } {} 107 do_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-00471-55166 The second column is the rowid of the row 119 # that contains the invalid REFERENCES clause, or NULL if the child 120 # table is a WITHOUT ROWID table. 121 # 122 # The second clause in the previous is tested by fkey5-10.3. 123 # 124 # EVIDENCE-OF: R-40482-20265 The third column is the name of the table 125 # that is referred to. 126 # 127 # EVIDENCE-OF: R-62839-07969 The fourth column is the index of the 128 # specific foreign key constraint that failed. 129 # 130 do_test fkey5-2.0 { 131 db eval { 132 INSERT INTO c5 SELECT x FROM c1; 133 DELETE FROM c1; 134 PRAGMA foreign_key_check; 135 } 136 } {c5 1 p1 0 c5 3 p1 0} 137 do_test fkey5-2.1 { 138 db eval { 139 PRAGMA foreign_key_check(c5); 140 } 141 } {c5 1 p1 0 c5 3 p1 0} 142 do_test fkey5-2.2 { 143 db eval { 144 PRAGMA foreign_key_check(c1); 145 } 146 } {} 147 do_execsql_test fkey5-2.3 { 148 PRAGMA foreign_key_list(c5); 149 } {0 0 p1 x {} {NO ACTION} {NO ACTION} NONE} 150 151 do_test fkey5-3.0 { 152 db eval { 153 INSERT INTO c9 SELECT x FROM c5; 154 DELETE FROM c5; 155 PRAGMA foreign_key_check; 156 } 157 } {c9 1 p1 0 c9 3 p1 0} 158 do_test fkey5-3.1 { 159 db eval { 160 PRAGMA foreign_key_check(c9); 161 } 162 } {c9 1 p1 0 c9 3 p1 0} 163 do_test fkey5-3.2 { 164 db eval { 165 PRAGMA foreign_key_check(c5); 166 } 167 } {} 168 169 do_test fkey5-4.0 { 170 db eval { 171 DELETE FROM c9; 172 INSERT INTO c2 VALUES(79),(77),(76); 173 PRAGMA foreign_key_check; 174 } 175 } {c2 76 p2 0 c2 79 p2 0} 176 do_test fkey5-4.1 { 177 db eval { 178 PRAGMA foreign_key_check(c2); 179 } 180 } {c2 76 p2 0 c2 79 p2 0} 181 do_test fkey5-4.2 { 182 db eval { 183 INSERT INTO c6 SELECT x FROM c2; 184 DELETE FROM c2; 185 PRAGMA foreign_key_check; 186 } 187 } {c6 1 p2 0 c6 3 p2 0} 188 do_test fkey5-4.3 { 189 db eval { 190 PRAGMA foreign_key_check(c6); 191 } 192 } {c6 1 p2 0 c6 3 p2 0} 193 do_test fkey5-4.4 { 194 db eval { 195 INSERT INTO c10 SELECT x FROM c6; 196 DELETE FROM c6; 197 PRAGMA foreign_key_check; 198 } 199 } {c10 1 p2 0 c10 3 p2 0} 200 do_test fkey5-4.5 { 201 db eval { 202 PRAGMA foreign_key_check(c10); 203 } 204 } {c10 1 p2 0 c10 3 p2 0} 205 206 do_test fkey5-5.0 { 207 db eval { 208 DELETE FROM c10; 209 INSERT INTO c3 VALUES(68),(67),(65); 210 PRAGMA foreign_key_check; 211 } 212 } {c3 65 p3 0 c3 68 p3 0} 213 do_test fkey5-5.1 { 214 db eval { 215 PRAGMA foreign_key_check(c3); 216 } 217 } {c3 65 p3 0 c3 68 p3 0} 218 do_test fkey5-5.2 { 219 db eval { 220 INSERT INTO c7 SELECT x FROM c3; 221 INSERT INTO c7 VALUES('Alpha'),('alpha'),('foxtrot'); 222 DELETE FROM c3; 223 PRAGMA foreign_key_check; 224 } 225 } {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0} 226 do_test fkey5-5.3 { 227 db eval { 228 PRAGMA foreign_key_check(c7); 229 } 230 } {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0} 231 do_test fkey5-5.4 { 232 db eval { 233 INSERT INTO c11 SELECT x FROM c7; 234 DELETE FROM c7; 235 PRAGMA foreign_key_check; 236 } 237 } {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0} 238 do_test fkey5-5.5 { 239 db eval { 240 PRAGMA foreign_key_check(c11); 241 } 242 } {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0} 243 244 do_test fkey5-6.0 { 245 db eval { 246 DELETE FROM c11; 247 INSERT INTO c4 VALUES(54),(55),(56); 248 PRAGMA foreign_key_check; 249 } 250 } {c4 54 p4 0 c4 56 p4 0} 251 do_test fkey5-6.1 { 252 db eval { 253 PRAGMA foreign_key_check(c4); 254 } 255 } {c4 54 p4 0 c4 56 p4 0} 256 do_test fkey5-6.2 { 257 db eval { 258 INSERT INTO c8 SELECT x FROM c4; 259 INSERT INTO c8 VALUES('Alpha'),('ALPHA'),('foxtrot'); 260 DELETE FROM c4; 261 PRAGMA foreign_key_check; 262 } 263 } {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0} 264 do_test fkey5-6.3 { 265 db eval { 266 PRAGMA foreign_key_check(c8); 267 } 268 } {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0} 269 do_test fkey5-6.4 { 270 db eval { 271 INSERT INTO c12 SELECT x FROM c8; 272 DELETE FROM c8; 273 PRAGMA foreign_key_check; 274 } 275 } {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0} 276 do_test fkey5-6.5 { 277 db eval { 278 PRAGMA foreign_key_check(c12); 279 } 280 } {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0} 281 282 do_test fkey5-7.1 { 283 set res {} 284 db eval { 285 INSERT OR IGNORE INTO c13 SELECT * FROM c12; 286 INSERT OR IGNORE INTO C14 SELECT * FROM c12; 287 DELETE FROM c12; 288 PRAGMA foreign_key_check; 289 } { 290 lappend res [list $table $rowid $fkid $parent] 291 } 292 lsort $res 293 } {{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}} 294 do_test fkey5-7.2 { 295 db eval { 296 PRAGMA foreign_key_check(c14); 297 } 298 } {c14 1 p4 0 c14 3 p4 0 c14 6 p4 0} 299 do_test fkey5-7.3 { 300 db eval { 301 PRAGMA foreign_key_check(c13); 302 } 303 } {c13 1 p3 0 c13 2 p3 0 c13 3 p3 0 c13 4 p3 0 c13 5 p3 0 c13 6 p3 0} 304 305 do_test fkey5-8.0 { 306 db eval { 307 DELETE FROM c13; 308 DELETE FROM c14; 309 INSERT INTO c19 VALUES('alpha','abc'); 310 PRAGMA foreign_key_check(c19); 311 } 312 } {c19 1 p5 0} 313 do_test fkey5-8.1 { 314 db eval { 315 DELETE FROM c19; 316 INSERT INTO c19 VALUES('Alpha','abc'); 317 PRAGMA foreign_key_check(c19); 318 } 319 } {} 320 do_test fkey5-8.2 { 321 db eval { 322 INSERT INTO c20 VALUES('Alpha','abc'); 323 PRAGMA foreign_key_check(c20); 324 } 325 } {c20 1 p5 0} 326 do_test fkey5-8.3 { 327 db eval { 328 DELETE FROM c20; 329 INSERT INTO c20 VALUES('abc','Alpha'); 330 PRAGMA foreign_key_check(c20); 331 } 332 } {} 333 do_test fkey5-8.4 { 334 db eval { 335 INSERT INTO c21 VALUES('alpha','abc '); 336 PRAGMA foreign_key_check(c21); 337 } 338 } {} 339 do_test fkey5-8.5 { 340 db eval { 341 DELETE FROM c21; 342 INSERT INTO c19 VALUES('Alpha','abc'); 343 PRAGMA foreign_key_check(c21); 344 } 345 } {} 346 do_test fkey5-8.6 { 347 db eval { 348 INSERT INTO c22 VALUES('Alpha','abc'); 349 PRAGMA foreign_key_check(c22); 350 } 351 } {c22 1 p6 0} 352 do_test fkey5-8.7 { 353 db eval { 354 DELETE FROM c22; 355 INSERT INTO c22 VALUES('abc ','ALPHA'); 356 PRAGMA foreign_key_check(c22); 357 } 358 } {} 359 360 361 #------------------------------------------------------------------------- 362 # Tests 9.* verify that missing parent tables are handled correctly. 363 # 364 do_execsql_test 9.1.1 { 365 CREATE TABLE k1(x REFERENCES s1); 366 PRAGMA foreign_key_check(k1); 367 } {} 368 do_execsql_test 9.1.2 { 369 INSERT INTO k1 VALUES(NULL); 370 PRAGMA foreign_key_check(k1); 371 } {} 372 do_execsql_test 9.1.3 { 373 INSERT INTO k1 VALUES(1); 374 PRAGMA foreign_key_check(k1); 375 } {k1 2 s1 0} 376 377 do_execsql_test 9.2.1 { 378 CREATE TABLE k2(x, y, FOREIGN KEY(x, y) REFERENCES s1(a, b)); 379 PRAGMA foreign_key_check(k2); 380 } {} 381 do_execsql_test 9.2 { 382 INSERT INTO k2 VALUES(NULL, 'five'); 383 PRAGMA foreign_key_check(k2); 384 } {} 385 do_execsql_test 9.3 { 386 INSERT INTO k2 VALUES('one', NULL); 387 PRAGMA foreign_key_check(k2); 388 } {} 389 do_execsql_test 9.4 { 390 INSERT INTO k2 VALUES('six', 'seven'); 391 PRAGMA foreign_key_check(k2); 392 } {k2 3 s1 0} 393 394 #------------------------------------------------------------------------- 395 # Test using a WITHOUT ROWID table as the child table with an INTEGER 396 # PRIMARY KEY as the parent key. 397 # 398 reset_db 399 do_execsql_test 10.1 { 400 CREATE TABLE p30 (id INTEGER PRIMARY KEY); 401 CREATE TABLE IF NOT EXISTS c30 ( 402 line INTEGER, 403 master REFERENCES p30(id), 404 PRIMARY KEY(master) 405 ) WITHOUT ROWID; 406 407 INSERT INTO p30 (id) VALUES (1); 408 INSERT INTO c30 (master, line) VALUES (1, 999); 409 } 410 do_execsql_test 10.2 { 411 PRAGMA foreign_key_check; 412 } 413 # EVIDENCE-OF: R-00471-55166 The second column is the rowid of the row 414 # that contains the invalid REFERENCES clause, or NULL if the child 415 # table is a WITHOUT ROWID table. 416 do_execsql_test 10.3 { 417 INSERT INTO c30 VALUES(45, 45); 418 PRAGMA foreign_key_check; 419 } {c30 {} p30 0} 420 421 #------------------------------------------------------------------------- 422 # Test "foreign key mismatch" errors. 423 # 424 reset_db 425 do_execsql_test 11.0 { 426 CREATE TABLE tt(y); 427 CREATE TABLE c11(x REFERENCES tt(y)); 428 } 429 do_catchsql_test 11.1 { 430 PRAGMA foreign_key_check; 431 } {1 {foreign key mismatch - "c11" referencing "tt"}} 432 433 # 2020-07-03 Bug in foreign_key_check discovered while working on the 434 # forum reports that pragma_foreign_key_check does not accept an argument: 435 # If two separate schemas seem to reference one another, that causes 436 # problems for foreign_key_check. 437 # 438 reset_db 439 do_execsql_test 12.0 { 440 ATTACH ':memory:' as aux; 441 CREATE TABLE aux.t1(a INTEGER PRIMARY KEY, b TEXT REFERENCES t2); 442 CREATE TABLE main.t2(x TEXT PRIMARY KEY, y INT); 443 INSERT INTO main.t2 VALUES('abc',11),('def',22),('xyz',99); 444 INSERT INTO aux.t1 VALUES(5,'abc'),(7,'xyz'),(9,'oops'); 445 PRAGMA foreign_key_check=t1; 446 } {t1 5 t2 0 t1 7 t2 0 t1 9 t2 0} 447 do_execsql_test 12.1 { 448 CREATE TABLE aux.t2(x TEXT PRIMARY KEY, y INT); 449 INSERT INTO aux.t2 VALUES('abc',11),('def',22),('xyz',99); 450 PRAGMA foreign_key_check=t1; 451 } {t1 9 t2 0} 452 453 # 2020-07-03: the pragma_foreign_key_check virtual table should 454 # accept arguments for the table name and/or schema name. 455 # 456 ifcapable vtab { 457 do_execsql_test 13.0 { 458 SELECT *, 'x' FROM pragma_foreign_key_check('t1'); 459 } {t1 9 t2 0 x} 460 do_catchsql_test 13.1 { 461 SELECT *, 'x' FROM pragma_foreign_key_check('t1','main'); 462 } {1 {no such table: main.t1}} 463 do_execsql_test 13.2 { 464 SELECT *, 'x' FROM pragma_foreign_key_check('t1','aux'); 465 } {t1 9 t2 0 x} 466 } 467 468 ifcapable vtab { 469 reset_db 470 do_execsql_test 13.10 { 471 PRAGMA foreign_keys=OFF; 472 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT REFERENCES t2); 473 CREATE TABLE t2(x TEXT PRIMARY KEY, y INT); 474 CREATE TABLE t3(w TEXT, z INT REFERENCES t1); 475 INSERT INTO t2 VALUES('abc',11),('def',22),('xyz',99); 476 INSERT INTO t1 VALUES(5,'abc'),(7,'xyz'),(9,'oops'); 477 INSERT INTO t3 VALUES(11,7),(22,19); 478 } {} 479 do_execsql_test 13.11 { 480 SELECT x.*, '|' 481 FROM sqlite_schema, pragma_foreign_key_check(name) AS x 482 WHERE type='table' 483 ORDER BY x."table"; 484 } {t1 9 t2 0 | t3 2 t1 0 |} 485 do_execsql_test 13.12 { 486 SELECT *, '|' 487 FROM pragma_foreign_key_check AS x 488 ORDER BY x."table"; 489 } {t1 9 t2 0 | t3 2 t1 0 |} 490 } 491 492 finish_test