test/fkey5.test

   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