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