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-23918-17301 The foreign_key_check pragma checks the
000019 # database, or the table called "table-name", for foreign key
000020 # constraints that are violated and returns one row of output for each
000021 # 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-25219-25618 The second column is the rowid of the row
000119 # that contains the invalid REFERENCES clause.
000120 #
000121 # EVIDENCE-OF: R-40482-20265 The third column is the name of the table
000122 # that is referred to.
000123 #
000124 # EVIDENCE-OF: R-62839-07969 The fourth column is the index of the
000125 # specific foreign key constraint that failed.
000126 #
000127 do_test fkey5-2.0 {
000128 db eval {
000129 INSERT INTO c5 SELECT x FROM c1;
000130 DELETE FROM c1;
000131 PRAGMA foreign_key_check;
000132 }
000133 } {c5 1 p1 0 c5 3 p1 0}
000134 do_test fkey5-2.1 {
000135 db eval {
000136 PRAGMA foreign_key_check(c5);
000137 }
000138 } {c5 1 p1 0 c5 3 p1 0}
000139 do_test fkey5-2.2 {
000140 db eval {
000141 PRAGMA foreign_key_check(c1);
000142 }
000143 } {}
000144 do_execsql_test fkey5-2.3 {
000145 PRAGMA foreign_key_list(c5);
000146 } {0 0 p1 x {} {NO ACTION} {NO ACTION} NONE}
000147
000148 do_test fkey5-3.0 {
000149 db eval {
000150 INSERT INTO c9 SELECT x FROM c5;
000151 DELETE FROM c5;
000152 PRAGMA foreign_key_check;
000153 }
000154 } {c9 1 p1 0 c9 3 p1 0}
000155 do_test fkey5-3.1 {
000156 db eval {
000157 PRAGMA foreign_key_check(c9);
000158 }
000159 } {c9 1 p1 0 c9 3 p1 0}
000160 do_test fkey5-3.2 {
000161 db eval {
000162 PRAGMA foreign_key_check(c5);
000163 }
000164 } {}
000165
000166 do_test fkey5-4.0 {
000167 db eval {
000168 DELETE FROM c9;
000169 INSERT INTO c2 VALUES(79),(77),(76);
000170 PRAGMA foreign_key_check;
000171 }
000172 } {c2 76 p2 0 c2 79 p2 0}
000173 do_test fkey5-4.1 {
000174 db eval {
000175 PRAGMA foreign_key_check(c2);
000176 }
000177 } {c2 76 p2 0 c2 79 p2 0}
000178 do_test fkey5-4.2 {
000179 db eval {
000180 INSERT INTO c6 SELECT x FROM c2;
000181 DELETE FROM c2;
000182 PRAGMA foreign_key_check;
000183 }
000184 } {c6 1 p2 0 c6 3 p2 0}
000185 do_test fkey5-4.3 {
000186 db eval {
000187 PRAGMA foreign_key_check(c6);
000188 }
000189 } {c6 1 p2 0 c6 3 p2 0}
000190 do_test fkey5-4.4 {
000191 db eval {
000192 INSERT INTO c10 SELECT x FROM c6;
000193 DELETE FROM c6;
000194 PRAGMA foreign_key_check;
000195 }
000196 } {c10 1 p2 0 c10 3 p2 0}
000197 do_test fkey5-4.5 {
000198 db eval {
000199 PRAGMA foreign_key_check(c10);
000200 }
000201 } {c10 1 p2 0 c10 3 p2 0}
000202
000203 do_test fkey5-5.0 {
000204 db eval {
000205 DELETE FROM c10;
000206 INSERT INTO c3 VALUES(68),(67),(65);
000207 PRAGMA foreign_key_check;
000208 }
000209 } {c3 65 p3 0 c3 68 p3 0}
000210 do_test fkey5-5.1 {
000211 db eval {
000212 PRAGMA foreign_key_check(c3);
000213 }
000214 } {c3 65 p3 0 c3 68 p3 0}
000215 do_test fkey5-5.2 {
000216 db eval {
000217 INSERT INTO c7 SELECT x FROM c3;
000218 INSERT INTO c7 VALUES('Alpha'),('alpha'),('foxtrot');
000219 DELETE FROM c3;
000220 PRAGMA foreign_key_check;
000221 }
000222 } {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0}
000223 do_test fkey5-5.3 {
000224 db eval {
000225 PRAGMA foreign_key_check(c7);
000226 }
000227 } {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0}
000228 do_test fkey5-5.4 {
000229 db eval {
000230 INSERT INTO c11 SELECT x FROM c7;
000231 DELETE FROM c7;
000232 PRAGMA foreign_key_check;
000233 }
000234 } {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0}
000235 do_test fkey5-5.5 {
000236 db eval {
000237 PRAGMA foreign_key_check(c11);
000238 }
000239 } {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0}
000240
000241 do_test fkey5-6.0 {
000242 db eval {
000243 DELETE FROM c11;
000244 INSERT INTO c4 VALUES(54),(55),(56);
000245 PRAGMA foreign_key_check;
000246 }
000247 } {c4 54 p4 0 c4 56 p4 0}
000248 do_test fkey5-6.1 {
000249 db eval {
000250 PRAGMA foreign_key_check(c4);
000251 }
000252 } {c4 54 p4 0 c4 56 p4 0}
000253 do_test fkey5-6.2 {
000254 db eval {
000255 INSERT INTO c8 SELECT x FROM c4;
000256 INSERT INTO c8 VALUES('Alpha'),('ALPHA'),('foxtrot');
000257 DELETE FROM c4;
000258 PRAGMA foreign_key_check;
000259 }
000260 } {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0}
000261 do_test fkey5-6.3 {
000262 db eval {
000263 PRAGMA foreign_key_check(c8);
000264 }
000265 } {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0}
000266 do_test fkey5-6.4 {
000267 db eval {
000268 INSERT INTO c12 SELECT x FROM c8;
000269 DELETE FROM c8;
000270 PRAGMA foreign_key_check;
000271 }
000272 } {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0}
000273 do_test fkey5-6.5 {
000274 db eval {
000275 PRAGMA foreign_key_check(c12);
000276 }
000277 } {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0}
000278
000279 do_test fkey5-7.1 {
000280 set res {}
000281 db eval {
000282 INSERT OR IGNORE INTO c13 SELECT * FROM c12;
000283 INSERT OR IGNORE INTO C14 SELECT * FROM c12;
000284 DELETE FROM c12;
000285 PRAGMA foreign_key_check;
000286 } {
000287 lappend res [list $table $rowid $fkid $parent]
000288 }
000289 lsort $res
000290 } {{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}}
000291 do_test fkey5-7.2 {
000292 db eval {
000293 PRAGMA foreign_key_check(c14);
000294 }
000295 } {c14 1 p4 0 c14 3 p4 0 c14 6 p4 0}
000296 do_test fkey5-7.3 {
000297 db eval {
000298 PRAGMA foreign_key_check(c13);
000299 }
000300 } {c13 1 p3 0 c13 2 p3 0 c13 3 p3 0 c13 4 p3 0 c13 5 p3 0 c13 6 p3 0}
000301
000302 do_test fkey5-8.0 {
000303 db eval {
000304 DELETE FROM c13;
000305 DELETE FROM c14;
000306 INSERT INTO c19 VALUES('alpha','abc');
000307 PRAGMA foreign_key_check(c19);
000308 }
000309 } {c19 1 p5 0}
000310 do_test fkey5-8.1 {
000311 db eval {
000312 DELETE FROM c19;
000313 INSERT INTO c19 VALUES('Alpha','abc');
000314 PRAGMA foreign_key_check(c19);
000315 }
000316 } {}
000317 do_test fkey5-8.2 {
000318 db eval {
000319 INSERT INTO c20 VALUES('Alpha','abc');
000320 PRAGMA foreign_key_check(c20);
000321 }
000322 } {c20 1 p5 0}
000323 do_test fkey5-8.3 {
000324 db eval {
000325 DELETE FROM c20;
000326 INSERT INTO c20 VALUES('abc','Alpha');
000327 PRAGMA foreign_key_check(c20);
000328 }
000329 } {}
000330 do_test fkey5-8.4 {
000331 db eval {
000332 INSERT INTO c21 VALUES('alpha','abc ');
000333 PRAGMA foreign_key_check(c21);
000334 }
000335 } {}
000336 do_test fkey5-8.5 {
000337 db eval {
000338 DELETE FROM c21;
000339 INSERT INTO c19 VALUES('Alpha','abc');
000340 PRAGMA foreign_key_check(c21);
000341 }
000342 } {}
000343 do_test fkey5-8.6 {
000344 db eval {
000345 INSERT INTO c22 VALUES('Alpha','abc');
000346 PRAGMA foreign_key_check(c22);
000347 }
000348 } {c22 1 p6 0}
000349 do_test fkey5-8.7 {
000350 db eval {
000351 DELETE FROM c22;
000352 INSERT INTO c22 VALUES('abc ','ALPHA');
000353 PRAGMA foreign_key_check(c22);
000354 }
000355 } {}
000356
000357
000358 #-------------------------------------------------------------------------
000359 # Tests 9.* verify that missing parent tables are handled correctly.
000360 #
000361 do_execsql_test 9.1.1 {
000362 CREATE TABLE k1(x REFERENCES s1);
000363 PRAGMA foreign_key_check(k1);
000364 } {}
000365 do_execsql_test 9.1.2 {
000366 INSERT INTO k1 VALUES(NULL);
000367 PRAGMA foreign_key_check(k1);
000368 } {}
000369 do_execsql_test 9.1.3 {
000370 INSERT INTO k1 VALUES(1);
000371 PRAGMA foreign_key_check(k1);
000372 } {k1 2 s1 0}
000373
000374 do_execsql_test 9.2.1 {
000375 CREATE TABLE k2(x, y, FOREIGN KEY(x, y) REFERENCES s1(a, b));
000376 PRAGMA foreign_key_check(k2);
000377 } {}
000378 do_execsql_test 9.2 {
000379 INSERT INTO k2 VALUES(NULL, 'five');
000380 PRAGMA foreign_key_check(k2);
000381 } {}
000382 do_execsql_test 9.3 {
000383 INSERT INTO k2 VALUES('one', NULL);
000384 PRAGMA foreign_key_check(k2);
000385 } {}
000386 do_execsql_test 9.4 {
000387 INSERT INTO k2 VALUES('six', 'seven');
000388 PRAGMA foreign_key_check(k2);
000389 } {k2 3 s1 0}
000390
000391
000392 finish_test