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