000001 # 2012 December 17
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 # This file implements regression tests for SQLite library.
000012 #
000013 # This file tests the PRAGMA foreign_key_check command.
000014 #
000015 # EVIDENCE-OF: R-15402-03103 PRAGMA schema.foreign_key_check; PRAGMA
000016 # schema.foreign_key_check(table-name);
000017 #
000018 # EVIDENCE-OF: R-41653-15278 The foreign_key_check pragma checks the
000019 # database, or the table called "table-name", for foreign key
000020 # constraints that are violated. The foreign_key_check pragma returns
000021 # one row output for each foreign key violation.
000022
000023 set testdir [file dirname $argv0]
000024 source $testdir/tester.tcl
000025 set testprefix fkey5
000026
000027 ifcapable {!foreignkey} {
000028 finish_test
000029 return
000030 }
000031
000032 do_test fkey5-1.1 {
000033 db eval {
000034 CREATE TABLE p1(a INTEGER PRIMARY KEY); INSERT INTO p1 VALUES(88),(89);
000035 CREATE TABLE p2(a INT PRIMARY KEY); INSERT INTO p2 VALUES(77),(78);
000036 CREATE TABLE p3(a TEXT PRIMARY KEY);
000037 INSERT INTO p3 VALUES(66),(67),('alpha'),('BRAVO');
000038 CREATE TABLE p4(a TEXT PRIMARY KEY COLLATE nocase);
000039 INSERT INTO p4 VALUES('alpha'),('BRAVO'),('55'),('Delta'),('ECHO');
000040 CREATE TABLE p5(a INTEGER PRIMARY KEY, b, c, UNIQUE(b,c));
000041 INSERT INTO p5 VALUES(1,'Alpha','abc'),(2,'beta','def');
000042 CREATE TABLE p6(a INTEGER PRIMARY KEY, b TEXT COLLATE nocase,
000043 c TEXT COLLATE rtrim, UNIQUE(b,c));
000044 INSERT INTO p6 VALUES(1,'Alpha','abc '),(2,'bETA','def ');
000045
000046 CREATE TABLE c1(x INTEGER PRIMARY KEY references p1);
000047 CREATE TABLE c2(x INTEGER PRIMARY KEY references p2);
000048 CREATE TABLE c3(x INTEGER PRIMARY KEY references p3);
000049 CREATE TABLE c4(x INTEGER PRIMARY KEY references p4);
000050 CREATE TABLE c5(x INT references p1);
000051 CREATE TABLE c6(x INT references p2);
000052 CREATE TABLE c7(x INT references p3);
000053 CREATE TABLE c8(x INT references p4);
000054 CREATE TABLE c9(x TEXT UNIQUE references p1);
000055 CREATE TABLE c10(x TEXT UNIQUE references p2);
000056 CREATE TABLE c11(x TEXT UNIQUE references p3);
000057 CREATE TABLE c12(x TEXT UNIQUE references p4);
000058 CREATE TABLE c13(x TEXT COLLATE nocase references p3);
000059 CREATE TABLE c14(x TEXT COLLATE nocase references p4);
000060 CREATE TABLE c15(x, y, FOREIGN KEY(x,y) REFERENCES p5(b,c));
000061 CREATE TABLE c16(x, y, FOREIGN KEY(x,y) REFERENCES p5(c,b));
000062 CREATE TABLE c17(x, y, FOREIGN KEY(x,y) REFERENCES p6(b,c));
000063 CREATE TABLE c18(x, y, FOREIGN KEY(x,y) REFERENCES p6(c,b));
000064 CREATE TABLE c19(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
000065 FOREIGN KEY(x,y) REFERENCES p5(b,c));
000066 CREATE TABLE c20(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
000067 FOREIGN KEY(x,y) REFERENCES p5(c,b));
000068 CREATE TABLE c21(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
000069 FOREIGN KEY(x,y) REFERENCES p6(b,c));
000070 CREATE TABLE c22(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
000071 FOREIGN KEY(x,y) REFERENCES p6(c,b));
000072
000073 PRAGMA foreign_key_check;
000074 }
000075 } {}
000076 do_test fkey5-1.2 {
000077 db eval {
000078 INSERT INTO c1 VALUES(90),(87),(88);
000079 PRAGMA foreign_key_check;
000080 }
000081 } {c1 87 p1 0 c1 90 p1 0}
000082 do_test fkey5-1.2b {
000083 db eval {
000084 PRAGMA main.foreign_key_check;
000085 }
000086 } {c1 87 p1 0 c1 90 p1 0}
000087 do_test fkey5-1.2c {
000088 db eval {
000089 PRAGMA temp.foreign_key_check;
000090 }
000091 } {}
000092 do_test fkey5-1.3 {
000093 db eval {
000094 PRAGMA foreign_key_check(c1);
000095 }
000096 } {c1 87 p1 0 c1 90 p1 0}
000097 do_test fkey5-1.4 {
000098 db eval {
000099 PRAGMA foreign_key_check(c2);
000100 }
000101 } {}
000102 do_test fkey5-1.5 {
000103 db eval {
000104 PRAGMA main.foreign_key_check(c2);
000105 }
000106 } {}
000107 do_test fkey5-1.6 {
000108 catchsql {
000109 PRAGMA temp.foreign_key_check(c2);
000110 }
000111 } {1 {no such table: temp.c2}}
000112
000113 # EVIDENCE-OF: R-45728-08709 There are four columns in each result row.
000114 #
000115 # EVIDENCE-OF: R-55672-01620 The first column is the name of the table
000116 # that contains the REFERENCES clause.
000117 #
000118 # EVIDENCE-OF: R-00471-55166 The second column is the rowid of the row
000119 # that contains the invalid REFERENCES clause, or NULL if the child
000120 # table is a WITHOUT ROWID table.
000121 #
000122 # The second clause in the previous is tested by fkey5-10.3.
000123 #
000124 # EVIDENCE-OF: R-40482-20265 The third column is the name of the table
000125 # that is referred to.
000126 #
000127 # EVIDENCE-OF: R-62839-07969 The fourth column is the index of the
000128 # specific foreign key constraint that failed.
000129 #
000130 do_test fkey5-2.0 {
000131 db eval {
000132 INSERT INTO c5 SELECT x FROM c1;
000133 DELETE FROM c1;
000134 PRAGMA foreign_key_check;
000135 }
000136 } {c5 1 p1 0 c5 3 p1 0}
000137 do_test fkey5-2.1 {
000138 db eval {
000139 PRAGMA foreign_key_check(c5);
000140 }
000141 } {c5 1 p1 0 c5 3 p1 0}
000142 do_test fkey5-2.2 {
000143 db eval {
000144 PRAGMA foreign_key_check(c1);
000145 }
000146 } {}
000147 do_execsql_test fkey5-2.3 {
000148 PRAGMA foreign_key_list(c5);
000149 } {0 0 p1 x {} {NO ACTION} {NO ACTION} NONE}
000150
000151 do_test fkey5-3.0 {
000152 db eval {
000153 INSERT INTO c9 SELECT x FROM c5;
000154 DELETE FROM c5;
000155 PRAGMA foreign_key_check;
000156 }
000157 } {c9 1 p1 0 c9 3 p1 0}
000158 do_test fkey5-3.1 {
000159 db eval {
000160 PRAGMA foreign_key_check(c9);
000161 }
000162 } {c9 1 p1 0 c9 3 p1 0}
000163 do_test fkey5-3.2 {
000164 db eval {
000165 PRAGMA foreign_key_check(c5);
000166 }
000167 } {}
000168
000169 do_test fkey5-4.0 {
000170 db eval {
000171 DELETE FROM c9;
000172 INSERT INTO c2 VALUES(79),(77),(76);
000173 PRAGMA foreign_key_check;
000174 }
000175 } {c2 76 p2 0 c2 79 p2 0}
000176 do_test fkey5-4.1 {
000177 db eval {
000178 PRAGMA foreign_key_check(c2);
000179 }
000180 } {c2 76 p2 0 c2 79 p2 0}
000181 do_test fkey5-4.2 {
000182 db eval {
000183 INSERT INTO c6 SELECT x FROM c2;
000184 DELETE FROM c2;
000185 PRAGMA foreign_key_check;
000186 }
000187 } {c6 1 p2 0 c6 3 p2 0}
000188 do_test fkey5-4.3 {
000189 db eval {
000190 PRAGMA foreign_key_check(c6);
000191 }
000192 } {c6 1 p2 0 c6 3 p2 0}
000193 do_test fkey5-4.4 {
000194 db eval {
000195 INSERT INTO c10 SELECT x FROM c6;
000196 DELETE FROM c6;
000197 PRAGMA foreign_key_check;
000198 }
000199 } {c10 1 p2 0 c10 3 p2 0}
000200 do_test fkey5-4.5 {
000201 db eval {
000202 PRAGMA foreign_key_check(c10);
000203 }
000204 } {c10 1 p2 0 c10 3 p2 0}
000205
000206 do_test fkey5-5.0 {
000207 db eval {
000208 DELETE FROM c10;
000209 INSERT INTO c3 VALUES(68),(67),(65);
000210 PRAGMA foreign_key_check;
000211 }
000212 } {c3 65 p3 0 c3 68 p3 0}
000213 do_test fkey5-5.1 {
000214 db eval {
000215 PRAGMA foreign_key_check(c3);
000216 }
000217 } {c3 65 p3 0 c3 68 p3 0}
000218 do_test fkey5-5.2 {
000219 db eval {
000220 INSERT INTO c7 SELECT x FROM c3;
000221 INSERT INTO c7 VALUES('Alpha'),('alpha'),('foxtrot');
000222 DELETE FROM c3;
000223 PRAGMA foreign_key_check;
000224 }
000225 } {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0}
000226 do_test fkey5-5.3 {
000227 db eval {
000228 PRAGMA foreign_key_check(c7);
000229 }
000230 } {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0}
000231 do_test fkey5-5.4 {
000232 db eval {
000233 INSERT INTO c11 SELECT x FROM c7;
000234 DELETE FROM c7;
000235 PRAGMA foreign_key_check;
000236 }
000237 } {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0}
000238 do_test fkey5-5.5 {
000239 db eval {
000240 PRAGMA foreign_key_check(c11);
000241 }
000242 } {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0}
000243
000244 do_test fkey5-6.0 {
000245 db eval {
000246 DELETE FROM c11;
000247 INSERT INTO c4 VALUES(54),(55),(56);
000248 PRAGMA foreign_key_check;
000249 }
000250 } {c4 54 p4 0 c4 56 p4 0}
000251 do_test fkey5-6.1 {
000252 db eval {
000253 PRAGMA foreign_key_check(c4);
000254 }
000255 } {c4 54 p4 0 c4 56 p4 0}
000256 do_test fkey5-6.2 {
000257 db eval {
000258 INSERT INTO c8 SELECT x FROM c4;
000259 INSERT INTO c8 VALUES('Alpha'),('ALPHA'),('foxtrot');
000260 DELETE FROM c4;
000261 PRAGMA foreign_key_check;
000262 }
000263 } {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0}
000264 do_test fkey5-6.3 {
000265 db eval {
000266 PRAGMA foreign_key_check(c8);
000267 }
000268 } {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0}
000269 do_test fkey5-6.4 {
000270 db eval {
000271 INSERT INTO c12 SELECT x FROM c8;
000272 DELETE FROM c8;
000273 PRAGMA foreign_key_check;
000274 }
000275 } {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0}
000276 do_test fkey5-6.5 {
000277 db eval {
000278 PRAGMA foreign_key_check(c12);
000279 }
000280 } {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0}
000281
000282 do_test fkey5-7.1 {
000283 set res {}
000284 db eval {
000285 INSERT OR IGNORE INTO c13 SELECT * FROM c12;
000286 INSERT OR IGNORE INTO C14 SELECT * FROM c12;
000287 DELETE FROM c12;
000288 PRAGMA foreign_key_check;
000289 } {
000290 lappend res [list $table $rowid $fkid $parent]
000291 }
000292 lsort $res
000293 } {{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}}
000294 do_test fkey5-7.2 {
000295 db eval {
000296 PRAGMA foreign_key_check(c14);
000297 }
000298 } {c14 1 p4 0 c14 3 p4 0 c14 6 p4 0}
000299 do_test fkey5-7.3 {
000300 db eval {
000301 PRAGMA foreign_key_check(c13);
000302 }
000303 } {c13 1 p3 0 c13 2 p3 0 c13 3 p3 0 c13 4 p3 0 c13 5 p3 0 c13 6 p3 0}
000304
000305 do_test fkey5-8.0 {
000306 db eval {
000307 DELETE FROM c13;
000308 DELETE FROM c14;
000309 INSERT INTO c19 VALUES('alpha','abc');
000310 PRAGMA foreign_key_check(c19);
000311 }
000312 } {c19 1 p5 0}
000313 do_test fkey5-8.1 {
000314 db eval {
000315 DELETE FROM c19;
000316 INSERT INTO c19 VALUES('Alpha','abc');
000317 PRAGMA foreign_key_check(c19);
000318 }
000319 } {}
000320 do_test fkey5-8.2 {
000321 db eval {
000322 INSERT INTO c20 VALUES('Alpha','abc');
000323 PRAGMA foreign_key_check(c20);
000324 }
000325 } {c20 1 p5 0}
000326 do_test fkey5-8.3 {
000327 db eval {
000328 DELETE FROM c20;
000329 INSERT INTO c20 VALUES('abc','Alpha');
000330 PRAGMA foreign_key_check(c20);
000331 }
000332 } {}
000333 do_test fkey5-8.4 {
000334 db eval {
000335 INSERT INTO c21 VALUES('alpha','abc ');
000336 PRAGMA foreign_key_check(c21);
000337 }
000338 } {}
000339 do_test fkey5-8.5 {
000340 db eval {
000341 DELETE FROM c21;
000342 INSERT INTO c19 VALUES('Alpha','abc');
000343 PRAGMA foreign_key_check(c21);
000344 }
000345 } {}
000346 do_test fkey5-8.6 {
000347 db eval {
000348 INSERT INTO c22 VALUES('Alpha','abc');
000349 PRAGMA foreign_key_check(c22);
000350 }
000351 } {c22 1 p6 0}
000352 do_test fkey5-8.7 {
000353 db eval {
000354 DELETE FROM c22;
000355 INSERT INTO c22 VALUES('abc ','ALPHA');
000356 PRAGMA foreign_key_check(c22);
000357 }
000358 } {}
000359
000360
000361 #-------------------------------------------------------------------------
000362 # Tests 9.* verify that missing parent tables are handled correctly.
000363 #
000364 do_execsql_test 9.1.1 {
000365 CREATE TABLE k1(x REFERENCES s1);
000366 PRAGMA foreign_key_check(k1);
000367 } {}
000368 do_execsql_test 9.1.2 {
000369 INSERT INTO k1 VALUES(NULL);
000370 PRAGMA foreign_key_check(k1);
000371 } {}
000372 do_execsql_test 9.1.3 {
000373 INSERT INTO k1 VALUES(1);
000374 PRAGMA foreign_key_check(k1);
000375 } {k1 2 s1 0}
000376
000377 do_execsql_test 9.2.1 {
000378 CREATE TABLE k2(x, y, FOREIGN KEY(x, y) REFERENCES s1(a, b));
000379 PRAGMA foreign_key_check(k2);
000380 } {}
000381 do_execsql_test 9.2 {
000382 INSERT INTO k2 VALUES(NULL, 'five');
000383 PRAGMA foreign_key_check(k2);
000384 } {}
000385 do_execsql_test 9.3 {
000386 INSERT INTO k2 VALUES('one', NULL);
000387 PRAGMA foreign_key_check(k2);
000388 } {}
000389 do_execsql_test 9.4 {
000390 INSERT INTO k2 VALUES('six', 'seven');
000391 PRAGMA foreign_key_check(k2);
000392 } {k2 3 s1 0}
000393
000394 #-------------------------------------------------------------------------
000395 # Test using a WITHOUT ROWID table as the child table with an INTEGER
000396 # PRIMARY KEY as the parent key.
000397 #
000398 reset_db
000399 do_execsql_test 10.1 {
000400 CREATE TABLE p30 (id INTEGER PRIMARY KEY);
000401 CREATE TABLE IF NOT EXISTS c30 (
000402 line INTEGER,
000403 master REFERENCES p30(id),
000404 PRIMARY KEY(master)
000405 ) WITHOUT ROWID;
000406
000407 INSERT INTO p30 (id) VALUES (1);
000408 INSERT INTO c30 (master, line) VALUES (1, 999);
000409 }
000410 do_execsql_test 10.2 {
000411 PRAGMA foreign_key_check;
000412 }
000413 # EVIDENCE-OF: R-00471-55166 The second column is the rowid of the row
000414 # that contains the invalid REFERENCES clause, or NULL if the child
000415 # table is a WITHOUT ROWID table.
000416 do_execsql_test 10.3 {
000417 INSERT INTO c30 VALUES(45, 45);
000418 PRAGMA foreign_key_check;
000419 } {c30 {} p30 0}
000420
000421 #-------------------------------------------------------------------------
000422 # Test "foreign key mismatch" errors.
000423 #
000424 reset_db
000425 do_execsql_test 11.0 {
000426 CREATE TABLE tt(y);
000427 CREATE TABLE c11(x REFERENCES tt(y));
000428 }
000429 do_catchsql_test 11.1 {
000430 PRAGMA foreign_key_check;
000431 } {1 {foreign key mismatch - "c11" referencing "tt"}}
000432
000433 # 2020-07-03 Bug in foreign_key_check discovered while working on the
000434 # forum reports that pragma_foreign_key_check does not accept an argument:
000435 # If two separate schemas seem to reference one another, that causes
000436 # problems for foreign_key_check.
000437 #
000438 reset_db
000439 do_execsql_test 12.0 {
000440 ATTACH ':memory:' as aux;
000441 CREATE TABLE aux.t1(a INTEGER PRIMARY KEY, b TEXT REFERENCES t2);
000442 CREATE TABLE main.t2(x TEXT PRIMARY KEY, y INT);
000443 INSERT INTO main.t2 VALUES('abc',11),('def',22),('xyz',99);
000444 INSERT INTO aux.t1 VALUES(5,'abc'),(7,'xyz'),(9,'oops');
000445 PRAGMA foreign_key_check=t1;
000446 } {t1 5 t2 0 t1 7 t2 0 t1 9 t2 0}
000447 do_execsql_test 12.1 {
000448 CREATE TABLE aux.t2(x TEXT PRIMARY KEY, y INT);
000449 INSERT INTO aux.t2 VALUES('abc',11),('def',22),('xyz',99);
000450 PRAGMA foreign_key_check=t1;
000451 } {t1 9 t2 0}
000452
000453 # 2020-07-03: the pragma_foreign_key_check virtual table should
000454 # accept arguments for the table name and/or schema name.
000455 #
000456 ifcapable vtab {
000457 do_execsql_test 13.0 {
000458 SELECT *, 'x' FROM pragma_foreign_key_check('t1');
000459 } {t1 9 t2 0 x}
000460 do_catchsql_test 13.1 {
000461 SELECT *, 'x' FROM pragma_foreign_key_check('t1','main');
000462 } {1 {no such table: main.t1}}
000463 do_execsql_test 13.2 {
000464 SELECT *, 'x' FROM pragma_foreign_key_check('t1','aux');
000465 } {t1 9 t2 0 x}
000466 }
000467
000468 ifcapable vtab {
000469 reset_db
000470 do_execsql_test 13.10 {
000471 PRAGMA foreign_keys=OFF;
000472 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT REFERENCES t2);
000473 CREATE TABLE t2(x TEXT PRIMARY KEY, y INT);
000474 CREATE TABLE t3(w TEXT, z INT REFERENCES t1);
000475 INSERT INTO t2 VALUES('abc',11),('def',22),('xyz',99);
000476 INSERT INTO t1 VALUES(5,'abc'),(7,'xyz'),(9,'oops');
000477 INSERT INTO t3 VALUES(11,7),(22,19);
000478 } {}
000479 do_execsql_test 13.11 {
000480 SELECT x.*, '|'
000481 FROM sqlite_schema, pragma_foreign_key_check(name) AS x
000482 WHERE type='table'
000483 ORDER BY x."table";
000484 } {t1 9 t2 0 | t3 2 t1 0 |}
000485 do_execsql_test 13.12 {
000486 SELECT *, '|'
000487 FROM pragma_foreign_key_check AS x
000488 ORDER BY x."table";
000489 } {t1 9 t2 0 | t3 2 t1 0 |}
000490 }
000491
000492 finish_test