**   Register (x+3):      3.1  (type real)
*/

/*
** A foreign key constraint requires that the key columns in the parent
** table are collectively subject to a UNIQUE or PRIMARY KEY constraint.
** Given that pParent is the parent table for foreign key constraint pFKey, 
** search the schema for a unique index on the parent key columns. 
**
** If successful, zero is returned. If the parent key is an INTEGER PRIMARY 
** KEY column, then output variable *ppIdx is set to NULL. Otherwise, *ppIdx 
** is set to point to the unique index. 
** 
** If the parent key consists of a single column (the foreign key constraint
** is not a composite foreign key), output variable *paiCol is set to NULL.

**      consists of a a different number of columns to the child key in 
**      the child table.
**
** then non-zero is returned, and a "foreign key mismatch" error loaded
** into pParse. If an OOM error occurs, non-zero is returned and the
** pParse->db->mallocFailed flag is set.
*/
int sqlite3FkLocateIndex(
  Parse *pParse,                  /* Parse context to store any error in */
  Table *pParent,                 /* Parent table of FK constraint pFKey */
  FKey *pFKey,                    /* Foreign key to find index for */
  Index **ppIdx,                  /* OUT: Unique index on parent table */
  int **paiCol                    /* OUT: Map of index columns in pFKey */
){
  Index *pIdx = 0;                    /* Value to return via *ppIdx */

        if( i==nCol ) break;      /* pIdx is usable */
      }
    }
  }

  if( !pIdx ){
    if( !pParse->disableTriggers ){
      sqlite3ErrorMsg(pParse,
           "foreign key mismatch - \"%w\" referencing \"%w\"",
           pFKey->pFrom->zName, pFKey->zTo);
    }
    sqlite3DbFree(pParse->db, aiCol);
    return 1;
  }

  *ppIdx = pIdx;
  return 0;

    ** schema items cannot be located, set an error in pParse and return 
    ** early.  */
    if( pParse->disableTriggers ){
      pTo = sqlite3FindTable(db, pFKey->zTo, zDb);
    }else{
      pTo = sqlite3LocateTable(pParse, 0, pFKey->zTo, zDb);
    }
    if( !pTo || sqlite3FkLocateIndex(pParse, pTo, pFKey, &pIdx, &aiFree) ){
      assert( isIgnoreErrors==0 || (regOld!=0 && regNew==0) );
      if( !isIgnoreErrors || db->mallocFailed ) return;
      if( pTo==0 ){
        /* If isIgnoreErrors is true, then a table is being dropped. In this
        ** case SQLite runs a "DELETE FROM xxx" on the table being dropped
        ** before actually dropping it in order to check FK constraints.
        ** If the parent table of an FK constraint on the current table is

    if( !pFKey->isDeferred && !pParse->pToplevel && !pParse->isMultiWrite ){
      assert( regOld==0 && regNew!=0 );
      /* Inserting a single row into a parent table cannot cause an immediate
      ** foreign key violation. So do nothing in this case.  */
      continue;
    }

    if( sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){
      if( !isIgnoreErrors || db->mallocFailed ) return;
      continue;
    }
    assert( aiCol || pFKey->nCol==1 );

    /* Create a SrcList structure containing a single table (the table 
    ** the foreign key that refers to this table is attached to). This

    FKey *p;
    int i;
    for(p=pTab->pFKey; p; p=p->pNextFrom){
      for(i=0; i<p->nCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom);
    }
    for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
      Index *pIdx = 0;
      sqlite3FkLocateIndex(pParse, pTab, p, &pIdx, 0);
      if( pIdx ){
        for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]);
      }
    }
  }
  return mask;
}

    TriggerStep *pStep = 0;        /* First (only) step of trigger program */
    Expr *pWhere = 0;             /* WHERE clause of trigger step */
    ExprList *pList = 0;          /* Changes list if ON UPDATE CASCADE */
    Select *pSelect = 0;          /* If RESTRICT, "SELECT RAISE(...)" */
    int i;                        /* Iterator variable */
    Expr *pWhen = 0;              /* WHEN clause for the trigger */

    if( sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0;
    assert( aiCol || pFKey->nCol==1 );

    for(i=0; i<pFKey->nCol; i++){
      Token tOld = { "old", 3 };  /* Literal "old" token */
      Token tNew = { "new", 3 };  /* Literal "new" token */
      Token tFromCol;             /* Name of column in child table */
      Token tToCol;               /* Name of column in parent table */

            sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 8);
          }
          ++i;
          pFK = pFK->pNextFrom;
        }
      }
    }
  }else
#endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */

#ifndef SQLITE_OMIT_FOREIGN_KEY
  if( sqlite3StrICmp(zLeft, "foreign_key_check")==0 ){
    FKey *pFK;             /* A foreign key constraint */
    Table *pTab;           /* Child table contain "REFERENCES" keyword */
    Table *pParent;        /* Parent table that child points to */
    Index *pIdx;           /* Index in the parent table */
    int i;                 /* Loop counter:  Foreign key number for pTab */
    int j;                 /* Loop counter:  Field of the foreign key */
    HashElem *k;           /* Loop counter:  Next table in schema */
    int x;                 /* result variable */
    int regResult;         /* 3 registers to hold a result row */
    int regKey;            /* Register to hold key for checking the FK */
    int regRow;            /* Registers to hold a row from pTab */
    int addrTop;           /* Top of a loop checking foreign keys */
    int addrOk;            /* Jump here if the key is OK */
    int *aiCols;           /* child to parent column mapping */

    if( sqlite3ReadSchema(pParse) ) goto pragma_out;
    regResult = pParse->nMem+1;
    pParse->nMem += 4;
    regKey = ++pParse->nMem;
    regRow = ++pParse->nMem;
    v = sqlite3GetVdbe(pParse);
    sqlite3VdbeSetNumCols(v, 4);
    sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "table", SQLITE_STATIC);
    sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "rowid", SQLITE_STATIC);
    sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "parent", SQLITE_STATIC);
    sqlite3VdbeSetColName(v, 3, COLNAME_NAME, "fkid", SQLITE_STATIC);
    sqlite3CodeVerifySchema(pParse, iDb);
    k = sqliteHashFirst(&db->aDb[iDb].pSchema->tblHash);
    while( k ){
      if( zRight ){
        pTab = sqlite3LocateTable(pParse, 0, zRight, zDb);
        k = 0;
      }else{
        pTab = (Table*)sqliteHashData(k);
        k = sqliteHashNext(k);
      }
      if( pTab==0 || pTab->pFKey==0 ) continue;
      sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
      if( pTab->nCol+regRow>pParse->nMem ) pParse->nMem = pTab->nCol + regRow;
      sqlite3OpenTable(pParse, 0, iDb, pTab, OP_OpenRead);
      sqlite3VdbeAddOp4(v, OP_String8, 0, regResult, 0, pTab->zName,
                        P4_TRANSIENT);
      for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
        pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb);
        if( pParent==0 ) break;
        pIdx = 0;
        sqlite3TableLock(pParse, iDb, pParent->tnum, 0, pParent->zName);
        x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, 0);
        if( x==0 ){
          if( pIdx==0 ){
            sqlite3OpenTable(pParse, i, iDb, pParent, OP_OpenRead);
          }else{
            KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);
            sqlite3VdbeAddOp3(v, OP_OpenRead, i, pIdx->tnum, iDb);
            sqlite3VdbeChangeP4(v, -1, (char*)pKey, P4_KEYINFO_HANDOFF);
          }
        }else{
          k = 0;
          break;
        }
      }
      if( pFK ) break;
      if( pParse->nTab<i ) pParse->nTab = i;
      addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, 0);
      for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
        pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb);
        assert( pParent!=0 );
        pIdx = 0;
        aiCols = 0;
        x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, &aiCols);
        assert( x==0 );
        addrOk = sqlite3VdbeMakeLabel(v);
        if( pIdx==0 ){
          int iKey = pFK->aCol[0].iFrom;
          assert( iKey>=0 && iKey<pTab->nCol );
          if( iKey!=pTab->iPKey ){
            sqlite3VdbeAddOp3(v, OP_Column, 0, iKey, regRow);
            sqlite3ColumnDefault(v, pTab, iKey, regRow);
            sqlite3VdbeAddOp2(v, OP_IsNull, regRow, addrOk);
            sqlite3VdbeAddOp2(v, OP_MustBeInt, regRow,
               sqlite3VdbeCurrentAddr(v)+3);
          }else{
            sqlite3VdbeAddOp2(v, OP_Rowid, 0, regRow);
          }
          sqlite3VdbeAddOp3(v, OP_NotExists, i, 0, regRow);
          sqlite3VdbeAddOp2(v, OP_Goto, 0, addrOk);
          sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
        }else{
          for(j=0; j<pFK->nCol; j++){
            sqlite3ExprCodeGetColumnOfTable(v, pTab, 0,
                            aiCols ? aiCols[j] : pFK->aCol[0].iFrom, regRow+j);
            sqlite3VdbeAddOp2(v, OP_IsNull, regRow+j, addrOk);
          }
          sqlite3VdbeAddOp3(v, OP_MakeRecord, regRow, pFK->nCol, regKey);
          sqlite3VdbeChangeP4(v, -1,
                   sqlite3IndexAffinityStr(v,pIdx), P4_TRANSIENT);
          sqlite3VdbeAddOp4Int(v, OP_Found, i, addrOk, regKey, 0);
        }
        sqlite3VdbeAddOp2(v, OP_Rowid, 0, regResult+1);
        sqlite3VdbeAddOp4(v, OP_String8, 0, regResult+2, 0, 
                          pFK->zTo, P4_TRANSIENT);
        sqlite3VdbeAddOp2(v, OP_Integer, i-1, regResult+3);
        sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, 4);
        sqlite3VdbeResolveLabel(v, addrOk);
        sqlite3DbFree(db, aiCols);
      }
      sqlite3VdbeAddOp2(v, OP_Next, 0, addrTop+1);
      sqlite3VdbeJumpHere(v, addrTop);
    }
  }else
#endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */

#ifndef NDEBUG
  if( sqlite3StrICmp(zLeft, "parser_trace")==0 ){
    if( zRight ){
      if( sqlite3GetBoolean(zRight, 0) ){

  #define sqlite3FkCheck(a,b,c,d)
  #define sqlite3FkDropTable(a,b,c)
  #define sqlite3FkOldmask(a,b)      0
  #define sqlite3FkRequired(a,b,c,d) 0
#endif
#ifndef SQLITE_OMIT_FOREIGN_KEY
  void sqlite3FkDelete(sqlite3 *, Table*);
  int sqlite3FkLocateIndex(Parse*,Table*,FKey*,Index**,int**);
#else
  #define sqlite3FkDelete(a,b)
  #define sqlite3FkLocateIndex(a,b,c,d,e)
#endif


/*
** Available fault injectors.  Should be numbered beginning with 0.
*/
#define SQLITE_FAULTINJECTOR_MALLOC     0

  }
} {}
proc test_efkey_57 {tn isError sql} {
  catchsql { DROP TABLE t1 }
  execsql $sql
  do_test e_fkey-18.$tn {
    catchsql { INSERT INTO t2 VALUES(NULL) }
  } [lindex {{0 {}} {/1 {foreign key mismatch - ".*" referencing ".*"}/}} \
     $isError]
}
test_efkey_57 2 0 { CREATE TABLE t1(x PRIMARY KEY) }
test_efkey_57 3 0 { CREATE TABLE t1(x UNIQUE) }
test_efkey_57 4 0 { CREATE TABLE t1(x); CREATE UNIQUE INDEX t1i ON t1(x) }
test_efkey_57 5 1 { 
  CREATE TABLE t1(x); 
  CREATE UNIQUE INDEX t1i ON t1(x COLLATE nocase);

    INSERT INTO child1 VALUES('xxx', 1);
    INSERT INTO child2 VALUES('xxx', 2);
    INSERT INTO child3 VALUES(3, 4);
  }
} {}
do_test e_fkey-19.2 {
  catchsql { INSERT INTO child4 VALUES('xxx', 5) }
} {1 {foreign key mismatch - "child4" referencing "parent"}}
do_test e_fkey-19.3 {
  catchsql { INSERT INTO child5 VALUES('xxx', 6) }
} {1 {foreign key mismatch - "child5" referencing "parent"}}
do_test e_fkey-19.4 {
  catchsql { INSERT INTO child6 VALUES(2, 3) }
} {1 {foreign key mismatch - "child6" referencing "parent"}}
do_test e_fkey-19.5 {
  catchsql { INSERT INTO child7 VALUES(3) }
} {1 {foreign key mismatch - "child7" referencing "parent"}}

#-------------------------------------------------------------------------
# Test errors in the database schema that are detected while preparing
# DML statements. The error text for these messages always matches 
# either "foreign key mismatch" or "no such table*" (using [string match]).
#
# EVIDENCE-OF: R-45488-08504 If the database schema contains foreign key

    CREATE TABLE p7(a, b, PRIMARY KEY(a, b));
    CREATE TABLE c7(c, d REFERENCES p7);
  }
} {}

foreach {tn tbl ptbl err} {
  2 c1 {} "no such table: main.nosuchtable"
  3 c2 p2 "foreign key mismatch - \"c2\" referencing \"p2\""
  4 c3 p3 "foreign key mismatch - \"c3\" referencing \"p3\""
  5 c4 p4 "foreign key mismatch - \"c4\" referencing \"p4\""
  6 c5 p5 "foreign key mismatch - \"c5\" referencing \"p5\""
  7 c6 p6 "foreign key mismatch - \"c6\" referencing \"p6\""
  8 c7 p7 "foreign key mismatch - \"c7\" referencing \"p7\""
} {
  do_test e_fkey-20.$tn.1 {
    catchsql "INSERT INTO $tbl VALUES('a', 'b')"
  } [list 1 $err]
  do_test e_fkey-20.$tn.2 {
    catchsql "UPDATE $tbl SET c = ?, d = ?"
  } [list 1 $err]

  execsql {
    INSERT INTO parent2 VALUES('I', 'II');
    INSERT INTO child8 VALUES('I', 'II');
  }
} {}
do_test e_fkey-21.3 {
  catchsql { INSERT INTO child9 VALUES('I') }
} {1 {foreign key mismatch - "child9" referencing "parent2"}}
do_test e_fkey-21.4 {
  catchsql { INSERT INTO child9 VALUES('II') }
} {1 {foreign key mismatch - "child9" referencing "parent2"}}
do_test e_fkey-21.5 {
  catchsql { INSERT INTO child9 VALUES(NULL) }
} {1 {foreign key mismatch - "child9" referencing "parent2"}}
do_test e_fkey-21.6 {
  catchsql { INSERT INTO child10 VALUES('I', 'II', 'III') }
} {1 {foreign key mismatch - "child10" referencing "parent2"}}
do_test e_fkey-21.7 {
  catchsql { INSERT INTO child10 VALUES(1, 2, 3) }
} {1 {foreign key mismatch - "child10" referencing "parent2"}}
do_test e_fkey-21.8 {
  catchsql { INSERT INTO child10 VALUES(NULL, NULL, NULL) }
} {1 {foreign key mismatch - "child10" referencing "parent2"}}

#-------------------------------------------------------------------------
# Test errors that are reported when creating the child table. 
# Specifically:
#
#   * different number of child and parent key columns, and
#   * child columns that do not exist.

do_test e_fkey-28.8 {
  drop_all_tables
  execsql {
    CREATE TABLE p(x PRIMARY KEY);
    CREATE TABLE c(a, b, FOREIGN KEY(a,b) REFERENCES p);
  }
  catchsql {DELETE FROM p}
} {1 {foreign key mismatch - "c" referencing "p"}}
do_test e_fkey-28.9 {
  drop_all_tables
  execsql {
    CREATE TABLE p(x, y, PRIMARY KEY(x,y));
    CREATE TABLE c(a REFERENCES p);
  }
  catchsql {DELETE FROM p}
} {1 {foreign key mismatch - "c" referencing "p"}}


#-------------------------------------------------------------------------
# EVIDENCE-OF: R-24676-09859
#
# Test the example schema in the "Composite Foreign Key Constraints" 
# section.

      SELECT * FROM c3;
    ROLLBACK;
  }
} {{} 2}
do_test e_fkey-60.4 {
  execsql { CREATE TABLE nosuchtable(x PRIMARY KEY) }
  catchsql { DELETE FROM p }
} {1 {foreign key mismatch - "c2" referencing "p"}}
do_test e_fkey-60.5 {
  execsql { DROP TABLE c1 }
  catchsql { DELETE FROM p }
} {1 {foreign key mismatch - "c2" referencing "p"}}
do_test e_fkey-60.6 {
  execsql { DROP TABLE c2 }
  execsql { DELETE FROM p }
} {}

#-------------------------------------------------------------------------
# Test that the special behaviours of ALTER and DROP TABLE are only

  4.13 "UPDATE t7 SET b = 1"              {0 {}}
  4.14 "INSERT INTO t8 VALUES('a', 'b')"  {1 {foreign key constraint failed}}
  4.15 "UPDATE t7 SET b = 5"              {1 {foreign key constraint failed}}
  4.16 "UPDATE t7 SET rowid = 5"          {1 {foreign key constraint failed}}
  4.17 "UPDATE t7 SET a = 10"             {0 {}}

  5.1  "INSERT INTO t9 VALUES(1, 3)"      {1 {no such table: main.nosuchtable}}
  5.2  "INSERT INTO t10 VALUES(1, 3)"  
                            {1 {foreign key mismatch - "t10" referencing "t9"}}
}

do_test fkey2-1.1.0 {
  execsql [string map {/D/ {}} $FkeySimpleSchema]
} {}
foreach {tn zSql res} $FkeySimpleTests {
  do_test fkey2-1.1.$tn.1 { catchsql $zSql } $res
  do_test fkey2-1.1.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
  do_test fkey2-1.1.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
  do_test fkey2-1.1.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
  do_test fkey2-1.1.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
  do_test fkey2-1.1.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
  do_test fkey2-1.1.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
}
drop_all_tables

do_test fkey2-1.2.0 {
  execsql [string map {/D/ {DEFERRABLE INITIALLY DEFERRED}} $FkeySimpleSchema]
} {}
foreach {tn zSql res} $FkeySimpleTests {
  do_test fkey2-1.2.$tn { catchsql $zSql } $res
  do_test fkey2-1.2.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
  do_test fkey2-1.2.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
  do_test fkey2-1.2.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
  do_test fkey2-1.2.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
  do_test fkey2-1.2.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
  do_test fkey2-1.2.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
}
drop_all_tables

do_test fkey2-1.3.0 {
  execsql [string map {/D/ {}} $FkeySimpleSchema]
  execsql { PRAGMA count_changes = 1 }
} {}
foreach {tn zSql res} $FkeySimpleTests {
  if {$res == "0 {}"} { set res {0 1} }
  do_test fkey2-1.3.$tn { catchsql $zSql } $res
  do_test fkey2-1.3.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
  do_test fkey2-1.3.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
  do_test fkey2-1.3.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
  do_test fkey2-1.3.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
  do_test fkey2-1.3.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
  do_test fkey2-1.3.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
}
execsql { PRAGMA count_changes = 0 }
drop_all_tables

do_test fkey2-1.4.0 {
  execsql [string map {/D/ {}} $FkeySimpleSchema]
  execsql { PRAGMA count_changes = 1 }

  CREATE UNIQUE INDEX i ON p(a COLLATE nocase);
  CREATE TABLE c(x REFERENCES p(a));
}] {
  drop_all_tables
  do_test fkey2-10.1.[incr tn] {
    execsql $zSql
    catchsql { INSERT INTO c DEFAULT VALUES }
  } {/1 {foreign key mismatch - "c" referencing "."}/}
}

# "rowid" cannot be used as part of a child or parent key definition 
# unless it happens to be the name of an explicitly declared column.
#
do_test fkey2-10.2.1 {
  drop_all_tables

  drop_all_tables
  catchsql {
    CREATE TABLE t1(a, b);
    CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
    INSERT INTO t1(rowid, a, b) VALUES(1, 1, 1);
    INSERT INTO t2 VALUES(1, 1);
  }
} {1 {foreign key mismatch - "t2" referencing "t1"}}
do_test fkey2-10.2.2 {
  drop_all_tables
  catchsql {
    CREATE TABLE t1(rowid PRIMARY KEY, b);
    CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
    INSERT INTO t1(rowid, b) VALUES(1, 1);
    INSERT INTO t2 VALUES(1, 1);

} {}
do_test fkey-2.14.3.8 {
  execsql {
    CREATE TABLE pp(x, y, PRIMARY KEY(x, y));
    CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z));
  }
  catchsql { INSERT INTO cc VALUES(1, 2) }
} {1 {foreign key mismatch - "cc" referencing "pp"}}
do_test fkey-2.14.3.9 {
  execsql { DROP TABLE cc }
} {}
do_test fkey-2.14.3.10 {
  execsql {
    CREATE TABLE cc(a, b, 
      FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED

# 2012 December 17
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file tests the PRAGMA foreign_key_check command.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable {!foreignkey} {
  finish_test
  return
}

do_test fkey5-1.1 {
  db eval {
    CREATE TABLE p1(a INTEGER PRIMARY KEY); INSERT INTO p1 VALUES(88),(89);
    CREATE TABLE p2(a INT PRIMARY KEY); INSERT INTO p2 VALUES(77),(78);
    CREATE TABLE p3(a TEXT PRIMARY KEY);
    INSERT INTO p3 VALUES(66),(67),('alpha'),('BRAVO');
    CREATE TABLE p4(a TEXT PRIMARY KEY COLLATE nocase);
    INSERT INTO p4 VALUES('alpha'),('BRAVO'),('55'),('Delta'),('ECHO');
    CREATE TABLE p5(a INTEGER PRIMARY KEY, b, c, UNIQUE(b,c));
    INSERT INTO p5 VALUES(1,'Alpha','abc'),(2,'beta','def');
    CREATE TABLE p6(a INTEGER PRIMARY KEY, b TEXT COLLATE nocase,
                    c TEXT COLLATE rtrim, UNIQUE(b,c));
    INSERT INTO p6 VALUES(1,'Alpha','abc '),(2,'bETA','def    ');

    CREATE TABLE c1(x INTEGER PRIMARY KEY references p1);
    CREATE TABLE c2(x INTEGER PRIMARY KEY references p2);
    CREATE TABLE c3(x INTEGER PRIMARY KEY references p3);
    CREATE TABLE c4(x INTEGER PRIMARY KEY references p4);
    CREATE TABLE c5(x INT references p1);
    CREATE TABLE c6(x INT references p2);
    CREATE TABLE c7(x INT references p3);
    CREATE TABLE c8(x INT references p4);
    CREATE TABLE c9(x TEXT UNIQUE references p1);
    CREATE TABLE c10(x TEXT UNIQUE references p2);
    CREATE TABLE c11(x TEXT UNIQUE references p3);
    CREATE TABLE c12(x TEXT UNIQUE references p4);
    CREATE TABLE c13(x TEXT COLLATE nocase references p3);
    CREATE TABLE c14(x TEXT COLLATE nocase references p4);
    CREATE TABLE c15(x, y, FOREIGN KEY(x,y) REFERENCES p5(b,c));
    CREATE TABLE c16(x, y, FOREIGN KEY(x,y) REFERENCES p5(c,b));
    CREATE TABLE c17(x, y, FOREIGN KEY(x,y) REFERENCES p6(b,c));
    CREATE TABLE c18(x, y, FOREIGN KEY(x,y) REFERENCES p6(c,b));
    CREATE TABLE c19(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
                     FOREIGN KEY(x,y) REFERENCES p5(b,c));
    CREATE TABLE c20(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
                     FOREIGN KEY(x,y) REFERENCES p5(c,b));
    CREATE TABLE c21(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
                     FOREIGN KEY(x,y) REFERENCES p6(b,c));
    CREATE TABLE c22(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
                     FOREIGN KEY(x,y) REFERENCES p6(c,b));

    PRAGMA foreign_key_check;
  }
} {}    
do_test fkey5-1.2 {
  db eval {
    INSERT INTO c1 VALUES(90),(87),(88);
    PRAGMA foreign_key_check;
  }
} {c1 87 p1 0 c1 90 p1 0}
do_test fkey5-1.3 {
  db eval {
    PRAGMA foreign_key_check(c1);
  }
} {c1 87 p1 0 c1 90 p1 0}
do_test fkey5-1.4 {
  db eval {
    PRAGMA foreign_key_check(c2);
  }
} {}

do_test fkey5-2.0 {
  db eval {
    INSERT INTO c5 SELECT x FROM c1;
    DELETE FROM c1;
    PRAGMA foreign_key_check;
  }
} {c5 1 p1 0 c5 3 p1 0}
do_test fkey5-2.1 {
  db eval {
    PRAGMA foreign_key_check(c5);
  }
} {c5 1 p1 0 c5 3 p1 0}
do_test fkey5-2.2 {
  db eval {
    PRAGMA foreign_key_check(c1);
  }
} {}

do_test fkey5-3.0 {
  db eval {
    INSERT INTO c9 SELECT x FROM c5;
    DELETE FROM c5;
    PRAGMA foreign_key_check;
  }
} {c9 1 p1 0 c9 3 p1 0}
do_test fkey5-3.1 {
  db eval {
    PRAGMA foreign_key_check(c9);
  }
} {c9 1 p1 0 c9 3 p1 0}
do_test fkey5-3.2 {
  db eval {
    PRAGMA foreign_key_check(c5);
  }
} {}

do_test fkey5-4.0 {
  db eval {
    DELETE FROM c9;
    INSERT INTO c2 VALUES(79),(77),(76);
    PRAGMA foreign_key_check;
  }
} {c2 76 p2 0 c2 79 p2 0}
do_test fkey5-4.1 {
  db eval {
    PRAGMA foreign_key_check(c2);
  }
} {c2 76 p2 0 c2 79 p2 0}
do_test fkey5-4.2 {
  db eval {
    INSERT INTO c6 SELECT x FROM c2;
    DELETE FROM c2;
    PRAGMA foreign_key_check;
  }
} {c6 1 p2 0 c6 3 p2 0}
do_test fkey5-4.3 {
  db eval {
    PRAGMA foreign_key_check(c6);
  }
} {c6 1 p2 0 c6 3 p2 0}
do_test fkey5-4.4 {
  db eval {
    INSERT INTO c10 SELECT x FROM c6;
    DELETE FROM c6;
    PRAGMA foreign_key_check;
  }
} {c10 1 p2 0 c10 3 p2 0}
do_test fkey5-4.5 {
  db eval {
    PRAGMA foreign_key_check(c10);
  }
} {c10 1 p2 0 c10 3 p2 0}

do_test fkey5-5.0 {
  db eval {
    DELETE FROM c10;
    INSERT INTO c3 VALUES(68),(67),(65);
    PRAGMA foreign_key_check;
  }
} {c3 65 p3 0 c3 68 p3 0}
do_test fkey5-5.1 {
  db eval {
    PRAGMA foreign_key_check(c3);
  }
} {c3 65 p3 0 c3 68 p3 0}
do_test fkey5-5.2 {
  db eval {
    INSERT INTO c7 SELECT x FROM c3;
    INSERT INTO c7 VALUES('Alpha'),('alpha'),('foxtrot');
    DELETE FROM c3;
    PRAGMA foreign_key_check;
  }
} {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0}
do_test fkey5-5.3 {
  db eval {
    PRAGMA foreign_key_check(c7);
  }
} {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0}
do_test fkey5-5.4 {
  db eval {
    INSERT INTO c11 SELECT x FROM c7;
    DELETE FROM c7;
    PRAGMA foreign_key_check;
  }
} {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0}
do_test fkey5-5.5 {
  db eval {
    PRAGMA foreign_key_check(c11);
  }
} {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0}

do_test fkey5-6.0 {
  db eval {
    DELETE FROM c11;
    INSERT INTO c4 VALUES(54),(55),(56);
    PRAGMA foreign_key_check;
  }
} {c4 54 p4 0 c4 56 p4 0}
do_test fkey5-6.1 {
  db eval {
    PRAGMA foreign_key_check(c4);
  }
} {c4 54 p4 0 c4 56 p4 0}
do_test fkey5-6.2 {
  db eval {
    INSERT INTO c8 SELECT x FROM c4;
    INSERT INTO c8 VALUES('Alpha'),('ALPHA'),('foxtrot');
    DELETE FROM c4;
    PRAGMA foreign_key_check;
  }
} {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0}
do_test fkey5-6.3 {
  db eval {
    PRAGMA foreign_key_check(c8);
  }
} {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0}
do_test fkey5-6.4 {
  db eval {
    INSERT INTO c12 SELECT x FROM c8;
    DELETE FROM c8;
    PRAGMA foreign_key_check;
  }
} {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0}
do_test fkey5-6.5 {
  db eval {
    PRAGMA foreign_key_check(c12);
  }
} {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0}

do_test fkey5-7.1 {
  db eval {
    INSERT OR IGNORE INTO c13 SELECT * FROM c12;
    INSERT OR IGNORE INTO C14 SELECT * FROM c12;
    DELETE FROM c12;
    PRAGMA foreign_key_check;
  }
} {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}
do_test fkey5-7.2 {
  db eval {
    PRAGMA foreign_key_check(c14);
  }
} {c14 1 p4 0 c14 3 p4 0 c14 6 p4 0}
do_test fkey5-7.3 {
  db eval {
    PRAGMA foreign_key_check(c13);
  }
} {c13 1 p3 0 c13 2 p3 0 c13 3 p3 0 c13 4 p3 0 c13 5 p3 0 c13 6 p3 0}

do_test fkey5-8.0 {
  db eval {
    DELETE FROM c13;
    DELETE FROM c14;
    INSERT INTO c19 VALUES('alpha','abc');
    PRAGMA foreign_key_check(c19);
  }
} {c19 1 p5 0}
do_test fkey5-8.1 {
  db eval {
    DELETE FROM c19;
    INSERT INTO c19 VALUES('Alpha','abc');
    PRAGMA foreign_key_check(c19);
  }
} {}
do_test fkey5-8.2 {
  db eval {
    INSERT INTO c20 VALUES('Alpha','abc');
    PRAGMA foreign_key_check(c20);
  }
} {c20 1 p5 0}
do_test fkey5-8.3 {
  db eval {
    DELETE FROM c20;
    INSERT INTO c20 VALUES('abc','Alpha');
    PRAGMA foreign_key_check(c20);
  }
} {}
do_test fkey5-8.4 {
  db eval {
    INSERT INTO c21 VALUES('alpha','abc    ');
    PRAGMA foreign_key_check(c21);
  }
} {}
do_test fkey5-8.5 {
  db eval {
    DELETE FROM c21;
    INSERT INTO c19 VALUES('Alpha','abc');
    PRAGMA foreign_key_check(c21);
  }
} {}
do_test fkey5-8.6 {
  db eval {
    INSERT INTO c22 VALUES('Alpha','abc');
    PRAGMA foreign_key_check(c22);
  }
} {c22 1 p6 0}
do_test fkey5-8.7 {
  db eval {
    DELETE FROM c22;
    INSERT INTO c22 VALUES('abc  ','ALPHA');
    PRAGMA foreign_key_check(c22);
  }
} {}



finish_test

  CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
  CREATE TABLE t2(x REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE);
} -sqlbody {
  INSERT INTO t1 VALUES('aaa', 1);
  INSERT INTO t2 VALUES('aaa');
  UPDATE t1 SET a = 'bbb';
  DELETE FROM t1;
  PRAGMA foreign_key_check;
}

do_malloc_test fkey_malloc-2 -sqlprep {
  PRAGMA foreign_keys = 1;
  CREATE TABLE t1(a, b, UNIQUE(a, b));
} -sqlbody {
  CREATE TABLE t2(x, y, 

  CREATE TABLE z(e, f, FOREIGN KEY(e, f) REFERENCES x);
} -sqlbody {
  DROP TABLE y;
  DROP TABLE x;
}

finish_test