AryaWu/sqlite
0
1# 2012 December 172#3# The author disclaims copyright to this source code. In place of4# 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; PRAGMA16# schema.foreign_key_check(table-name);17#18# EVIDENCE-OF: R-41653-15278 The foreign_key_check pragma checks the19# database, or the table called "table-name", for foreign key20# constraints that are violated. The foreign_key_check pragma returns21# one row output for each foreign key violation.22 23set testdir [file dirname $argv0]24source $testdir/tester.tcl25set testprefix fkey526 27ifcapable {!foreignkey} {28 finish_test29 return30}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 table116# that contains the REFERENCES clause.117#118# EVIDENCE-OF: R-00471-55166 The second column is the rowid of the row119# that contains the invalid REFERENCES clause, or NULL if the child120# 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 table125# that is referred to.126#127# EVIDENCE-OF: R-62839-07969 The fourth column is the index of the128# specific foreign key constraint that failed.129#130do_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}137do_test fkey5-2.1 {138 db eval {139 PRAGMA foreign_key_check(c5);140 }141} {c5 1 p1 0 c5 3 p1 0}142do_test fkey5-2.2 {143 db eval {144 PRAGMA foreign_key_check(c1);145 }146} {}147do_execsql_test fkey5-2.3 {148 PRAGMA foreign_key_list(c5);149} {0 0 p1 x {} {NO ACTION} {NO ACTION} NONE}150 151do_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}158do_test fkey5-3.1 {159 db eval {160 PRAGMA foreign_key_check(c9);161 }162} {c9 1 p1 0 c9 3 p1 0}163do_test fkey5-3.2 {164 db eval {165 PRAGMA foreign_key_check(c5);166 }167} {}168 169do_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}176do_test fkey5-4.1 {177 db eval {178 PRAGMA foreign_key_check(c2);179 }180} {c2 76 p2 0 c2 79 p2 0}181do_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}188do_test fkey5-4.3 {189 db eval {190 PRAGMA foreign_key_check(c6);191 }192} {c6 1 p2 0 c6 3 p2 0}193do_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}200do_test fkey5-4.5 {201 db eval {202 PRAGMA foreign_key_check(c10);203 }204} {c10 1 p2 0 c10 3 p2 0}205 206do_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}213do_test fkey5-5.1 {214 db eval {215 PRAGMA foreign_key_check(c3);216 }217} {c3 65 p3 0 c3 68 p3 0}218do_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}226do_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}231do_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}238do_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 244do_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}251do_test fkey5-6.1 {252 db eval {253 PRAGMA foreign_key_check(c4);254 }255} {c4 54 p4 0 c4 56 p4 0}256do_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}264do_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}269do_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}276do_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 282do_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 $res293} {{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}}294do_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}299do_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 305do_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}313do_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} {}320do_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}326do_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} {}333do_test fkey5-8.4 {334 db eval {335 INSERT INTO c21 VALUES('alpha','abc ');336 PRAGMA foreign_key_check(c21);337 }338} {}339do_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} {}346do_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}352do_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#364do_execsql_test 9.1.1 {365 CREATE TABLE k1(x REFERENCES s1);366 PRAGMA foreign_key_check(k1);367} {}368do_execsql_test 9.1.2 {369 INSERT INTO k1 VALUES(NULL);370 PRAGMA foreign_key_check(k1);371} {}372do_execsql_test 9.1.3 {373 INSERT INTO k1 VALUES(1);374 PRAGMA foreign_key_check(k1);375} {k1 2 s1 0}376 377do_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} {}381do_execsql_test 9.2 {382 INSERT INTO k2 VALUES(NULL, 'five');383 PRAGMA foreign_key_check(k2);384} {}385do_execsql_test 9.3 {386 INSERT INTO k2 VALUES('one', NULL);387 PRAGMA foreign_key_check(k2);388} {}389do_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#398reset_db399do_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}410do_execsql_test 10.2 {411 PRAGMA foreign_key_check;412}413# EVIDENCE-OF: R-00471-55166 The second column is the rowid of the row414# that contains the invalid REFERENCES clause, or NULL if the child415# table is a WITHOUT ROWID table.416do_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#424reset_db425do_execsql_test 11.0 {426 CREATE TABLE tt(y);427 CREATE TABLE c11(x REFERENCES tt(y));428}429do_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 the434# forum reports that pragma_foreign_key_check does not accept an argument:435# If two separate schemas seem to reference one another, that causes436# problems for foreign_key_check.437#438reset_db439do_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}447do_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 should454# accept arguments for the table name and/or schema name.455#456ifcapable 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 468ifcapable vtab {469 reset_db470 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 x482 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 x488 ORDER BY x."table";489 } {t1 9 t2 0 | t3 2 t1 0 |}490}491 492finish_test493