SQLite

  assert( pKey!=0 || db->mallocFailed || pParse->nErr );
  if( pIndex->onError!=OE_None && pKey!=0 ){
    int j2 = sqlite3VdbeCurrentAddr(v) + 3;
    sqlite3VdbeAddOp2(v, OP_Goto, 0, j2);
    addr2 = sqlite3VdbeCurrentAddr(v);
    sqlite3VdbeAddOp4Int(v, OP_SorterCompare, iSorter, j2, regRecord,
                         pKey->nField - pIndex->nKeyCol);
    sqlite3UniqueConstraint(pParse, OE_Abort, pIndex);


  }else{
    addr2 = sqlite3VdbeCurrentAddr(v);
  }
  sqlite3VdbeAddOp2(v, OP_SorterData, iSorter, regRecord);
  sqlite3VdbeAddOp3(v, OP_IdxInsert, iIdx, regRecord, 1);
  sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
  sqlite3ReleaseTempReg(pParse, regRecord);

** and/or current transaction is rolled back.
*/
void sqlite3HaltConstraint(
  Parse *pParse,    /* Parsing context */
  int errCode,      /* extended error code */
  int onError,      /* Constraint type */
  char *p4,         /* Error message */
  i8 p4type,        /* P4_STATIC or P4_TRANSIENT */
  u8 p5Errmsg       /* P5_ErrMsg type */
){
  Vdbe *v = sqlite3GetVdbe(pParse);
  assert( (errCode&0xff)==SQLITE_CONSTRAINT );
  if( onError==OE_Abort ){
    sqlite3MayAbort(pParse);
  }
  sqlite3VdbeAddOp4(v, OP_Halt, errCode, onError, 0, p4, p4type);
  if( p5Errmsg ) sqlite3VdbeChangeP5(v, p5Errmsg);
}

/*
** Code an OP_Halt due to UNIQUE or PRIMARY KEY constraint violation.
*/
void sqlite3UniqueConstraint(
  Parse *pParse,    /* Parsing context */
  int onError,      /* Constraint type */
  Index *pIdx       /* The index that triggers the constraint */
){
  char *zErr;
  int j;
  StrAccum errMsg;
  Table *pTab = pIdx->pTable;

  sqlite3StrAccumInit(&errMsg, 0, 0, 200);
  errMsg.db = pParse->db;
  for(j=0; j<pIdx->nKeyCol; j++){
    char *zCol = pTab->aCol[pIdx->aiColumn[j]].zName;
    if( j ) sqlite3StrAccumAppend(&errMsg, ", ", 2);
    sqlite3StrAccumAppend(&errMsg, pTab->zName, -1);
    sqlite3StrAccumAppend(&errMsg, ".", 1);
    sqlite3StrAccumAppend(&errMsg, zCol, -1);
  }
  zErr = sqlite3StrAccumFinish(&errMsg);
  sqlite3HaltConstraint(pParse, 
    (pIdx->autoIndex==2)?SQLITE_CONSTRAINT_PRIMARYKEY:SQLITE_CONSTRAINT_UNIQUE,
    onError, zErr, 0, P5_ConstraintUnique);
  sqlite3DbFree(errMsg.db, zErr);
}


/*
** Code an OP_Halt due to non-unique rowid.
*/
void sqlite3RowidConstraint(
  Parse *pParse,    /* Parsing context */
  int onError,      /* Conflict resolution algorithm */
  Table *pTab       /* The table with the non-unique rowid */ 
){
  char *zMsg;
  int rc;
  if( pTab->iPKey>=0 ){
    zMsg = sqlite3MPrintf(pParse->db, "%s.%s", pTab->zName,
                          pTab->aCol[pTab->iPKey].zName);
    rc = SQLITE_CONSTRAINT_PRIMARYKEY;
  }else{
    zMsg = sqlite3MPrintf(pParse->db, "%s.rowid", pTab->zName);
    rc = SQLITE_CONSTRAINT_ROWID;
  }
  sqlite3HaltConstraint(pParse, rc, onError, zMsg, P4_DYNAMIC,
                        P5_ConstraintUnique);
}

/*
** Check to see if pIndex uses the collating sequence pColl.  Return
** true if it does and false if it does not.
*/
#ifndef SQLITE_OMIT_REINDEX

      }
      assert( !ExprHasProperty(pExpr, EP_IntValue) );
      if( pExpr->affinity==OE_Ignore ){
        sqlite3VdbeAddOp4(
            v, OP_Halt, SQLITE_OK, OE_Ignore, 0, pExpr->u.zToken,0);
      }else{
        sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_TRIGGER,
                              pExpr->affinity, pExpr->u.zToken, 0, 0);
      }

      break;
    }
#endif
  }
  sqlite3ReleaseTempReg(pParse, regFree1);

  ){
    /* Special case: If this is an INSERT statement that will insert exactly
    ** one row into the table, raise a constraint immediately instead of
    ** incrementing a counter. This is necessary as the VM code is being
    ** generated for will not open a statement transaction.  */
    assert( nIncr==1 );
    sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY,
        OE_Abort, 0, P4_STATIC, P5_ConstraintFK);

  }else{
    if( nIncr>0 && pFKey->isDeferred==0 ){
      sqlite3ParseToplevel(pParse)->mayAbort = 1;
    }
    sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
  }

** table that correspond to the parent table row being deleted or inserted.
** For each child row found, one of the following actions is taken:
**
**   Operation | FK type   | Action taken
**   --------------------------------------------------------------------------
**   DELETE      immediate   Increment the "immediate constraint counter".
**                           Or, if the ON (UPDATE|DELETE) action is RESTRICT,
**                           throw a "FOREIGN KEY constraint failed" exception.
**
**   INSERT      immediate   Decrement the "immediate constraint counter".
**
**   DELETE      deferred    Increment the "deferred constraint counter".
**                           Or, if the ON (UPDATE|DELETE) action is RESTRICT,
**                           throw a "FOREIGN KEY constraint failed" exception.
**
**   INSERT      deferred    Decrement the "deferred constraint counter".
**
** These operations are identified in the comment at the top of this file 
** (fkey.c) as "I.2" and "D.2".
*/
static void fkScanChildren(

    ** If the SQLITE_DeferFKs flag is set, then this is not required, as
    ** the statement transaction will not be rolled back even if FK
    ** constraints are violated.
    */
    if( (db->flags & SQLITE_DeferFKs)==0 ){
      sqlite3VdbeAddOp2(v, OP_FkIfZero, 0, sqlite3VdbeCurrentAddr(v)+2);
      sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY,
          OE_Abort, 0, P4_STATIC, P5_ConstraintFK);

    }

    if( iSkip ){
      sqlite3VdbeResolveLabel(v, iSkip);
    }
  }
}

    if( action==OE_Restrict ){
      Token tFrom;
      Expr *pRaise; 

      tFrom.z = zFrom;
      tFrom.n = nFrom;
      pRaise = sqlite3Expr(db, TK_RAISE, "FOREIGN KEY constraint failed");
      if( pRaise ){
        pRaise->affinity = OE_Abort;
      }
      pSelect = sqlite3SelectNew(pParse, 
          sqlite3ExprListAppend(pParse, 0, pRaise),
          sqlite3SrcListAppend(db, 0, &tFrom, 0),
          pWhere,

        || onError==OE_Ignore || onError==OE_Replace );
    switch( onError ){
      case OE_Abort:
        sqlite3MayAbort(pParse);
        /* Fall through */
      case OE_Rollback:
      case OE_Fail: {



        char *zMsg = sqlite3MPrintf(db, "%s.%s", pTab->zName,
                                    pTab->aCol[i].zName);
        sqlite3VdbeAddOp4(v, OP_HaltIfNull, SQLITE_CONSTRAINT_NOTNULL, onError,
                          regNewData+1+i, zMsg, P4_DYNAMIC);
        sqlite3VdbeChangeP5(v, P5_ConstraintNotNull);
        break;
      }
      case OE_Ignore: {
        sqlite3VdbeAddOp2(v, OP_IsNull, regNewData+1+i, ignoreDest);
        break;
      }
      default: {

    onError = overrideError!=OE_Default ? overrideError : OE_Abort;
    for(i=0; i<pCheck->nExpr; i++){
      int allOk = sqlite3VdbeMakeLabel(v);
      sqlite3ExprIfTrue(pParse, pCheck->a[i].pExpr, allOk, SQLITE_JUMPIFNULL);
      if( onError==OE_Ignore ){
        sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest);
      }else{
        char *zName = pCheck->a[i].zName;
        if( zName==0 ) zName = pTab->zName;
        if( onError==OE_Replace ) onError = OE_Abort; /* IMP: R-15569-63625 */





        sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_CHECK,
                              onError, zName, P4_TRANSIENT,
                              P5_ConstraintCheck);
      }
      sqlite3VdbeResolveLabel(v, allOk);
    }
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

  /* If rowid is changing, make sure the new rowid does not previously

      default: {
        onError = OE_Abort;
        /* Fall thru into the next case */
      }
      case OE_Rollback:
      case OE_Abort:
      case OE_Fail: {
        sqlite3RowidConstraint(pParse, onError, pTab);

        break;
      }
      case OE_Replace: {
        /* If there are DELETE triggers on this table and the
        ** recursive-triggers flag is set, call GenerateRowDelete() to
        ** remove the conflicting row from the table. This will fire
        ** the triggers and remove both the table and index b-tree entries.

    /* Generate code that executes if the new index entry is not unique */
    assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
        || onError==OE_Ignore || onError==OE_Replace );
    switch( onError ){
      case OE_Rollback:
      case OE_Abort:
      case OE_Fail: {





















        sqlite3UniqueConstraint(pParse, onError, pIdx);



        break;
      }
      case OE_Ignore: {
        assert( seenReplace==0 );
        sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest);
        break;
      }

      emptyDestTest = 0;
    }
    sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead);
    emptySrcTest = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0);
    if( pDest->iPKey>=0 ){
      addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
      addr2 = sqlite3VdbeAddOp3(v, OP_NotExists, iDest, 0, regRowid);
      sqlite3RowidConstraint(pParse, onError, pDest);

      sqlite3VdbeJumpHere(v, addr2);
      autoIncStep(pParse, regAutoinc, regRowid);
    }else if( pDest->pIndex==0 ){
      addr1 = sqlite3VdbeAddOp2(v, OP_NewRowid, iDest, regRowid);
    }else{
      addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
      assert( (pDest->tabFlags & TF_Autoincrement)==0 );

                                zName = "SQLITE_CONSTRAINT_PRIMARYKEY";   break;
      case SQLITE_CONSTRAINT_NOTNULL: zName = "SQLITE_CONSTRAINT_NOTNULL";break;
      case SQLITE_CONSTRAINT_COMMITHOOK:
                                zName = "SQLITE_CONSTRAINT_COMMITHOOK";   break;
      case SQLITE_CONSTRAINT_VTAB:    zName = "SQLITE_CONSTRAINT_VTAB";   break;
      case SQLITE_CONSTRAINT_FUNCTION:
                                zName = "SQLITE_CONSTRAINT_FUNCTION";     break;
      case SQLITE_CONSTRAINT_ROWID:   zName = "SQLITE_CONSTRAINT_ROWID";  break;
      case SQLITE_MISMATCH:           zName = "SQLITE_MISMATCH";          break;
      case SQLITE_MISUSE:             zName = "SQLITE_MISUSE";            break;
      case SQLITE_NOLFS:              zName = "SQLITE_NOLFS";             break;
      case SQLITE_AUTH:               zName = "SQLITE_AUTH";              break;
      case SQLITE_FORMAT:             zName = "SQLITE_FORMAT";            break;
      case SQLITE_RANGE:              zName = "SQLITE_RANGE";             break;
      case SQLITE_NOTADB:             zName = "SQLITE_NOTADB";            break;

#define SQLITE_CONSTRAINT_FOREIGNKEY   (SQLITE_CONSTRAINT | (3<<8))
#define SQLITE_CONSTRAINT_FUNCTION     (SQLITE_CONSTRAINT | (4<<8))
#define SQLITE_CONSTRAINT_NOTNULL      (SQLITE_CONSTRAINT | (5<<8))
#define SQLITE_CONSTRAINT_PRIMARYKEY   (SQLITE_CONSTRAINT | (6<<8))
#define SQLITE_CONSTRAINT_TRIGGER      (SQLITE_CONSTRAINT | (7<<8))
#define SQLITE_CONSTRAINT_UNIQUE       (SQLITE_CONSTRAINT | (8<<8))
#define SQLITE_CONSTRAINT_VTAB         (SQLITE_CONSTRAINT | (9<<8))
#define SQLITE_CONSTRAINT_ROWID        (SQLITE_CONSTRAINT |(10<<8))
#define SQLITE_NOTICE_RECOVER_WAL      (SQLITE_NOTICE | (1<<8))
#define SQLITE_NOTICE_RECOVER_ROLLBACK (SQLITE_NOTICE | (2<<8))
#define SQLITE_WARNING_AUTOINDEX       (SQLITE_WARNING | (1<<8))

/*
** CAPI3REF: Flags For File Open Operations
**

void sqlite3GenerateConstraintChecks(Parse*,Table*,int*,int,int,int,int,
                                     u8,u8,int,int*);
void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int*,int,int,int);
int sqlite3OpenTableAndIndices(Parse*, Table*, int, int, int*, int*);
void sqlite3BeginWriteOperation(Parse*, int, int);
void sqlite3MultiWrite(Parse*);
void sqlite3MayAbort(Parse*);
void sqlite3HaltConstraint(Parse*, int, int, char*, i8, u8);
void sqlite3UniqueConstraint(Parse*, int, Index*);
void sqlite3RowidConstraint(Parse*, int, Table*);
Expr *sqlite3ExprDup(sqlite3*,Expr*,int);
ExprList *sqlite3ExprListDup(sqlite3*,ExprList*,int);
SrcList *sqlite3SrcListDup(sqlite3*,SrcList*,int);
IdList *sqlite3IdListDup(sqlite3*,IdList*);
Select *sqlite3SelectDup(sqlite3*,Select*,int);
void sqlite3FuncDefInsert(FuncDefHash*, FuncDef*);
FuncDef *sqlite3FindFunction(sqlite3*,const char*,int,int,u8,u8);

  pcDest = (int)pIn1->u.i;
  pIn1->u.i = pc;
  REGISTER_TRACE(pOp->p1, pIn1);
  pc = pcDest;
  break;
}

/* Opcode:  HaltIfNull  P1 P2 P3 P4 P5
** Synopsis:  if r[P3] null then halt
**
** Check the value in register P3.  If it is NULL then Halt using
** parameter P1, P2, and P4 as if this were a Halt instruction.  If the
** value in register P3 is not NULL, then this routine is a no-op.
** The P5 parameter should be 1.
*/
case OP_HaltIfNull: {      /* in3 */
  pIn3 = &aMem[pOp->p3];
  if( (pIn3->flags & MEM_Null)==0 ) break;
  /* Fall through into OP_Halt */
}

/* Opcode:  Halt P1 P2 * P4 P5
**
** Exit immediately.  All open cursors, etc are closed
** automatically.
**
** P1 is the result code returned by sqlite3_exec(), sqlite3_reset(),
** or sqlite3_finalize().  For a normal halt, this should be SQLITE_OK (0).
** For errors, it can be some other value.  If P1!=0 then P2 will determine
** whether or not to rollback the current transaction.  Do not rollback
** if P2==OE_Fail. Do the rollback if P2==OE_Rollback.  If P2==OE_Abort,
** then back out all changes that have occurred during this execution of the
** VDBE, but do not rollback the transaction. 
**
** If P4 is not null then it is an error message string.
**
** P5 is a value between 0 and 4, inclusive, that modifies the P4 string.
**
**    0:  (no change)
**    1:  NOT NULL contraint failed: P4
**    2:  UNIQUE constraint failed: P4
**    3:  CHECK constraint failed: P4
**    4:  FOREIGN KEY constraint failed: P4
**
** If P5 is not zero and P4 is NULL, then everything after the ":" is
** omitted.
**
** There is an implied "Halt 0 0 0" instruction inserted at the very end of
** every program.  So a jump past the last instruction of the program
** is the same as executing Halt.
*/
case OP_Halt: {
  const char *zType;
  const char *zLogFmt;

  if( pOp->p1==SQLITE_OK && p->pFrame ){
    /* Halt the sub-program. Return control to the parent frame. */
    VdbeFrame *pFrame = p->pFrame;
    p->pFrame = pFrame->pParent;
    p->nFrame--;
    sqlite3VdbeSetChanges(db, p->nChange);
    pc = sqlite3VdbeFrameRestore(pFrame);
    lastRowid = db->lastRowid;
    if( pOp->p2==OE_Ignore ){
      /* Instruction pc is the OP_Program that invoked the sub-program 
      ** currently being halted. If the p2 instruction of this OP_Halt
      ** instruction is set to OE_Ignore, then the sub-program is throwing
      ** an IGNORE exception. In this case jump to the address specified
      ** as the p2 of the calling OP_Program.  */
      pc = p->aOp[pc].p2-1;
    }
    aOp = p->aOp;
    aMem = p->aMem;
    break;
  }
  if( pOp->p5 ){
    static const char * const azType[] = { "NOT NULL", "UNIQUE", "CHECK",
                                           "FOREIGN KEY" };
    assert( pOp->p5>=1 && pOp->p5<=4 );
    testcase( pOp->p5==1 );
    testcase( pOp->p5==2 );
    testcase( pOp->p5==3 );
    testcase( pOp->p5==4 );
    zType = azType[pOp->p5-1];
  }else{
    zType = 0;
  }
  p->rc = pOp->p1;
  p->errorAction = (u8)pOp->p2;
  p->pc = pc;
  if( p->rc ){
    zLogFmt = "abort at %d in [%s]: %s";
    if( zType && pOp->p4.z ){
      sqlite3SetString(&p->zErrMsg, db, "%s constraint failed: %s", 
                       zType, pOp->p4.z);
    }else if( pOp->p4.z ){

      sqlite3SetString(&p->zErrMsg, db, "%s", pOp->p4.z);


    }else if( zType ){
      sqlite3SetString(&p->zErrMsg, db, "%s constraint failed", zType);
    }else{
      zLogFmt = "abort at %d in [%s]";
    }
    sqlite3_log(pOp->p1, zLogFmt, pc, p->zSql, p->zErrMsg);
  }
  rc = sqlite3VdbeHalt(p);
  assert( rc==SQLITE_BUSY || rc==SQLITE_OK || rc==SQLITE_ERROR );
  if( rc==SQLITE_BUSY ){
    p->rc = rc = SQLITE_BUSY;
  }else{
    assert( rc==SQLITE_OK || (p->rc&0xff)==SQLITE_CONSTRAINT );

** argument is P4_KEYINFO_HANDOFF, the passed in pointer is used.  It still
** gets freed when the Vdbe is finalized so it still should be obtained
** from a single sqliteMalloc().  But no copy is made and the calling
** function should *not* try to free the KeyInfo.
*/
#define P4_KEYINFO_HANDOFF (-16)
#define P4_KEYINFO_STATIC  (-17)

/* Error message codes for OP_Halt */
#define P5_ConstraintNotNull 1
#define P5_ConstraintUnique  2
#define P5_ConstraintCheck   3
#define P5_ConstraintFK      4

/*
** The Vdbe.aColName array contains 5n Mem structures, where n is the 
** number of columns of data returned by the statement.
*/
#define COLNAME_NAME     0
#define COLNAME_DECLTYPE 1

int sqlite3VdbeCheckFk(Vdbe *p, int deferred){
  sqlite3 *db = p->db;
  if( (deferred && (db->nDeferredCons+db->nDeferredImmCons)>0) 
   || (!deferred && p->nFkConstraint>0) 
  ){
    p->rc = SQLITE_CONSTRAINT_FOREIGNKEY;
    p->errorAction = OE_Abort;
    sqlite3SetString(&p->zErrMsg, db, "FOREIGN KEY constraint failed");
    return SQLITE_ERROR;
  }
  return SQLITE_OK;
}
#endif

/*

    SELECT sum(a) FROM av1;
  }
} {5049}
do_test autovacuum-4.2 {
  catchsql {
    CREATE UNIQUE INDEX av1_i ON av1(a);
  }
} {1 {UNIQUE constraint failed: av1.a}}
do_test autovacuum-4.3 {
  execsql {
    SELECT sum(a) FROM av1;
  }
} {5049}
do_test autovacuum-4.4 {
  execsql {

# (Test result changes from 0 to 1).  (Later:) change counter updates occur
# when sqlite3_step returns, not at finalize time.
do_test capi2-3.13b {db changes} {0}

do_test capi2-3.14 {
  list [sqlite3_finalize $VM] [sqlite3_errmsg $DB] \
       [sqlite3_extended_errcode $DB]
} {SQLITE_CONSTRAINT {UNIQUE constraint failed: t1.a} SQLITE_CONSTRAINT_UNIQUE}
do_test capi2-3.15 {
  set VM [sqlite3_prepare $DB {CREATE TABLE t2(a NOT NULL, b)} -1 TAIL]
  set TAIL
} {}
do_test capi2-3.16 {
  list [sqlite3_step $VM] \
       [sqlite3_column_count $VM] \

       [sqlite3_column_count $VM] \
       [get_row_values $VM] \
       [get_column_names $VM]
} {SQLITE_ERROR 0 {} {}}
do_test capi2-3.19 {
  list [sqlite3_finalize $VM] [sqlite3_errmsg $DB] \
       [sqlite3_extended_errcode $DB]
} {SQLITE_CONSTRAINT {NOT NULL constraint failed: t2.a} SQLITE_CONSTRAINT_NOTNULL}

do_test capi2-3.20 {
  execsql {
    CREATE TABLE a1(message_id, name , UNIQUE(message_id, name) );
    INSERT INTO a1 VALUES(1, 1);
  }
} {}

       [get_column_names $VM1]
} {SQLITE_ROW 1 12 {x counter}}
do_test capi2-6.27 {
  catchsql {
    INSERT INTO t1 VALUES(2,4,5);
    SELECT * FROM t1;
  }
} {1 {UNIQUE constraint failed: t1.a}}
do_test capi2-6.28 {
  list [sqlite3_step $VM1] \
       [sqlite3_column_count $VM1] \
       [get_row_values $VM1] \
       [get_column_names $VM1]
} {SQLITE_ROW 1 13 {x counter}}
do_test capi2-6.99 {

    SELECT * FROM t1;
  }  
} {3 4.0}
do_test check-1.3 {
  catchsql {
    INSERT INTO t1 VALUES(6,7);
  }
} {1 {CHECK constraint failed: t1}}
do_test check-1.4 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4.0}
do_test check-1.5 {
  catchsql {
    INSERT INTO t1 VALUES(4,3);
  }
} {1 {CHECK constraint failed: t1}}
do_test check-1.6 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4.0}
do_test check-1.7 {
  catchsql {

    SELECT * FROM t1;
  }
} {2 4.0}
do_test check-1.12 {
  catchsql {
    UPDATE t1 SET x=7 WHERE x==2
  }
} {1 {CHECK constraint failed: t1}}
do_test check-1.13 {
  execsql {
    SELECT * FROM t1;
  }
} {2 4.0}
do_test check-1.14 {
  catchsql {
    UPDATE t1 SET x=5 WHERE x==2
  }
} {1 {CHECK constraint failed: t1}}
do_test check-1.15 {
  execsql {
    SELECT * FROM t1;
  }
} {2 4.0}
do_test check-1.16 {
  catchsql {

    SELECT * FROM t2;
  }
} {1 2.2 three {} {} {}}
do_test check-2.4 {
  catchsql {
    INSERT INTO t2 VALUES(1.1, NULL, NULL);
  }
} {1 {CHECK constraint failed: one}}
do_test check-2.5 {
  catchsql {
    INSERT INTO t2 VALUES(NULL, 5, NULL);
  }
} {1 {CHECK constraint failed: two}}
do_test check-2.6 {
  catchsql {
    INSERT INTO t2 VALUES(NULL, NULL, 3.14159);
  }
} {1 {CHECK constraint failed: three}}

# Undocumented behavior:  The CONSTRAINT name clause can follow a constraint.
# Such a clause is ignored.  But the parser must accept it for backwards
# compatibility.
#
do_test check-2.10 {
  execsql {
    CREATE TABLE t2b(
      x INTEGER CHECK( typeof(coalesce(x,0))=='integer' ) CONSTRAINT one,
      y TEXT PRIMARY KEY constraint two,
      z INTEGER,
      UNIQUE(x,z) constraint three
    );
  }
} {}
do_test check-2.11 {
  catchsql {
    INSERT INTO t2b VALUES('xyzzy','hi',5);
  }
} {1 {CHECK constraint failed: t2b}}
do_test check-2.12 {
  execsql {
    CREATE TABLE t2c(
      x INTEGER CONSTRAINT x_one CONSTRAINT x_two
          CHECK( typeof(coalesce(x,0))=='integer' )
          CONSTRAINT x_two CONSTRAINT x_three,
      y INTEGER, z INTEGER,
      CONSTRAINT u_one UNIQUE(x,y,z) CONSTRAINT u_two
    );
  }
} {}
do_test check-2.13 {
  catchsql {
    INSERT INTO t2c VALUES('xyzzy',7,8);
  }
} {1 {CHECK constraint failed: x_two}}
do_test check-2.cleanup {
  execsql {
    DROP TABLE IF EXISTS t2b;
    DROP TABLE IF EXISTS t2c;
  }
} {}

    SELECT * FROM t3;
  }
} {1 2 3}
do_test check-3.9 {
  catchsql {
    INSERT INTO t3 VALUES(111,222,333);
  }
} {1 {CHECK constraint failed: t3}}

do_test check-4.1 {
  execsql {
    CREATE TABLE t4(x, y,
      CHECK (
           x+y==11
        OR x*y==12

    SELECT * FROM t4
  }
} {12 -22}
do_test check-4.6 {
  catchsql {
    UPDATE t4 SET x=0, y=1;
  }
} {1 {CHECK constraint failed: t4}}
do_test check-4.7 {
  execsql {
    SELECT * FROM t4;
  }
} {12 -22}
do_test check-4.8 {
  execsql {
    PRAGMA ignore_check_constraints=ON;
    UPDATE t4 SET x=0, y=1;
    SELECT * FROM t4;
  }
} {0 1}
do_test check-4.9 {
  catchsql {
    PRAGMA ignore_check_constraints=OFF;
    UPDATE t4 SET x=0, y=2;
  }
} {1 {CHECK constraint failed: t4}}
ifcapable vacuum {
  do_test check_4.10 {
    catchsql {
      VACUUM
    }
  } {0 {}}
}

    SELECT * FROM t1;
  }
} {4 11.0 2 20.0}
do_test check-6.5 {
  catchsql {
    UPDATE OR FAIL t1 SET x=7-x, y=y+1;
  }
} {1 {CHECK constraint failed: t1}}
do_test check-6.6 {
  execsql {
    SELECT * FROM t1;
  }
} {3 12.0 2 20.0}
do_test check-6.7 {
  catchsql {
    BEGIN;
    INSERT INTO t1 VALUES(1,30.0);
    INSERT OR ROLLBACK INTO t1 VALUES(8,40.0);
  }
} {1 {CHECK constraint failed: t1}}
do_test check-6.8 {
  catchsql {
    COMMIT;
  }
} {1 {cannot commit - no transaction is active}}
do_test check-6.9 {
  execsql {
    SELECT * FROM t1
  }
} {3 12.0 2 20.0}

do_test check-6.11 {
  execsql {SELECT * FROM t1}
} {3 12.0 2 20.0}
do_test check-6.12 {
  catchsql {
    REPLACE INTO t1 VALUES(6,7);
  }
} {1 {CHECK constraint failed: t1}}
do_test check-6.13 {
  execsql {SELECT * FROM t1}
} {3 12.0 2 20.0}
do_test check-6.14 {
  catchsql {
    INSERT OR IGNORE INTO t1 VALUES(6,7);
  }

#
reset_db
proc myfunc {x} {expr $x < 10}
db func myfunc myfunc

do_execsql_test  7.1 { CREATE TABLE t6(a CHECK (myfunc(a))) }
do_execsql_test  7.2 { INSERT INTO t6 VALUES(9)  }
do_catchsql_test 7.3 { INSERT INTO t6 VALUES(11) } \
          {1 {CHECK constraint failed: t6}}

do_test 7.4 {
  sqlite3 db2 test.db
  execsql { SELECT * FROM t6 } db2 
} {9}

do_test 7.5 {

  db2 func myfunc myfunc
  execsql { INSERT INTO t6 VALUES(8) } db2
} {}

do_test 7.8 {
  db2 func myfunc myfunc
  catchsql { INSERT INTO t6 VALUES(12) } db2
} {1 {CHECK constraint failed: t6}}

# 2013-08-02:  Silently ignore database name qualifiers in CHECK constraints.
#
do_execsql_test 8.1 {
  CREATE TABLE t810(a, CHECK( main.t810.a>0 ));
  CREATE TABLE t811(b, CHECK( xyzzy.t811.b BETWEEN 5 AND 10 ));
} {}

finish_test

  }
} {}
do_test collate4-3.1 {
  catchsql {
    INSERT INTO collate4t1 VALUES('abc');
    INSERT INTO collate4t1 VALUES('ABC');
  }
} {1 {UNIQUE constraint failed: collate4t1.a}}
do_test collate4-3.2 {
  execsql {
    SELECT * FROM collate4t1;
  }
} {abc}
do_test collate4-3.3 {
  catchsql {
    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
  }
} {1 {UNIQUE constraint failed: collate4t1.a}}
do_test collate4-3.4 {
  catchsql {
    INSERT INTO collate4t1 VALUES(1);
    UPDATE collate4t1 SET a = 'abc';
  }
} {1 {UNIQUE constraint failed: collate4t1.a}}
do_test collate4-3.5 {
  execsql {
    DROP TABLE collate4t1;
    CREATE TABLE collate4t1(a COLLATE NOCASE UNIQUE);
  }
} {}
do_test collate4-3.6 {
  catchsql {
    INSERT INTO collate4t1 VALUES('abc');
    INSERT INTO collate4t1 VALUES('ABC');
  }
} {1 {UNIQUE constraint failed: collate4t1.a}}
do_test collate4-3.7 {
  execsql {
    SELECT * FROM collate4t1;
  }
} {abc}
do_test collate4-3.8 {
  catchsql {
    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
  }
} {1 {UNIQUE constraint failed: collate4t1.a}}
do_test collate4-3.9 {
  catchsql {
    INSERT INTO collate4t1 VALUES(1);
    UPDATE collate4t1 SET a = 'abc';
  }
} {1 {UNIQUE constraint failed: collate4t1.a}}
do_test collate4-3.10 {
  execsql {
    DROP TABLE collate4t1;
    CREATE TABLE collate4t1(a);
    CREATE UNIQUE INDEX collate4i1 ON collate4t1(a COLLATE NOCASE);
  }
} {}
do_test collate4-3.11 {
  catchsql {
    INSERT INTO collate4t1 VALUES('abc');
    INSERT INTO collate4t1 VALUES('ABC');
  }
} {1 {UNIQUE constraint failed: collate4t1.a}}
do_test collate4-3.12 {
  execsql {
    SELECT * FROM collate4t1;
  }
} {abc}
do_test collate4-3.13 {
  catchsql {
    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
  }
} {1 {UNIQUE constraint failed: collate4t1.a}}
do_test collate4-3.14 {
  catchsql {
    INSERT INTO collate4t1 VALUES(1);
    UPDATE collate4t1 SET a = 'abc';
  }
} {1 {UNIQUE constraint failed: collate4t1.a}}

do_test collate4-3.15 {
  execsql {
    DROP TABLE collate4t1;
  }
} {}

 11 ROLLBACK {INSERT OR IGNORE}      0 {}  1
 12 {}       {INSERT OR IGNORE}      0 {}  1
 13 {}       {INSERT OR REPLACE}     0 5   1
 14 {}       {INSERT OR FAIL}        1 {}  1
 15 {}       {INSERT OR ABORT}       1 {}  1
 16 {}       {INSERT OR ROLLBACK}    1 {}  {}
} {
  if {$t0} {set t1 {NOT NULL constraint failed: t1.c}}
  do_test conflict-5.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
      CREATE TABLE t1(a,b,c NOT NULL $conf1 DEFAULT 5);
      DELETE FROM t2;
      BEGIN;

 11 ROLLBACK {UPDATE OR IGNORE}      0 {6 7 3 9}  1 0 0
 12 {}       {UPDATE OR IGNORE}      0 {6 7 3 9}  1 0 0
 13 {}       {UPDATE OR REPLACE}     0 {7 6 9}    1 0 0
 14 {}       {UPDATE OR FAIL}        1 {6 7 3 4}  1 0 0
 15 {}       {UPDATE OR ABORT}       1 {1 2 3 4}  1 0 1
 16 {}       {UPDATE OR ROLLBACK}    1 {1 2 3 4}  0 0 0
} {
  if {$t0} {set t1 {UNIQUE constraint failed: t1.a}}
  if {[info exists TEMP_STORE] && $TEMP_STORE==3} {
    set t3 0
  } else {
    set t3 [expr {$t3+$t4}]
  }
  do_test conflict-6.$i {
    db close

  }
} {0 {1 1 1 1 1 2 2 2 2 2}}
do_test conflict-9.5 {
  catchsql {
    INSERT INTO t2 VALUES(3,1,3,3,3);
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.b}}
do_test conflict-9.6 {
  catchsql {
    UPDATE t2 SET b=b+1 WHERE b=1;
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.b}}
do_test conflict-9.7 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    INSERT INTO t2 VALUES(3,1,3,3,3);
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.b}}
do_test conflict-9.8 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {2}
do_test conflict-9.9 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    UPDATE t2 SET b=b+1 WHERE b=1;
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.b}}
do_test conflict-9.10 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {3}
do_test conflict-9.11 {
  catchsql {
    INSERT INTO t2 VALUES(3,3,3,1,3);
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.d}}
do_test conflict-9.12 {
  catchsql {
    UPDATE t2 SET d=d+1 WHERE d=1;
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.d}}
do_test conflict-9.13 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    INSERT INTO t2 VALUES(3,3,3,1,3);
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.d}}
do_test conflict-9.14 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {4}
do_test conflict-9.15 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    UPDATE t2 SET d=d+1 WHERE d=1;
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.d}}
do_test conflict-9.16 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {5}
do_test conflict-9.17 {
  catchsql {
    INSERT INTO t2 VALUES(3,3,3,3,1);
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.e}}
do_test conflict-9.18 {
  catchsql {
    UPDATE t2 SET e=e+1 WHERE e=1;
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.e}}
do_test conflict-9.19 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    INSERT INTO t2 VALUES(3,3,3,3,1);
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.e}}
verify_ex_errcode conflict-9.21b SQLITE_CONSTRAINT_UNIQUE
do_test conflict-9.20 {
  catch {execsql {COMMIT}}
  execsql {SELECT * FROM t3}
} {5}
do_test conflict-9.21 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    UPDATE t2 SET e=e+1 WHERE e=1;
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.e}}
verify_ex_errcode conflict-9.21b SQLITE_CONSTRAINT_UNIQUE
do_test conflict-9.22 {
  catch {execsql {COMMIT}}
  execsql {SELECT * FROM t3}
} {5}
do_test conflict-9.23 {
  catchsql {

    SELECT * FROM t5;
  }
} {1 one 2 two}
do_test conflict-12.3 {
  catchsql {
    UPDATE t5 SET a=a+1 WHERE a=1;
  }
} {1 {UNIQUE constraint failed: t5.a}}
verify_ex_errcode conflict-12.3b SQLITE_CONSTRAINT_PRIMARYKEY
do_test conflict-12.4 {
  execsql {
    UPDATE OR REPLACE t5 SET a=a+1 WHERE a=1;
    SELECT * FROM t5;
  }
} {2 one}
do_test conflict-12.5 {
  catchsql {
    CREATE TABLE t5b(x);
    INSERT INTO t5b(rowid, x) VALUES(1,10),(2,11);
    UPDATE t5b SET rowid=rowid+1 WHERE x=10;
  }
} {1 {UNIQUE constraint failed: t5b.rowid}}
verify_ex_errcode conflict-12.5b SQLITE_CONSTRAINT_ROWID


# Ticket [c38baa3d969eab7946dc50ba9d9b4f0057a19437]
# REPLACE works like ABORT on a CHECK constraint.
#
do_test conflict-13.1 {
  execsql {
    CREATE TABLE t13(a CHECK(a!=2));
    BEGIN;
    REPLACE INTO t13 VALUES(1);
  }
  catchsql {
    REPLACE INTO t13 VALUES(2);
  }
} {1 {CHECK constraint failed: t13}}
verify_ex_errcode conflict-13.1b SQLITE_CONSTRAINT_CHECK
do_test conflict-13.2 {
  execsql {
    REPLACE INTO t13 VALUES(3);
    COMMIT;
    SELECT * FROM t13;
  }
} {1 3}


finish_test

 11 ROLLBACK {INSERT OR IGNORE}      0 {}  1
 12 {}       {INSERT OR IGNORE}      0 {}  1
 13 {}       {INSERT OR REPLACE}     0 5   1
 14 {}       {INSERT OR FAIL}        1 {}  1
 15 {}       {INSERT OR ABORT}       1 {}  1
 16 {}       {INSERT OR ROLLBACK}    1 {}  {}
} {
  if {$t0} {set t1 {NOT NULL constraint failed: t1.c}}
  do_test conflict2-5.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    set r0 [catch {execsql [subst {
      DROP TABLE t1;
      CREATE TABLE t1(a,b,c NOT NULL $conf1 DEFAULT 5);
      DELETE FROM t2;
      BEGIN;

 11 ROLLBACK {UPDATE OR IGNORE}      0 {6 7 3 9}  1 0 0
 12 {}       {UPDATE OR IGNORE}      0 {6 7 3 9}  1 0 0
 13 {}       {UPDATE OR REPLACE}     0 {7 6 9}    1 0 1
 14 {}       {UPDATE OR FAIL}        1 {6 7 3 4}  1 0 0
 15 {}       {UPDATE OR ABORT}       1 {1 2 3 4}  1 0 1
 16 {}       {UPDATE OR ROLLBACK}    1 {1 2 3 4}  0 0 0
} {
  if {$t0} {set t1 {UNIQUE constraint failed: t1.a}}
  if {[info exists TEMP_STORE] && $TEMP_STORE==3} {
    set t3 0
  } else {
    set t3 [expr {$t3+$t4}]
  }
  do_test conflict2-6.$i {
    db close

    SELECT * FROM t2;
  }
} {0 {1 1 1 1 1 2 2 2 2 2}}
do_test conflict2-9.5 {
  catchsql {
    INSERT INTO t2 VALUES(3,1,3,3,3);
  }
} {1 {UNIQUE constraint failed: t2.b}}
do_test conflict2-9.5b {
  db eval {SELECT * FROM t2;}
} {1 1 1 1 1 2 2 2 2 2}
do_test conflict2-9.6 {
  catchsql {
    UPDATE t2 SET b=b+1 WHERE b=1;
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.b}}
do_test conflict2-9.6b {
  db eval {SELECT * FROM t2;}
} {1 1 1 1 1 2 2 2 2 2}
do_test conflict2-9.7 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    INSERT INTO t2 VALUES(3,1,3,3,3);
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.b}}
do_test conflict2-9.8 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {2}
do_test conflict2-9.9 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    UPDATE t2 SET b=b+1 WHERE b=1;
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.b}}
do_test conflict2-9.10 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {3}
do_test conflict2-9.11 {
  catchsql {
    INSERT INTO t2 VALUES(3,3,3,1,3);
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.d}}
do_test conflict2-9.12 {
  catchsql {
    UPDATE t2 SET d=d+1 WHERE d=1;
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.d}}
do_test conflict2-9.13 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    INSERT INTO t2 VALUES(3,3,3,1,3);
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.d}}
do_test conflict2-9.14 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {4}
do_test conflict2-9.15 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    UPDATE t2 SET d=d+1 WHERE d=1;
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.d}}
do_test conflict2-9.16 {
  execsql {COMMIT}
  execsql {SELECT * FROM t3}
} {5}
do_test conflict2-9.17 {
  catchsql {
    INSERT INTO t2 VALUES(3,3,3,3,1);
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.e}}
do_test conflict2-9.18 {
  catchsql {
    UPDATE t2 SET e=e+1 WHERE e=1;
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.e}}
do_test conflict2-9.19 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    INSERT INTO t2 VALUES(3,3,3,3,1);
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.e}}
verify_ex_errcode conflict2-9.21b SQLITE_CONSTRAINT_UNIQUE
do_test conflict2-9.20 {
  catch {execsql {COMMIT}}
  execsql {SELECT * FROM t3}
} {5}
do_test conflict2-9.21 {
  catchsql {
    BEGIN;
    UPDATE t3 SET x=x+1;
    UPDATE t2 SET e=e+1 WHERE e=1;
    SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t2.e}}
verify_ex_errcode conflict2-9.21b SQLITE_CONSTRAINT_UNIQUE
do_test conflict2-9.22 {
  catch {execsql {COMMIT}}
  execsql {SELECT * FROM t3}
} {5}
do_test conflict2-9.23 {
  catchsql {

    SELECT * FROM t5;
  }
} {1 one 2 two}
do_test conflict2-12.3 {
  catchsql {
    UPDATE t5 SET a=a+1 WHERE a=1;
  }
} {1 {UNIQUE constraint failed: t5.a}}
verify_ex_errcode conflict2-12.3b SQLITE_CONSTRAINT_PRIMARYKEY
do_test conflict2-12.4 {
  execsql {
    UPDATE OR REPLACE t5 SET a=a+1 WHERE a=1;
    SELECT * FROM t5;
  }
} {2 one}

    CREATE TABLE t13(a PRIMARY KEY CHECK(a!=2)) WITHOUT rowid;
    BEGIN;
    REPLACE INTO t13 VALUES(1);
  }
  catchsql {
    REPLACE INTO t13 VALUES(2);
  }
} {1 {CHECK constraint failed: t13}}
verify_ex_errcode conflict2-13.1b SQLITE_CONSTRAINT_CHECK
do_test conflict2-13.2 {
  execsql {
    REPLACE INTO t13 VALUES(3);
    COMMIT;
    SELECT * FROM t13;
  }
} {1 3}


finish_test

  CREATE TABLE t2(x, y, PRIMARY KEY(x, y));
  INSERT INTO t2 VALUES(0,          'zero');
  INSERT INTO t2 VALUES(45.5,       'one');
  INSERT INTO t2 VALUES('brambles', 'two');
  INSERT INTO t2 VALUES(X'ABCDEF',  'three');
} {}

do_createtable_tests 4.3.1 -error {UNIQUE constraint failed: t1.x} {
  1    "INSERT INTO t1 VALUES(0, 0)"                 {"column x is"}
  2    "INSERT INTO t1 VALUES(45.5, 'abc')"          {"column x is"}
  3    "INSERT INTO t1 VALUES(0.0, 'abc')"           {"column x is"}
  4    "INSERT INTO t1 VALUES('brambles', 'abc')"    {"column x is"}
  5    "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')"     {"column x is"}
}
do_createtable_tests 4.3.1 -error {UNIQUE constraint failed: t2.x, t2.y} {
  6    "INSERT INTO t2 VALUES(0, 'zero')"            {"columns x, y are"}
  7    "INSERT INTO t2 VALUES(45.5, 'one')"          {"columns x, y are"}
  8    "INSERT INTO t2 VALUES(0.0, 'zero')"          {"columns x, y are"}
  9    "INSERT INTO t2 VALUES('brambles', 'two')"    {"columns x, y are"}
  10   "INSERT INTO t2 VALUES(X'ABCDEF', 'three')"   {"columns x, y are"}
}
do_createtable_tests 4.3.2 {
  1    "INSERT INTO t1 VALUES(-1, 0)"                {}
  2    "INSERT INTO t1 VALUES(45.2, 'abc')"          {}
  3    "INSERT INTO t1 VALUES(0.01, 'abc')"          {}
  4    "INSERT INTO t1 VALUES('bramble', 'abc')"     {}
  5    "INSERT INTO t1 VALUES(X'ABCDEE', 'abc')"     {}

  6    "INSERT INTO t2 VALUES(0, 0)"                 {}
  7    "INSERT INTO t2 VALUES(45.5, 'abc')"          {}
  8    "INSERT INTO t2 VALUES(0.0, 'abc')"           {}
  9    "INSERT INTO t2 VALUES('brambles', 'abc')"    {}
  10   "INSERT INTO t2 VALUES(X'ABCDEF', 'abc')"     {}
}
do_createtable_tests 4.3.3 -error {UNIQUE constraint failed: t1.x} {
  1    "UPDATE t1 SET x=0           WHERE y='two'"    {"column x is"}
  2    "UPDATE t1 SET x='brambles'  WHERE y='three'"  {"column x is"}
  3    "UPDATE t1 SET x=45.5        WHERE y='zero'"   {"column x is"}
  4    "UPDATE t1 SET x=X'ABCDEF'   WHERE y='one'"    {"column x is"}
  5    "UPDATE t1 SET x=0.0         WHERE y='three'"  {"column x is"}
}
do_createtable_tests 4.3.3 -error {UNIQUE constraint failed: t2.x, t2.y} {
  6    "UPDATE t2 SET x=0, y='zero' WHERE y='two'"    {"columns x, y are"}
  7    "UPDATE t2 SET x='brambles', y='two' WHERE y='three'"  
       {"columns x, y are"}
  8    "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {"columns x, y are"}
  9    "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'" 
       {"columns x, y are"}
  10   "UPDATE t2 SET x=0.0, y='zero'        WHERE y='three'"  

  INSERT INTO t1 VALUES('reveal', 'variableness');
  INSERT INTO t1 VALUES(X'123456', X'654321');

  INSERT INTO t4 VALUES('xyx', 1, 1);
  INSERT INTO t4 VALUES('xyx', 2, 1);
  INSERT INTO t4 VALUES('uvw', 1, 1);
}
do_createtable_tests 4.7.1 -error {UNIQUE constraint failed: %s} {
  1    "INSERT INTO t1 VALUES(1, 'one')"             {{t1.a}}
  2    "INSERT INTO t1 VALUES(4.3, 'two')"           {{t1.a}}
  3    "INSERT INTO t1 VALUES('reveal', 'three')"    {{t1.a}}
  4    "INSERT INTO t1 VALUES(X'123456', 'four')"    {{t1.a}}

  5    "UPDATE t1 SET a = 1 WHERE rowid=2"           {{t1.a}}
  6    "UPDATE t1 SET a = 4.3 WHERE rowid=3"         {{t1.a}}
  7    "UPDATE t1 SET a = 'reveal' WHERE rowid=4"    {{t1.a}}
  8    "UPDATE t1 SET a = X'123456' WHERE rowid=1"   {{t1.a}}

  9    "INSERT INTO t4 VALUES('xyx', 1, 1)"          {{t4.a, t4.b, t4.c}}
  10   "INSERT INTO t4 VALUES('xyx', 2, 1)"          {{t4.a, t4.b, t4.c}}
  11   "INSERT INTO t4 VALUES('uvw', 1, 1)"          {{t4.a, t4.b, t4.c}}

  12   "UPDATE t4 SET a='xyx' WHERE rowid=3"         {{t4.a, t4.b, t4.c}}
  13   "UPDATE t4 SET b=1 WHERE rowid=2"             {{t4.a, t4.b, t4.c}}
  14   "UPDATE t4 SET a=0, b=0, c=0"                 {{t4.a, t4.b, t4.c}}
}

# EVIDENCE-OF: R-21289-11559 As with PRIMARY KEY constraints, for the
# purposes of UNIQUE constraints NULL values are considered distinct
# from all other values (including other NULLs).
#
do_createtable_tests 4.8 {

  CREATE TABLE x2(a CHECK( a||b ), b);
  CREATE TABLE t2(a, b, CHECK( a||b ));
  INSERT INTO x2 VALUES(1, 'xx');
  INSERT INTO x2 VALUES(1, 'yy');
  INSERT INTO t2 SELECT * FROM x2;
}

do_createtable_tests 4.11 -error {CHECK constraint failed: %s} {
  1a    "INSERT INTO x1 VALUES('one', 0)"       {x1}
  1b    "INSERT INTO t1 VALUES('one', -4.0)"    {t1}

  2a    "INSERT INTO x2 VALUES('abc', 1)"       {x2}
  2b    "INSERT INTO t2 VALUES('abc', 1)"       {t2}

  3a    "INSERT INTO x2 VALUES(0, 'abc')"       {x2}
  3b    "INSERT INTO t2 VALUES(0, 'abc')"       {t2}

  4a    "UPDATE t1 SET b=-1 WHERE rowid=1"      {t1}
  4b    "UPDATE x1 SET b=-1 WHERE rowid=1"      {x1}

  4a    "UPDATE x2 SET a='' WHERE rowid=1"      {x2}
  4b    "UPDATE t2 SET a='' WHERE rowid=1"      {t2}
}

# EVIDENCE-OF: R-34109-39108 If the CHECK expression evaluates to NULL,
# or any other non-zero value, it is not a constraint violation.
#
do_createtable_tests 4.12 {
  1a    "INSERT INTO x1 VALUES('one', NULL)"    {}

  INSERT INTO t2 VALUES('x', 'y');
  INSERT INTO t2 VALUES('z', NULL);

  INSERT INTO t3 VALUES('x', 'y', 'z');
  INSERT INTO t3 VALUES(1, 2, 3);
}
do_createtable_tests 4.14 -error {NOT NULL constraint failed: %s} {


  1    "INSERT INTO t1 VALUES(NULL, 'a')"         {t1.a}
  2    "INSERT INTO t2 VALUES(NULL, 'b')"         {t2.a}
  3    "INSERT INTO t3 VALUES('c', 'd', NULL)"    {t3.c}
  4    "INSERT INTO t3 VALUES('e', NULL, 'f')"    {t3.b}
  5    "INSERT INTO t3 VALUES(NULL, 'g', 'h')"    {t3.a}
}

  INSERT INTO t3_ig SELECT * FROM t3_ab;
  INSERT INTO t3_fa SELECT * FROM t3_ab;
  INSERT INTO t3_re SELECT * FROM t3_ab;
  INSERT INTO t3_xx SELECT * FROM t3_ab;
}

foreach {tn tbl res ac data} {
  1   t1_ab    {1 {UNIQUE constraint failed: t1_ab.a}} 0 {1 one 2 two 3 three}
  2   t1_ro    {1 {UNIQUE constraint failed: t1_ro.a}} 1 {1 one 2 two}
  3   t1_fa    {1 {UNIQUE constraint failed: t1_fa.a}} 0 {1 one 2 two 3 three 4 string}
  4   t1_ig    {0 {}} 0 {1 one 2 two 3 three 4 string 6 string}
  5   t1_re    {0 {}} 0 {1 one 2 two 4 string 3 string 6 string}
  6   t1_xx    {1 {UNIQUE constraint failed: t1_xx.a}} 0 {1 one 2 two 3 three}
} {
  catchsql COMMIT
  do_execsql_test  4.15.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"

  do_catchsql_test 4.15.$tn.2 " 
    INSERT INTO $tbl SELECT ((a%2)*a+3), 'string' FROM $tbl;
  " $res

  do_test e_createtable-4.15.$tn.3 { sqlite3_get_autocommit db } $ac
  do_execsql_test 4.15.$tn.4 "SELECT * FROM $tbl" $data
}
foreach {tn tbl res ac data} {
  1   t2_ab    {1 {NOT NULL constraint failed: t2_ab.b}} 0 {1 one 2 two 3 three}
  2   t2_ro    {1 {NOT NULL constraint failed: t2_ro.b}} 1 {1 one 2 two}
  3   t2_fa    {1 {NOT NULL constraint failed: t2_fa.b}} 0 {1 one 2 two 3 three 4 xx}
  4   t2_ig    {0 {}} 0 {1 one 2 two 3 three 4 xx 6 xx}
  5   t2_re    {1 {NOT NULL constraint failed: t2_re.b}} 0 {1 one 2 two 3 three}
  6   t2_xx    {1 {NOT NULL constraint failed: t2_xx.b}} 0 {1 one 2 two 3 three}
} {
  catchsql COMMIT
  do_execsql_test  4.16.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"

  do_catchsql_test 4.16.$tn.2 " 
    INSERT INTO $tbl SELECT a+3, CASE a WHEN 2 THEN NULL ELSE 'xx' END FROM $tbl
  " $res

  do_test e_createtable-4.16.$tn.3 { sqlite3_get_autocommit db } $ac
  do_execsql_test 4.16.$tn.4 "SELECT * FROM $tbl" $data
}
foreach {tn tbl res ac data} {
  1   t3_ab    {1 {UNIQUE constraint failed: t3_ab.a, t3_ab.b}}
               0 {1 one 2 two 3 three}
  2   t3_ro    {1 {UNIQUE constraint failed: t3_ro.a, t3_ro.b}}
               1 {1 one 2 two}
  3   t3_fa    {1 {UNIQUE constraint failed: t3_fa.a, t3_fa.b}}
               0 {1 one 2 two 3 three 4 three}
  4   t3_ig    {0 {}} 0 {1 one 2 two 3 three 4 three 6 three}
  5   t3_re    {0 {}} 0 {1 one 2 two 4 three 3 three 6 three}
  6   t3_xx    {1 {UNIQUE constraint failed: t3_xx.a, t3_xx.b}}
               0 {1 one 2 two 3 three}
} {
  catchsql COMMIT
  do_execsql_test  4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"

  do_catchsql_test 4.17.$tn.2 " 
    INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl
  " $res

  CREATE TABLE t4(a, b CHECK (b!=10));
  INSERT INTO t4 VALUES(1, 2);
  INSERT INTO t4 VALUES(3, 4);
}
do_execsql_test  4.18.2 { BEGIN; INSERT INTO t4 VALUES(5, 6) }
do_catchsql_test 4.18.3 { 
  INSERT INTO t4 SELECT a+4, b+4 FROM t4
} {1 {CHECK constraint failed: t4}}
do_test e_createtable-4.18.4 { sqlite3_get_autocommit db } 0
do_execsql_test 4.18.5 { SELECT * FROM t4 } {1 2 3 4 5 6}

# EVIDENCE-OF: R-19114-56113 Different constraints within the same table
# may have different default conflict resolution algorithms.
#
do_execsql_test 4.19.0 {
  CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT);
}
do_catchsql_test 4.19.1 { INSERT INTO t5 VALUES(NULL, 'not null') } {0 {}}
do_execsql_test  4.19.2 { SELECT * FROM t5 } {}
do_catchsql_test 4.19.3 { INSERT INTO t5 VALUES('not null', NULL) } \
  {1 {NOT NULL constraint failed: t5.b}}
do_execsql_test  4.19.4 { SELECT * FROM t5 } {}

#------------------------------------------------------------------------
# Tests for INTEGER PRIMARY KEY and rowid related statements.
#

# EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one

  SELECT typeof(pk), pk FROM t7;
  SELECT typeof(pk), pk FROM t8;
  SELECT typeof(pk), pk FROM t9;
} {integer 2 integer 2 integer 2 integer 2}

do_catchsql_test 5.4.4.1 { 
  INSERT INTO t6 VALUES(2) 
} {1 {UNIQUE constraint failed: t6.pk}}
do_catchsql_test 5.4.4.2 { 
  INSERT INTO t7 VALUES(2) 
} {1 {UNIQUE constraint failed: t7.pk}}
do_catchsql_test 5.4.4.3 { 
  INSERT INTO t8 VALUES(2) 
} {1 {UNIQUE constraint failed: t8.pk}}
do_catchsql_test 5.4.4.4 { 
  INSERT INTO t9 VALUES(2) 
} {1 {UNIQUE constraint failed: t9.pk}}

# EVIDENCE-OF: R-56094-57830 the following three table declarations all
# cause the column "x" to be an alias for the rowid (an integer primary
# key): CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE
# t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y,
# z, PRIMARY KEY(x DESC));
#

    INSERT INTO t2 VALUES(2, 1);
    BEGIN;
      PRAGMA foreign_keys = OFF;
  }
  catchsql {
      DELETE FROM t1
  }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-6.2 {
  execsql { PRAGMA foreign_keys }
} {1}
do_test e_fkey-6.3 {
  execsql {
    COMMIT;
    PRAGMA foreign_keys = OFF;

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-61362-32087 Attempting to insert a row into the track
# table that does not correspond to any row in the artist table will
# fail,
#
do_test e_fkey-8.1 {
  catchsql { INSERT INTO track VALUES(1, 'track 1', 1) }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-8.2 {
  execsql { INSERT INTO artist VALUES(2, 'artist 1') }
  catchsql { INSERT INTO track VALUES(1, 'track 1', 1) }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-8.2 {
  execsql { INSERT INTO track VALUES(1, 'track 1', 2) }
} {}

#-------------------------------------------------------------------------
# Attempting to delete a row from the 'artist' table while there are 
# dependent rows in the track table also fails.
#
# EVIDENCE-OF: R-24401-52400 as will attempting to delete a row from the
# artist table when there exist dependent rows in the track table
#
do_test e_fkey-9.1 {
  catchsql { DELETE FROM artist WHERE artistid = 2 }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-9.2 {
  execsql { 
    DELETE FROM track WHERE trackartist = 2;
    DELETE FROM artist WHERE artistid = 2;
  }
} {}

} {}
do_test e_fkey-10.2 {
  execsql { SELECT * FROM artist }
} {}
do_test e_fkey-10.3 {
  # Setting the trackid to a non-NULL value fails, of course.
  catchsql { UPDATE track SET trackartist = 5 WHERE trackid = 1 }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-10.4 {
  execsql {
    INSERT INTO artist VALUES(5, 'artist 5');
    UPDATE track SET trackartist = 5 WHERE trackid = 1;
  }
  catchsql { DELETE FROM artist WHERE artistid = 5}
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-10.5 {
  execsql { 
    UPDATE track SET trackartist = NULL WHERE trackid = 1;
    DELETE FROM artist WHERE artistid = 5;
  }
} {}

# This procedure executes a test case to check that statement 
# R-52486-21352 is true after executing the SQL statement passed.
# as the second argument.
proc test_r52486_21352 {tn sql} {
  set res [catchsql $sql]
  set results {
    {0 {}} 
    {1 {UNIQUE constraint failed: artist.artistid}} 
    {1 {FOREIGN KEY constraint failed}}
  }
  if {[lsearch $results $res]<0} {
    error $res
  }

  do_test e_fkey-11.$tn {
    execsql {

      trackartist INTEGER NOT NULL,
      FOREIGN KEY(trackartist) REFERENCES artist(artistid)
    );
  }
} {}
do_test e_fkey-12.2 {
  catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) }
} {1 {NOT NULL constraint failed: track.trackartist}}

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-16127-35442
#
# Test an example from foreignkeys.html.
#
drop_all_tables

    INSERT INTO track VALUES(11, 'That''s Amore', 1);
    INSERT INTO track VALUES(12, 'Christmas Blues', 1);
    INSERT INTO track VALUES(13, 'My Way', 2);
  }
} {}
do_test e_fkey-13.2 {
  catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', 3) }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-13.3 {
  execsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) }
} {}
do_test e_fkey-13.4 {
  catchsql { 
    UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';
  }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-13.5 {
  execsql {
    INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
    UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles';
    INSERT INTO track VALUES(15, 'Boogie Woogie', 3);
  }
} {}

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-15958-50233
#
# Test the second example from the first section of foreignkeys.html.
#
do_test e_fkey-14.1 {
  catchsql {
    DELETE FROM artist WHERE artistname = 'Frank Sinatra';
  }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-14.2 {
  execsql {
    DELETE FROM track WHERE trackname = 'My Way';
    DELETE FROM artist WHERE artistname = 'Frank Sinatra';
  }
} {}
do_test e_fkey-14.3 {
  catchsql {
    UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
  }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-14.4 {
  execsql {
    DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues');
    UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
  }
} {}

    SELECT typeof(p) FROM par;
  }
} {integer text blob}

proc test_efkey_45 {tn isError sql} {
  do_test e_fkey-15.$tn.1 "
    catchsql {$sql}
  " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]

  do_test e_fkey-15.$tn.2 {
    execsql {
      SELECT * FROM chi WHERE c IS NOT NULL AND c NOT IN (SELECT p FROM par)
    }
  } {}
}

    INSERT INTO t2 VALUES('ONE');
    UPDATE t2 SET b = 'OnE';
    UPDATE t1 SET a = 'ONE';
  }
} {}
do_test e_fkey-16.3 {
  catchsql { UPDATE t2 SET b = 'two' WHERE rowid = 1 }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-16.4 {
  catchsql { DELETE FROM t1 WHERE rowid = 1 }
} {1 {FOREIGN KEY constraint failed}}

#-------------------------------------------------------------------------
# Specifically, test that when comparing child and parent key values the
# affinity of the parent key column is applied to the child key value
# before the comparison takes place.
#
# EVIDENCE-OF: R-04240-13860 When comparing values, if the parent key

  }
} {2.0 text}
do_test e_fkey-17.3 {
  execsql { SELECT typeof(a) FROM t1 }
} {integer integer text}
do_test e_fkey-17.4 {
  catchsql { DELETE FROM t1 WHERE rowid = 2 }
} {1 {FOREIGN KEY constraint failed}}

###########################################################################
### SECTION 3: Required and Suggested Database Indexes
###########################################################################

#-------------------------------------------------------------------------
# A parent key must be either a PRIMARY KEY, subject to a UNIQUE 

    CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p1);
    CREATE TABLE c2(a, b REFERENCES p2);
  }
} {}
proc test_efkey_60 {tn isError sql} {
  do_test e_fkey-23.$tn "
    catchsql {$sql}
  " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]
}

test_efkey_60 2 1 "INSERT INTO c1 VALUES(239, 231)"
test_efkey_60 3 0 "INSERT INTO p1 VALUES(239, 231)"
test_efkey_60 4 0 "INSERT INTO c1 VALUES(239, 231)"
test_efkey_60 5 1 "INSERT INTO c2 VALUES(239, 231)"
test_efkey_60 6 0 "INSERT INTO p2 VALUES(239, 231)"

    CREATE INDEX c2i ON c2(a, b);
    CREATE UNIQUE INDEX c3i ON c2(b, a);
  }
} {}
proc test_efkey_61 {tn isError sql} {
  do_test e_fkey-24.$tn "
    catchsql {$sql}
  " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]
}
foreach {tn c} [list 2 c1 3 c2 4 c3] {
  test_efkey_61 $tn.1 1 "INSERT INTO $c VALUES(1, 2)"
  test_efkey_61 $tn.2 0 "INSERT INTO parent VALUES(1, 2)"
  test_efkey_61 $tn.3 0 "INSERT INTO $c VALUES(1, 2)"

  execsql "DELETE FROM $c ; DELETE FROM parent"

  }
} {}

do_test e_fkey-25.5 {
  concat \
    [execsql { SELECT rowid FROM track WHERE trackartist = 5 }]   \
    [catchsql { DELETE FROM artist WHERE artistid = 5 }]
} {1 1 {FOREIGN KEY constraint failed}}

do_test e_fkey-25.6 {
  concat \
    [execsql { SELECT rowid FROM track WHERE trackartist = 7 }]   \
    [catchsql { DELETE FROM artist WHERE artistid = 7 }]
} {0 {}}

do_test e_fkey-25.7 {
  concat \
    [execsql { SELECT rowid FROM track WHERE trackartist = 6 }]   \
    [catchsql { DELETE FROM artist WHERE artistid = 6 }]
} {2 1 {FOREIGN KEY constraint failed}}

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-47936-10044 Or, more generally:
# SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
#
# Test that when a row is deleted from the parent table of an FK 
# constraint, the child table is queried for orphaned rows. The

    );
  }
} {}
do_test e_fkey-29.3 {
  catchsql {
    INSERT INTO song VALUES(2, 'Elvis Presley', 'Elvis Is Back!', 'Fever');
  }
} {1 {FOREIGN KEY constraint failed}}


#-------------------------------------------------------------------------
# EVIDENCE-OF: R-33626-48418 In SQLite, if any of the child key columns
# (in this case songartist and songalbum) are NULL, then there is no
# requirement for a corresponding row in the parent table.
#

    CREATE TABLE prince(c REFERENCES king, d);
  }
} {}

do_test e_fkey-31.2 {
  # Execute a statement that violates the immediate FK constraint.
  catchsql { INSERT INTO prince VALUES(1, 2) }
} {1 {FOREIGN KEY constraint failed}}

do_test e_fkey-31.3 {
  # This time, use a trigger to fix the constraint violation before the
  # statement has finished executing. Then execute the same statement as
  # in the previous test case. This time, no error.
  execsql {
    CREATE TRIGGER kt AFTER INSERT ON prince WHEN

do_test e_fkey-31.4 {
  execsql {
    BEGIN;
    INSERT INTO prince VALUES(2, 3);
    DROP TRIGGER kt;
  }
  catchsql { INSERT INTO prince VALUES(3, 4) }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-31.5 {
  execsql {
    COMMIT;
    SELECT * FROM king;
  }
} {1 {} 2 {}}

#
# EVIDENCE-OF: R-29604-30395 However, COMMIT will fail as long as
# foreign key constraints remain in violation.
#
proc test_efkey_34 {tn isError sql} {
  do_test e_fkey-32.$tn "
    catchsql {$sql}
  " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]
}
drop_all_tables

test_efkey_34  1 0 {
  CREATE TABLE ll(k PRIMARY KEY);
  CREATE TABLE kk(c REFERENCES ll DEFERRABLE INITIALLY DEFERRED);
}

# executing. In this case deferred constraints behave the same as
# immediate constraints.
#
drop_all_tables
proc test_efkey_35 {tn isError sql} {
  do_test e_fkey-33.$tn "
    catchsql {$sql}
  " [lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError]
}
do_test e_fkey-33.1 {
  execsql {
    CREATE TABLE parent(x, y);
    CREATE UNIQUE INDEX pi ON parent(x, y);
    CREATE TABLE child(a, b,
      FOREIGN KEY(a, b) REFERENCES parent(x, y) DEFERRABLE INITIALLY DEFERRED

    INSERT INTO c6 VALUES('p', 'q', 'r');
    INSERT INTO c7 VALUES('s', 't', 'u');
  }
} {}

proc test_efkey_29 {tn sql isError} {
  do_test e_fkey-34.$tn "catchsql {$sql}" [
    lindex {{0 {}} {1 {FOREIGN KEY constraint failed}}} $isError
  ]
}
test_efkey_29  2 "BEGIN"                                   0
test_efkey_29  3 "DELETE FROM parent WHERE x = 'a'"        1
test_efkey_29  4 "DELETE FROM parent WHERE x = 'd'"        1
test_efkey_29  5 "DELETE FROM parent WHERE x = 'g'"        1
test_efkey_29  6 "DELETE FROM parent WHERE x = 'j'"        1

} {}
do_test e_fkey-35.2 {
  execsql {
    BEGIN;
      INSERT INTO track VALUES(1, 'White Christmas', 5);
  }
  catchsql COMMIT
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-35.3 {
  execsql {
    INSERT INTO artist VALUES(5, 'Bing Crosby');
    COMMIT;
  }
} {}

      SAVEPOINT one;
        INSERT INTO t1 VALUES(4, 5);
      RELEASE one;
  }
} {}
do_test e_fkey-36.3 {
  catchsql COMMIT
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-36.4 {
  execsql {
    UPDATE t1 SET a = 5 WHERE a = 4;
    COMMIT;
  }
} {}

      SAVEPOINT two;
        INSERT INTO t1 VALUES(6, 7);
      RELEASE two;
  }
} {}
do_test e_fkey-37.2 {
  catchsql {RELEASE one}
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-37.3 {
  execsql {
      UPDATE t1 SET a = 7 WHERE a = 6;
    RELEASE one;
  }
} {}
do_test e_fkey-37.4 {
  execsql {
    SAVEPOINT one;
      SAVEPOINT two;
        INSERT INTO t1 VALUES(9, 10);
      RELEASE two;
  }
} {}
do_test e_fkey-37.5 {
  catchsql {RELEASE one}
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-37.6 {
  execsql {ROLLBACK TO one ; RELEASE one}
} {}

#-------------------------------------------------------------------------
# Test that if a COMMIT operation fails due to deferred foreign key 
# constraints, any nested savepoints remain open.

      SAVEPOINT one;
        INSERT INTO t1 VALUES(5, 6);
        SELECT * FROM t1;
  }
} {1 1 2 2 3 3 4 4 5 6}
do_test e_fkey-38.3 {
  catchsql COMMIT
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-38.4 {
  execsql {
    ROLLBACK TO one;
    COMMIT;
    SELECT * FROM t1;
  }
} {1 1 2 2 3 3 4 4}

do_test e_fkey-38.5 {
  execsql {
    SAVEPOINT a;
      INSERT INTO t1 VALUES(5, 5);
      SAVEPOINT b;
        INSERT INTO t1 VALUES(6, 7);
        SAVEPOINT c;
          INSERT INTO t1 VALUES(7, 8);
  }
} {}
do_test e_fkey-38.6 {
  catchsql {RELEASE a}
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-38.7 {
  execsql  {ROLLBACK TO c}
  catchsql {RELEASE a}
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-38.8 {
  execsql  {
    ROLLBACK TO b;
    RELEASE a;
    SELECT * FROM t1;
  }
} {1 1 2 2 3 3 4 4 5 5}

      UPDATE parent SET p1='k' WHERE p1='j';
      DELETE FROM parent WHERE p1='l';
      SELECT * FROM child;
  }
} {j k l m}
do_test e_fkey-41.3 {
  catchsql COMMIT
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-41.4 {
  execsql ROLLBACK
} {}

#-------------------------------------------------------------------------
# Test that "RESTRICT" means the application is prohibited from deleting
# or updating a parent table row when there exists one or more child keys

    INSERT INTO parent VALUES('c', 'd');
    INSERT INTO child1 VALUES('b', 'a');
    INSERT INTO child2 VALUES('d', 'c');
  }
} {}
do_test e_fkey-41.3 {
  catchsql { DELETE FROM parent WHERE p1 = 'a' }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-41.4 {
  catchsql { UPDATE parent SET p2 = 'e' WHERE p1 = 'c' }
} {1 {FOREIGN KEY constraint failed}}

#-------------------------------------------------------------------------
# Test that RESTRICT is slightly different from NO ACTION for IMMEDIATE
# constraints, in that it is enforced immediately, not at the end of the 
# statement.
#
# EVIDENCE-OF: R-37997-42187 The difference between the effect of a

      UPDATE child1 set c = new.x WHERE c = old.x;
      UPDATE child2 set c = new.x WHERE c = old.x;
    END;
  }
} {}
do_test e_fkey-42.2 {
  catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-42.3 {
  execsql { 
    UPDATE parent SET x = 'key two' WHERE x = 'key2';
    SELECT * FROM child2;
  }
} {{key two}}

      UPDATE child1 SET c = NULL WHERE c = old.x;
      UPDATE child2 SET c = NULL WHERE c = old.x;
    END;
  }
} {}
do_test e_fkey-42.5 {
  catchsql { DELETE FROM parent WHERE x = 'key1' }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-42.6 {
  execsql { 
    DELETE FROM parent WHERE x = 'key2';
    SELECT * FROM child2;
  }
} {{}}

    INSERT INTO parent VALUES('key2');
    INSERT INTO child1 VALUES('key1');
    INSERT INTO child2 VALUES('key2');
  }
} {}
do_test e_fkey-42.8 {
  catchsql { REPLACE INTO parent VALUES('key1') }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-42.9 {
  execsql { 
    REPLACE INTO parent VALUES('key2');
    SELECT * FROM child2;
  }
} {key2}

    INSERT INTO child1 VALUES('key1');
    INSERT INTO child2 VALUES('key2');
    BEGIN;
  }
} {}
do_test e_fkey-43.2 {
  catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-43.3 {
  execsql { UPDATE parent SET x = 'key two' WHERE x = 'key2' }
} {}
do_test e_fkey-43.4 {
  catchsql COMMIT
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-43.5 {
  execsql {
    UPDATE child2 SET c = 'key two';
    COMMIT;
  }
} {}

    INSERT INTO child1 VALUES('key1');
    INSERT INTO child2 VALUES('key2');
    BEGIN;
  }
} {}
do_test e_fkey-43.7 {
  catchsql { DELETE FROM parent WHERE x = 'key1' }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-43.8 {
  execsql { DELETE FROM parent WHERE x = 'key2' }
} {}
do_test e_fkey-43.9 {
  catchsql COMMIT
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-43.10 {
  execsql {
    UPDATE child2 SET c = NULL;
    COMMIT;
  }
} {}

    ROLLBACK;
    DELETE FROM parent WHERE a = 'A';
    SELECT * FROM parent;
  }
} {ONE two three}
do_test e_fkey-49.4 {
  catchsql { UPDATE parent SET a = '' WHERE a = 'oNe' }
} {1 {FOREIGN KEY constraint failed}}


#-------------------------------------------------------------------------
# EVIDENCE-OF: R-11856-19836
#
# Test an example from the "ON DELETE and ON UPDATE Actions" section 
# of foreignkeys.html. This example shows that adding an "ON DELETE DEFAULT"

    );
    INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');
    INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);
  }
} {}
do_test e_fkey-50.2 {
  catchsql { DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.' }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-50.3 {
  execsql {
    INSERT INTO artist VALUES(0, 'Unknown Artist');
    DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';
  }
} {}
do_test e_fkey-50.4 {

  execsql { 
    DELETE FROM c1;
    DELETE FROM c2;
    DELETE FROM c3;
  }
  execsql { INSERT INTO c5 VALUES('a', 'b') }
  catchsql { DROP TABLE p }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-58.2 {
  execsql { SELECT * FROM p }
} {a b}
do_test e_fkey-58.3 {
  catchsql {
    BEGIN;
      DROP TABLE p;
  }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-58.4 {
  execsql {
    SELECT * FROM p;
    SELECT * FROM c5;
    ROLLBACK;
  }
} {a b a b}

  execsql {
    BEGIN;
      DROP TABLE p;
  }
} {}
do_test e_fkey-59.3 {
  catchsql COMMIT
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-59.4 {
  execsql { CREATE TABLE p(a, b, PRIMARY KEY(a, b)) }
  catchsql COMMIT
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-59.5 {
  execsql { INSERT INTO p VALUES('a', 'b') }
  execsql COMMIT
} {}

#-------------------------------------------------------------------------
# Any "foreign key mismatch" errors encountered while running an implicit

    execsql { INSERT INTO c VALUES('x', 'x', NULL)  }
    execsql { INSERT INTO c VALUES('y', NULL, 'x')  }
    execsql { INSERT INTO c VALUES('z', NULL, NULL) }

    # Check that the FK is enforced properly if there are no NULL values 
    # in the child key columns.
    catchsql { INSERT INTO c VALUES('a', 2, 4) }
  } {1 {FOREIGN KEY constraint failed}}
}

#-------------------------------------------------------------------------
# Test that SQLite does not support the SET CONSTRAINT statement. And
# that it is possible to create both immediate and deferred constraints.
#
# EVIDENCE-OF: R-21599-16038 In SQLite, a foreign key constraint is

    CREATE TABLE ci(c, d, 
      FOREIGN KEY(c, d) REFERENCES p DEFERRABLE INITIALLY IMMEDIATE);
    BEGIN;
  }
} {}
do_test e_fkey-62.4 {
  catchsql { INSERT INTO ci VALUES('x', 'y') }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-62.5 {
  catchsql { INSERT INTO cd VALUES('x', 'y') }
} {0 {}}
do_test e_fkey-62.6 {
  catchsql { COMMIT }
} {1 {FOREIGN KEY constraint failed}}
do_test e_fkey-62.7 {
  execsql { 
    DELETE FROM cd;
    COMMIT;
  }
} {}

#
do_execsql_test e_insert-4.1.0 {
  INSERT INTO a4 VALUES(1, 'a');
  INSERT INTO a4 VALUES(2, 'a');
  INSERT INTO a4 VALUES(3, 'a');
} {}
foreach {tn sql error ac data } {
  1.1  "INSERT INTO a4 VALUES(2,'b')"  {UNIQUE constraint failed: a4.c}  1 {1 a 2 a 3 a}
  1.2  "INSERT OR REPLACE INTO a4 VALUES(2, 'b')"            {}  1 {1 a 3 a 2 b}
  1.3  "INSERT OR IGNORE INTO a4 VALUES(3, 'c')"             {}  1 {1 a 3 a 2 b}
  1.4  "BEGIN" {} 0 {1 a 3 a 2 b}
  1.5  "INSERT INTO a4 VALUES(1, 'd')" {UNIQUE constraint failed: a4.c}  0 {1 a 3 a 2 b}
  1.6  "INSERT OR ABORT INTO a4 VALUES(1, 'd')" 
        {UNIQUE constraint failed: a4.c}  0 {1 a 3 a 2 b}
  1.7  "INSERT OR ROLLBACK INTO a4 VALUES(1, 'd')" 
        {UNIQUE constraint failed: a4.c}  1 {1 a 3 a 2 b}
  1.8  "INSERT INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"
        {UNIQUE constraint failed: a4.c}  1 {1 a 3 a 2 b}
  1.9  "INSERT OR FAIL INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"
        {UNIQUE constraint failed: a4.c}  1 {1 a 3 a 2 b 4 e}

  2.1  "INSERT INTO a4 VALUES(2,'f')"  
        {UNIQUE constraint failed: a4.c}  1 {1 a 3 a 2 b 4 e}
  2.2  "REPLACE INTO a4 VALUES(2, 'f')" {}  1 {1 a 3 a 4 e 2 f}
} {
  do_catchsql_test e_insert-4.1.$tn.1 $sql [list [expr {$error!=""}] $error]
  do_execsql_test  e_insert-4.1.$tn.2 {SELECT * FROM a4} [list {*}$data]
  do_test          e_insert-4.1.$tn.3 {sqlite3_get_autocommit db} $ac
}

  INSERT INTO t3 VALUES(1, 'one');
  INSERT INTO t3 VALUES(2, 'two');
  INSERT INTO t3 VALUES(3, 'three');
  INSERT INTO t3 VALUES(4, 'four');
} {}
foreach {tn sql error ac data } {
  1  "UPDATE t3 SET b='one' WHERE a=3" 
     {UNIQUE constraint failed: t3.b} 1 {1 one 2 two 3 three 4 four}

  2  "UPDATE OR REPLACE t3 SET b='one' WHERE a=3" 
     {} 1 {2 two 3 one 4 four}

  3  "UPDATE OR FAIL t3 SET b='three'"
     {UNIQUE constraint failed: t3.b} 1 {2 three 3 one 4 four}

  4  "UPDATE OR IGNORE t3 SET b='three' WHERE a=3" 
     {} 1 {2 three 3 one 4 four}

  5  "UPDATE OR ABORT t3 SET b='three' WHERE a=3" 
     {UNIQUE constraint failed: t3.b} 1 {2 three 3 one 4 four}

  6  "BEGIN" {} 0 {2 three 3 one 4 four}

  7  "UPDATE t3 SET b='three' WHERE a=3" 
     {UNIQUE constraint failed: t3.b} 0 {2 three 3 one 4 four}

  8  "UPDATE OR ABORT t3 SET b='three' WHERE a=3" 
     {UNIQUE constraint failed: t3.b} 0 {2 three 3 one 4 four}

  9  "UPDATE OR FAIL t3 SET b='two'"
     {UNIQUE constraint failed: t3.b} 0 {2 two 3 one 4 four}

  10 "UPDATE OR IGNORE t3 SET b='four' WHERE a=3"
     {} 0 {2 two 3 one 4 four}

  11 "UPDATE OR REPLACE t3 SET b='four' WHERE a=3"
     {} 0 {2 two 3 four}

  12 "UPDATE OR ROLLBACK t3 SET b='four'"
     {UNIQUE constraint failed: t3.b} 1 {2 three 3 one 4 four}
} {
  do_catchsql_test e_update-1.8.$tn.1 $sql [list [expr {$error!=""}] $error]
  do_execsql_test  e_update-1.8.$tn.2 {SELECT * FROM t3} [list {*}$data]
  do_test          e_update-1.8.$tn.3 {sqlite3_get_autocommit db} $ac
}

  CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
  INSERT INTO t1 VALUES('abc', 'def');
}
do_test 2.2 {
  error_messages "INSERT INTO t1 VALUES('ghi', 'def')"
} [list {*}{
    SQLITE_ERROR      {SQL logic error or missing database} 
    SQLITE_CONSTRAINT {UNIQUE constraint failed: t1.b}
}]
verify_ex_errcode 2.2b SQLITE_CONSTRAINT_UNIQUE
do_test 2.3 {
  error_messages_v2 "INSERT INTO t1 VALUES('ghi', 'def')"
} [list {*}{
    SQLITE_CONSTRAINT {UNIQUE constraint failed: t1.b}
    SQLITE_CONSTRAINT {UNIQUE constraint failed: t1.b}
}]
verify_ex_errcode 2.3b SQLITE_CONSTRAINT_UNIQUE

#-------------------------------------------------------------------------
# Test SQLITE_SCHEMA errors. And, for _v2(), test that if the schema
# change invalidates the SQL statement itself the error message is returned
# correctly.

  CREATE TABLE t9(a REFERENCES nosuchtable, b);
  CREATE TABLE t10(a REFERENCES t9(c) /D/, b);
}


set FkeySimpleTests {
  1.1  "INSERT INTO t2 VALUES(1, 3)"      {1 {FOREIGN KEY constraint failed}}
  1.2  "INSERT INTO t1 VALUES(1, 2)"      {0 {}}
  1.3  "INSERT INTO t2 VALUES(1, 3)"      {0 {}}
  1.4  "INSERT INTO t2 VALUES(2, 4)"      {1 {FOREIGN KEY constraint failed}}
  1.5  "INSERT INTO t2 VALUES(NULL, 4)"   {0 {}}
  1.6  "UPDATE t2 SET c=2 WHERE d=4"      {1 {FOREIGN KEY constraint failed}}
  1.7  "UPDATE t2 SET c=1 WHERE d=4"      {0 {}}
  1.9  "UPDATE t2 SET c=1 WHERE d=4"      {0 {}}
  1.10 "UPDATE t2 SET c=NULL WHERE d=4"   {0 {}}
  1.11 "DELETE FROM t1 WHERE a=1"         {1 {FOREIGN KEY constraint failed}}
  1.12 "UPDATE t1 SET a = 2"              {1 {FOREIGN KEY constraint failed}}
  1.13 "UPDATE t1 SET a = 1"              {0 {}}

  2.1  "INSERT INTO t4 VALUES(1, 3)"      {1 {FOREIGN KEY constraint failed}}
  2.2  "INSERT INTO t3 VALUES(1, 2)"      {0 {}}
  2.3  "INSERT INTO t4 VALUES(1, 3)"      {0 {}}

  4.1  "INSERT INTO t8 VALUES(1, 3)"      {1 {FOREIGN KEY constraint failed}}
  4.2  "INSERT INTO t7 VALUES(2, 1)"      {0 {}}
  4.3  "INSERT INTO t8 VALUES(1, 3)"      {0 {}}
  4.4  "INSERT INTO t8 VALUES(2, 4)"      {1 {FOREIGN KEY constraint failed}}
  4.5  "INSERT INTO t8 VALUES(NULL, 4)"   {0 {}}
  4.6  "UPDATE t8 SET c=2 WHERE d=4"      {1 {FOREIGN KEY constraint failed}}
  4.7  "UPDATE t8 SET c=1 WHERE d=4"      {0 {}}
  4.9  "UPDATE t8 SET c=1 WHERE d=4"      {0 {}}
  4.10 "UPDATE t8 SET c=NULL WHERE d=4"   {0 {}}
  4.11 "DELETE FROM t7 WHERE b=1"         {1 {FOREIGN KEY constraint failed}}
  4.12 "UPDATE t7 SET b = 2"              {1 {FOREIGN KEY constraint failed}}
  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"}}
}

    INSERT INTO i VALUES(35);
    INSERT INTO j VALUES('35.0');
    SELECT j, typeof(j) FROM j;
  }
} {35.0 text}
do_test fkey2-1.5.2 {
  catchsql { DELETE FROM i }
} {1 {FOREIGN KEY constraint failed}}

# Same test using a regular primary key with integer affinity.
drop_all_tables
do_test fkey2-1.6.1 {
  execsql {
    CREATE TABLE i(i INT UNIQUE);
    CREATE TABLE j(j REFERENCES i(i));
    INSERT INTO i VALUES('35.0');
    INSERT INTO j VALUES('35.0');
    SELECT j, typeof(j) FROM j;
    SELECT i, typeof(i) FROM i;
  }
} {35.0 text 35 integer}
do_test fkey2-1.6.2 {
  catchsql { DELETE FROM i }
} {1 {FOREIGN KEY constraint failed}}

# Use a collation sequence on the parent key.
drop_all_tables
do_test fkey2-1.7.1 {
  execsql {
    CREATE TABLE i(i TEXT COLLATE nocase PRIMARY KEY);
    CREATE TABLE j(j TEXT COLLATE binary REFERENCES i(i));
    INSERT INTO i VALUES('SQLite');
    INSERT INTO j VALUES('sqlite');
  }
  catchsql { DELETE FROM i }
} {1 {FOREIGN KEY constraint failed}}

# Use the parent key collation even if it is default and the child key
# has an explicit value.
drop_all_tables
do_test fkey2-1.7.2 {
  execsql {
    CREATE TABLE i(i TEXT PRIMARY KEY);        -- Colseq is "BINARY"
    CREATE TABLE j(j TEXT COLLATE nocase REFERENCES i(i));
    INSERT INTO i VALUES('SQLite');
  }
  catchsql { INSERT INTO j VALUES('sqlite') }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-1.7.3 {
  execsql {
    INSERT INTO i VALUES('sqlite');
    INSERT INTO j VALUES('sqlite');
    DELETE FROM i WHERE i = 'SQLite';
  }
  catchsql { DELETE FROM i WHERE i = 'sqlite' }
} {1 {FOREIGN KEY constraint failed}}

#-------------------------------------------------------------------------
# This section (test cases fkey2-2.*) contains tests to check that the
# deferred foreign key constraint logic works.
#
proc fkey2-2-test {tn nocommit sql {res {}}} {
  if {$res eq "FKV"} {
    set expected {1 {FOREIGN KEY constraint failed}}
  } else {
    set expected [list 0 $res]
  }
  do_test fkey2-2.$tn [list catchsql $sql] $expected
  if {$nocommit} {
    do_test fkey2-2.${tn}c {
      catchsql COMMIT
    } {1 {FOREIGN KEY constraint failed}}
  }
}

fkey2-2-test 1 0 {
  CREATE TABLE node(
    nodeid PRIMARY KEY,
    parent REFERENCES node DEFERRABLE INITIALLY DEFERRED

fkey2-2-test 62 0   "DELETE FROM leaf"
fkey2-2-test 63 0   "DELETE FROM node"
fkey2-2-test 64 1   "INSERT INTO leaf VALUES('a', 1)"
fkey2-2-test 65 1   "INSERT INTO leaf VALUES('b', 2)"
fkey2-2-test 66 1   "INSERT INTO leaf VALUES('c', 1)"
do_test fkey2-2-test-67 {
  catchsql          "INSERT INTO node SELECT parent, 3 FROM leaf"
} {1 {UNIQUE constraint failed: node.nodeid}}
fkey2-2-test 68 0 "COMMIT"           FKV
fkey2-2-test 69 1   "INSERT INTO node VALUES(1, NULL)"
fkey2-2-test 70 0   "INSERT INTO node VALUES(2, NULL)"
fkey2-2-test 71 0 "COMMIT"

fkey2-2-test 72 0 "BEGIN"
fkey2-2-test 73 1   "DELETE FROM node"

    INSERT INTO ab VALUES(1, 'b');
    INSERT INTO cd VALUES(1, 'd');
    INSERT INTO ef VALUES(1, 'e');
  }
} {}
do_test fkey2-3.1.3 {
  catchsql { UPDATE ab SET a = 5 }
} {1 {CHECK constraint failed: ef}}
do_test fkey2-3.1.4 {
  execsql { SELECT * FROM ab }
} {1 b}
do_test fkey2-3.1.4 {
  execsql BEGIN;
  catchsql { UPDATE ab SET a = 5 }
} {1 {CHECK constraint failed: ef}}
do_test fkey2-3.1.5 {
  execsql COMMIT;
  execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
} {1 b 1 d 1 e}

do_test fkey2-3.2.1 {
  execsql BEGIN;
  catchsql { DELETE FROM ab }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-3.2.2 {
  execsql COMMIT
  execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
} {1 b 1 d 1 e}

#-------------------------------------------------------------------------
# Test cases fkey2-4.* test that recursive foreign key actions 

  execsql {
    CREATE TABLE t1(a PRIMARY KEY, b);
    CREATE TABLE t2(c INTEGER PRIMARY KEY REFERENCES t1, b);
  }
} {}
do_test fkey2-7.2 {
  catchsql { INSERT INTO t2 VALUES(1, 'A'); }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-7.3 {
  execsql { 
    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t1 VALUES(2, 3);
    INSERT INTO t2 VALUES(1, 'A');
  }
} {}
do_test fkey2-7.4 {
  execsql { UPDATE t2 SET c = 2 }
} {}
do_test fkey2-7.5 {
  catchsql { UPDATE t2 SET c = 3 }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-7.6 {
  catchsql { DELETE FROM t1 WHERE a = 2 }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-7.7 {
  execsql { DELETE FROM t1 WHERE a = 1 }
} {}
do_test fkey2-7.8 {
  catchsql { UPDATE t1 SET a = 3 }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-7.9 {
  catchsql { UPDATE t2 SET rowid = 3 }
} {1 {FOREIGN KEY constraint failed}}

#-------------------------------------------------------------------------
# Test that it is not possible to enable/disable FK support while a
# transaction is open.
# 
drop_all_tables
proc fkey2-8-test {tn zSql value} {

  }
} {1 2}
do_test fkey2-9.1.4 {
  execsql { SELECT * FROM t1 }
} {2 two}
do_test fkey2-9.1.5 {
  catchsql { DELETE FROM t1 }
} {1 {FOREIGN KEY constraint failed}}

do_test fkey2-9.2.1 {
  execsql {
    CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c));
    CREATE TABLE cc(d DEFAULT 3, e DEFAULT 1, f DEFAULT 2,
        FOREIGN KEY(f, d) REFERENCES pp 
        ON UPDATE SET DEFAULT 

  execsql "INSERT INTO t2 VALUES('two')"
} {}
do_test fkey2-12.1.3 { 
  execsql "UPDATE t1 SET b = 'four' WHERE b = 'one'"
} {}
do_test fkey2-12.1.4 { 
  catchsql "UPDATE t1 SET b = 'five' WHERE b = 'two'"
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-12.1.5 { 
  execsql "DELETE FROM t1 WHERE b = 'two'"
} {}
do_test fkey2-12.1.6 { 
  catchsql "COMMIT"
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-12.1.7 { 
  execsql {
    INSERT INTO t1 VALUES(2, 'two');
    COMMIT;
  }
} {}

  execsql {
    DROP TABLE t2;
    CREATE TABLE t2(y REFERENCES t1 ON DELETE RESTRICT);
    INSERT INTO t2 VALUES('a');
    INSERT INTO t2 VALUES('b');
  }
  catchsql { DELETE FROM t1 }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-12.2.4 {
  execsql {
    SELECT * FROM t1;
    SELECT * FROM t2;
  }
} {A B a b}

    UPDATE up SET c34 = 'possibly';
    SELECT c38, c39 FROM down;
    DELETE FROM down;
  }
} {no possibly}
do_test fkey2-12.3.3 {
  catchsql { INSERT INTO down(c39, c38) VALUES('yes', 'no') }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-12.3.4 {
  execsql { 
    INSERT INTO up(c34, c35) VALUES('yes', 'no');
    INSERT INTO down(c39, c38) VALUES('yes', 'no');
  }
  catchsql { DELETE FROM up WHERE c34 = 'yes' }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-12.3.5 {
  execsql { 
    DELETE FROM up WHERE c34 = 'possibly';
    SELECT c34, c35 FROM up;
    SELECT c39, c38 FROM down;
  }
} {yes no yes no}

} {}
foreach {tn stmt} {
  1   "REPLACE INTO pp VALUES(1, 4, 5)"
  2   "REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 3, 4)"
} {
  do_test fkey2-13.1.$tn.1 {
    catchsql $stmt
  } {1 {FOREIGN KEY constraint failed}}
  do_test fkey2-13.1.$tn.2 {
    execsql {
      SELECT * FROM pp;
      SELECT * FROM cc;
    }
  } {1 2 3 2 3 1}
  do_test fkey2-13.1.$tn.3 {
    execsql BEGIN;
    catchsql $stmt
  } {1 {FOREIGN KEY constraint failed}}
  do_test fkey2-13.1.$tn.4 {
    execsql {
      COMMIT;
      SELECT * FROM pp;
      SELECT * FROM cc;
    }
  } {1 2 3 2 3 1}

  } [list \
    {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")}                    \
    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)}     \
    {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
  ]
  do_test fkey2-14.2.2.3 {
    catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
  } {1 {FOREIGN KEY constraint failed}}
  do_test fkey2-14.2.2.4 {
    execsql { INSERT INTO t4 VALUES(1, NULL) }
  } {}
  do_test fkey2-14.2.2.5 {
    catchsql { UPDATE t4 SET b = 5 }
  } {1 {FOREIGN KEY constraint failed}}
  do_test fkey2-14.2.2.6 {
    catchsql { UPDATE t4 SET b = 1 }
  } {0 {}}
  do_test fkey2-14.2.2.7 {
    execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
  } {}

  } [list \
    {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")}                    \
    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)}     \
    {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
  ]
  do_test fkey2-14.2tmp.2.3 {
    catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
  } {1 {FOREIGN KEY constraint failed}}
  do_test fkey2-14.2tmp.2.4 {
    execsql { INSERT INTO t4 VALUES(1, NULL) }
  } {}
  do_test fkey2-14.2tmp.2.5 {
    catchsql { UPDATE t4 SET b = 5 }
  } {1 {FOREIGN KEY constraint failed}}
  do_test fkey2-14.2tmp.2.6 {
    catchsql { UPDATE t4 SET b = 1 }
  } {0 {}}
  do_test fkey2-14.2tmp.2.7 {
    execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
  } {}

  } [list \
    {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")}                    \
    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)}     \
    {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
  ]
  do_test fkey2-14.2aux.2.3 {
    catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
  } {1 {FOREIGN KEY constraint failed}}
  do_test fkey2-14.2aux.2.4 {
    execsql { INSERT INTO t4 VALUES(1, NULL) }
  } {}
  do_test fkey2-14.2aux.2.5 {
    catchsql { UPDATE t4 SET b = 5 }
  } {1 {FOREIGN KEY constraint failed}}
  do_test fkey2-14.2aux.2.6 {
    catchsql { UPDATE t4 SET b = 1 }
  } {0 {}}
  do_test fkey2-14.2aux.2.7 {
    execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
  } {}
}

    INSERT INTO t1 VALUES('a', 1);
    CREATE TABLE t2(x REFERENCES t1);
    INSERT INTO t2 VALUES('a');
  }
} {}
do_test fkey-2.14.3.3 {
  catchsql { DROP TABLE t1 }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey-2.14.3.4 {
  execsql {
    DELETE FROM t2;
    DROP TABLE t1;
  }
} {}
do_test fkey-2.14.3.4 {
  catchsql { INSERT INTO t2 VALUES('x') }
} {1 {no such table: main.t1}}
do_test fkey-2.14.3.5 {
  execsql {
    CREATE TABLE t1(x PRIMARY KEY);
    INSERT INTO t1 VALUES('x');
  }
  execsql { INSERT INTO t2 VALUES('x') }
} {}
do_test fkey-2.14.3.6 {
  catchsql { DROP TABLE t1 }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey-2.14.3.7 {
  execsql {
    DROP TABLE t2;
    DROP TABLE t1;
  }
} {}
do_test fkey-2.14.3.8 {

  } {}
  do_test fkey2-16.1.$tn.2 {
    execsql { UPDATE self SET a = 14, b = 14 }
  } {}

  do_test fkey2-16.1.$tn.3 {
    catchsql { UPDATE self SET b = 15 }
  } {1 {FOREIGN KEY constraint failed}}

  do_test fkey2-16.1.$tn.4 {
    catchsql { UPDATE self SET a = 15 }
  } {1 {FOREIGN KEY constraint failed}}

  do_test fkey2-16.1.$tn.5 {
    catchsql { UPDATE self SET a = 15, b = 16 }
  } {1 {FOREIGN KEY constraint failed}}

  do_test fkey2-16.1.$tn.6 {
    catchsql { UPDATE self SET a = 17, b = 17 }
  } {0 {}}

  do_test fkey2-16.1.$tn.7 {
    execsql { DELETE FROM self }
  } {}
  do_test fkey2-16.1.$tn.8 {
    catchsql { INSERT INTO self VALUES(20, 21) }
  } {1 {FOREIGN KEY constraint failed}}
}

#-------------------------------------------------------------------------
# This next block of tests, fkey2-17.*, tests that if "PRAGMA count_changes"
# is turned on statements that violate immediate FK constraints return
# SQLITE_CONSTRAINT immediately, not after returning a number of rows.
# Whereas statements that violate deferred FK constraints return the number

} {1 1 1 1 1}
do_test fkey2-17.1.6 {
  catchsql {
    BEGIN;
      INSERT INTO one VALUES(0, 0, 0);
      UPDATE two SET e=e+1, f=f+1;
  }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-17.1.7 {
  execsql { SELECT * FROM one }
} {1 2 3 2 3 4 3 4 5 0 0 0}
do_test fkey2-17.1.8 {
  execsql { SELECT * FROM two }
} {1 2 3 2 3 4 3 4 5}
do_test fkey2-17.1.9 {

  rename auth {}
  proc auth {args} {
    if {[lindex $args 1] == "long"} {return SQLITE_IGNORE}
    return SQLITE_OK
  }
  do_test fkey2-18.8 {
    catchsql { INSERT INTO short VALUES(1, 3, 2) }
  } {1 {FOREIGN KEY constraint failed}}
  do_test fkey2-18.9 {
    execsql { INSERT INTO short VALUES(1, 3, NULL) }
  } {}
  do_test fkey2-18.10 {
    execsql { SELECT * FROM short }
  } {1 3 2 1 3 {}}
  do_test fkey2-18.11 {
    catchsql { UPDATE short SET f = 2 WHERE f IS NULL }
  } {1 {FOREIGN KEY constraint failed}}

  db auth {}
  unset authargs
}


do_test fkey2-19.1 {

  3 "INSERT OR ABORT"
  4 "INSERT OR ROLLBACK"
  5 "INSERT OR REPLACE"
  6 "INSERT OR FAIL"
} {
  do_test fkey2-20.2.$tn.1 {
    catchsql "$insert INTO cc VALUES(1, 2)"
  } {1 {FOREIGN KEY constraint failed}}
  do_test fkey2-20.2.$tn.2 {
    execsql { SELECT * FROM cc }
  } {}
  do_test fkey2-20.2.$tn.3 {
    execsql {
      BEGIN;
        INSERT INTO pp VALUES(2, 'two');
        INSERT INTO cc VALUES(1, 2);
    }
    catchsql "$insert INTO cc VALUES(3, 4)"
  } {1 {FOREIGN KEY constraint failed}}
  do_test fkey2-20.2.$tn.4 {
    execsql { COMMIT ; SELECT * FROM cc }
  } {1 2}
  do_test fkey2-20.2.$tn.5 {
    execsql { DELETE FROM cc ; DELETE FROM pp }
  } {}
}

    execsql {
      INSERT INTO pp VALUES(2, 'two');
      INSERT INTO cc VALUES(1, 2);
    }
  } {}
  do_test fkey2-20.3.$tn.2 {
    catchsql "$update pp SET a = 1"
  } {1 {FOREIGN KEY constraint failed}}
  do_test fkey2-20.3.$tn.3 {
    execsql { SELECT * FROM pp }
  } {2 two}
  do_test fkey2-20.3.$tn.4 {
    catchsql "$update cc SET d = 1"
  } {1 {FOREIGN KEY constraint failed}}
  do_test fkey2-20.3.$tn.5 {
    execsql { SELECT * FROM cc }
  } {1 2}
  do_test fkey2-20.3.$tn.6 {
    execsql {
      BEGIN;
        INSERT INTO pp VALUES(3, 'three');
    }
    catchsql "$update pp SET a = 1 WHERE a = 2"
  } {1 {FOREIGN KEY constraint failed}}
  do_test fkey2-20.3.$tn.7 {
    execsql { COMMIT ; SELECT * FROM pp }
  } {2 two 3 three}
  do_test fkey2-20.3.$tn.8 {
    execsql {
      BEGIN;
        INSERT INTO cc VALUES(2, 2);
    }
    catchsql "$update cc SET d = 1 WHERE c = 1"
  } {1 {FOREIGN KEY constraint failed}}
  do_test fkey2-20.3.$tn.9 {
    execsql { COMMIT ; SELECT * FROM cc }
  } {1 2 2 2}
  do_test fkey2-20.3.$tn.10 {
    execsql { DELETE FROM cc ; DELETE FROM pp }
  } {}
}

    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
    CREATE TABLE t2(e REFERENCES t1, f);
    CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c));
  }
} {}
do_test fkey2-genfkey.1.2 {
  catchsql { INSERT INTO t2 VALUES(1, 2) }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-genfkey.1.3 {
  execsql {
    INSERT INTO t1 VALUES(1, 2, 3);
    INSERT INTO t2 VALUES(1, 2);
  }
} {}
do_test fkey2-genfkey.1.4 {
  execsql { INSERT INTO t2 VALUES(NULL, 3) }
} {}
do_test fkey2-genfkey.1.5 {
  catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-genfkey.1.6 {
  execsql { UPDATE t2 SET e = 1 WHERE e IS NULL }
} {}
do_test fkey2-genfkey.1.7 {
  execsql { UPDATE t2 SET e = NULL WHERE f = 3 }
} {}
do_test fkey2-genfkey.1.8 {
  catchsql { UPDATE t1 SET a = 10 }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-genfkey.1.9 {
  catchsql { UPDATE t1 SET a = NULL }
} {1 {datatype mismatch}}
do_test fkey2-genfkey.1.10 {
  catchsql { DELETE FROM t1 }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-genfkey.1.11 {
  execsql { UPDATE t2 SET e = NULL }
} {}
do_test fkey2-genfkey.1.12 {
  execsql { 
    UPDATE t1 SET a = 10;
    DELETE FROM t1;
    DELETE FROM t2;
  }
} {}
do_test fkey2-genfkey.1.13 {
  execsql {
    INSERT INTO t3 VALUES(1, NULL, NULL);
    INSERT INTO t3 VALUES(1, 2, NULL);
    INSERT INTO t3 VALUES(1, NULL, 3);
  }
} {}
do_test fkey2-genfkey.1.14 {
  catchsql { INSERT INTO t3 VALUES(3, 1, 4) }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-genfkey.1.15 {
  execsql { 
    INSERT INTO t1 VALUES(1, 1, 4);
    INSERT INTO t3 VALUES(3, 1, 4);
  }
} {}
do_test fkey2-genfkey.1.16 {
  catchsql { DELETE FROM t1 }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-genfkey.1.17 {
  catchsql { UPDATE t1 SET b = 10}
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-genfkey.1.18 {
  execsql { UPDATE t1 SET a = 10}
} {}
do_test fkey2-genfkey.1.19 {
  catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3}
} {1 {FOREIGN KEY constraint failed}}

drop_all_tables
do_test fkey2-genfkey.2.1 {
  execsql {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
    CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f);
    CREATE TABLE t3(g, h, i, 

    INSERT INTO tdd08_b VALUES(100,200,300);
  }
} {}
do_test fkey2-dd08e5.1.2 {
  catchsql {
    DELETE FROM tdd08;
  }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-dd08e5.1.3 {
  execsql {
    SELECT * FROM tdd08;
  }
} {200 300}
do_test fkey2-dd08e5.1.4 {
  catchsql {
    INSERT INTO tdd08_b VALUES(400,500,300);
  }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-dd08e5.1.5 {
  catchsql {
    UPDATE tdd08_b SET x=x+1;
  }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-dd08e5.1.6 {
  catchsql {
    UPDATE tdd08 SET a=a+1;
  }
} {1 {FOREIGN KEY constraint failed}}

#-------------------------------------------------------------------------
# Verify that ticket ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba
# fixed.
#
do_test fkey2-ce7c13.1.1 {
  execsql {
    CREATE TABLE tce71(a INTEGER PRIMARY KEY, b);
    CREATE UNIQUE INDEX ice71 ON tce71(a,b);
    INSERT INTO tce71 VALUES(100,200);
    CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b));
    INSERT INTO tce72 VALUES(300,100,200);
    UPDATE tce71 set b = 200 where a = 100;
    SELECT * FROM tce71, tce72;
  }
} {100 200 300 100 200}
do_test fkey2-ce7c13.1.2 {
  catchsql {
    UPDATE tce71 set b = 201 where a = 100;
  }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-ce7c13.1.3 {
  catchsql {
    UPDATE tce71 set a = 101 where a = 100;
  }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-ce7c13.1.4 {
  execsql {
    CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b));
    INSERT INTO tce73 VALUES(100,200);
    CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b));
    INSERT INTO tce74 VALUES(300,100,200);
    UPDATE tce73 set b = 200 where a = 100;
    SELECT * FROM tce73, tce74;
  }
} {100 200 300 100 200}
do_test fkey2-ce7c13.1.5 {
  catchsql {
    UPDATE tce73 set b = 201 where a = 100;
  }
} {1 {FOREIGN KEY constraint failed}}
do_test fkey2-ce7c13.1.6 {
  catchsql {
    UPDATE tce73 set a = 101 where a = 100;
  }
} {1 {FOREIGN KEY constraint failed}}

finish_test

  }
} {1 100 1 101 2 100 2 101}

do_test fkey3-1.2 {
  catchsql {
    DELETE FROM t1 WHERE x=100;
  }
} {1 {FOREIGN KEY constraint failed}}

do_test fkey3-1.3 {
  catchsql {
    DROP TABLE t1;
  }
} {1 {FOREIGN KEY constraint failed}}

do_test fkey3-1.4 {
  execsql {
    DROP TABLE t2;
  }
} {}

    UNIQUE(a, b),
    FOREIGN KEY(c, d) REFERENCES t3(a, b)
  );
  INSERT INTO t3 VALUES(1, 2, 1, 2);
} {}
do_catchsql_test 3.1.2 {
  INSERT INTO t3 VALUES(NULL, 2, 5, 2);
} {1 {FOREIGN KEY constraint failed}}
do_catchsql_test 3.1.3 {
  INSERT INTO t3 VALUES(NULL, 3, 5, 2);
} {1 {FOREIGN KEY constraint failed}}

do_execsql_test 3.2.1 {
  CREATE TABLE t4(a UNIQUE, b REFERENCES t4(a));
}
do_catchsql_test 3.2.2 {
  INSERT INTO t4 VALUES(NULL, 1);
} {1 {FOREIGN KEY constraint failed}}

do_execsql_test 3.3.1 {
  CREATE TABLE t5(a INTEGER PRIMARY KEY, b REFERENCES t5(a));
  INSERT INTO t5 VALUES(NULL, 1);
} {}
do_catchsql_test 3.3.2 {
  INSERT INTO t5 VALUES(NULL, 3);
} {1 {FOREIGN KEY constraint failed}}

do_execsql_test 3.4.1 {
  CREATE TABLE t6(a INTEGER PRIMARY KEY, b, c, d,
    FOREIGN KEY(c, d) REFERENCES t6(a, b)
  );
  CREATE UNIQUE INDEX t6i ON t6(b, a);
}
do_execsql_test 3.4.2  { INSERT INTO t6 VALUES(NULL, 'a', 1, 'a'); } {}
do_execsql_test 3.4.3  { INSERT INTO t6 VALUES(2, 'a', 2, 'a');    } {}
do_execsql_test 3.4.4  { INSERT INTO t6 VALUES(NULL, 'a', 1, 'a'); } {}
do_execsql_test 3.4.5  { INSERT INTO t6 VALUES(5, 'a', 2, 'a'); } {}
do_catchsql_test 3.4.6 { 
  INSERT INTO t6 VALUES(NULL, 'a', 65, 'a');    
} {1 {FOREIGN KEY constraint failed}}

do_execsql_test 3.4.7 {
  INSERT INTO t6 VALUES(100, 'one', 100, 'one');
  DELETE FROM t6 WHERE a = 100;
}
do_execsql_test 3.4.8 {
  INSERT INTO t6 VALUES(100, 'one', 100, 'one');

  );
  CREATE UNIQUE INDEX t7i ON t7(a, b);
}
do_execsql_test 3.5.2  { INSERT INTO t7 VALUES('x', 1, 'x', NULL) } {}
do_execsql_test 3.5.3  { INSERT INTO t7 VALUES('x', 2, 'x', 2) } {}
do_catchsql_test 3.5.4  { 
  INSERT INTO t7 VALUES('x', 450, 'x', NULL);
} {1 {FOREIGN KEY constraint failed}}
do_catchsql_test 3.5.5  { 
  INSERT INTO t7 VALUES('x', 450, 'x', 451);
} {1 {FOREIGN KEY constraint failed}}


do_execsql_test 3.6.1 {
  CREATE TABLE t8(a, b, c, d, e, FOREIGN KEY(c, d) REFERENCES t8(a, b));
  CREATE UNIQUE INDEX t8i1 ON t8(a, b);
  CREATE UNIQUE INDEX t8i2 ON t8(c);
  INSERT INTO t8 VALUES(1, 1, 1, 1, 1);
}
do_catchsql_test 3.6.2 { 
  UPDATE t8 SET d = 2; 
} {1 {FOREIGN KEY constraint failed}}
do_execsql_test 3.6.3 { UPDATE t8 SET d = 1; }
do_execsql_test 3.6.4 { UPDATE t8 SET e = 2; }

do_catchsql_test 3.6.5 {
  CREATE TABLE TestTable (
    id INTEGER PRIMARY KEY,
    name text,
    source_id integer not null,
    parent_id integer,

    foreign key(source_id, parent_id) references TestTable(source_id, id)
  );
  CREATE UNIQUE INDEX testindex on TestTable(source_id, id);
  PRAGMA foreign_keys=1;
  INSERT INTO TestTable VALUES (1, 'parent', 1, null);
  INSERT INTO TestTable VALUES (2, 'child', 1, 1);
  UPDATE TestTable SET parent_id=1000 where id=2;
} {1 {FOREIGN KEY constraint failed}}

finish_test

  CREATE INDEX t3v ON t3(v);
  INSERT INTO t1 VALUES(1),(2),(3),(4),(5);
  INSERT INTO t2 VALUES(1,1),(2,2);
  INSERT INTO t3 VALUES(3,3),(4,4);
} {}
do_test fkey6-1.2 {
  catchsql {DELETE FROM t1 WHERE x=2;}
} {1 {FOREIGN KEY constraint failed}}
do_test fkey6-1.3 {
  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
} {0 0 0}
do_test fkey6-1.4 {
  execsql {
    BEGIN;
    DELETE FROM t1 WHERE x=1;

  PRAGMA defer_foreign_keys;
  COMMIT;
  PRAGMA defer_foreign_keys;
  BEGIN;
} {1 0 1 0}
do_test fkey6-1.10.2 {
  catchsql {DELETE FROM t1 WHERE x=3}
} {1 {FOREIGN KEY constraint failed}}
db eval {ROLLBACK}

do_test fkey6-1.20 {
  execsql {
    BEGIN;
    DELETE FROM t1 WHERE x=1;
  }

    DROP TABLE c1;
  COMMIT;
  PRAGMA defer_foreign_keys;
} {0}


finish_test

      x INTEGER CHECK(tointeger(x) IS NOT NULL)
    );
  } {}
  do_test func4-3.2 {
    catchsql {
      INSERT INTO t1 (x) VALUES (NULL);
    }
  } {1 {CHECK constraint failed: t1}}
  do_test func4-3.3 {
    catchsql {
      INSERT INTO t1 (x) VALUES (NULL);
    }
  } {1 {CHECK constraint failed: t1}}
  do_test func4-3.4 {
    catchsql {
      INSERT INTO t1 (x) VALUES ('');
    }
  } {1 {CHECK constraint failed: t1}}
  do_test func4-3.5 {
    catchsql {
      INSERT INTO t1 (x) VALUES ('bad');
    }
  } {1 {CHECK constraint failed: t1}}
  do_test func4-3.6 {
    catchsql {
      INSERT INTO t1 (x) VALUES ('1234bad');
    }
  } {1 {CHECK constraint failed: t1}}
  do_test func4-3.7 {
    catchsql {
      INSERT INTO t1 (x) VALUES ('1234.56bad');
    }
  } {1 {CHECK constraint failed: t1}}
  do_test func4-3.8 {
    catchsql {
      INSERT INTO t1 (x) VALUES (1234);
    }
  } {0 {}}
  do_test func4-3.9 {
    catchsql {
      INSERT INTO t1 (x) VALUES (1234.56);
    }
  } {1 {CHECK constraint failed: t1}}
  do_test func4-3.10 {
    catchsql {
      INSERT INTO t1 (x) VALUES ('1234');
    }
  } {0 {}}
  do_test func4-3.11 {
    catchsql {
      INSERT INTO t1 (x) VALUES ('1234.56');
    }
  } {1 {CHECK constraint failed: t1}}
  do_test func4-3.12 {
    catchsql {
      INSERT INTO t1 (x) VALUES (ZEROBLOB(4));
    }
  } {1 {CHECK constraint failed: t1}}
  do_test func4-3.13 {
    catchsql {
      INSERT INTO t1 (x) VALUES (X'');
    }
  } {1 {CHECK constraint failed: t1}}
  do_test func4-3.14 {
    catchsql {
      INSERT INTO t1 (x) VALUES (X'1234');
    }
  } {1 {CHECK constraint failed: t1}}
  do_test func4-3.15 {
    catchsql {
      INSERT INTO t1 (x) VALUES (X'12345678');
    }
  } {1 {CHECK constraint failed: t1}}
  do_test func4-3.16 {
    catchsql {
      INSERT INTO t1 (x) VALUES ('1234.00');
    }
  } {1 {CHECK constraint failed: t1}}
  do_test func4-3.17 {
    catchsql {
      INSERT INTO t1 (x) VALUES (1234.00);
    }
  } {0 {}}
  do_test func4-3.18 {
    catchsql {
      INSERT INTO t1 (x) VALUES ('-9223372036854775809');
    }
  } {1 {CHECK constraint failed: t1}}
  if {$highPrecision(1)} {
    do_test func4-3.19 {
      catchsql {
        INSERT INTO t1 (x) VALUES (9223372036854775808);
      }
    } {1 {CHECK constraint failed: t1}}
  }
  do_execsql_test func4-3.20 {
    SELECT x FROM t1 ORDER BY x;
  } {1234 1234 1234}

  ifcapable floatingpoint {
    do_execsql_test func4-4.1 {
      CREATE TABLE t2(
        x REAL CHECK(toreal(x) IS NOT NULL)
      );
    } {}
    do_test func4-4.2 {
      catchsql {
        INSERT INTO t2 (x) VALUES (NULL);
      }
    } {1 {CHECK constraint failed: t2}}
    do_test func4-4.3 {
      catchsql {
        INSERT INTO t2 (x) VALUES (NULL);
      }
    } {1 {CHECK constraint failed: t2}}
    do_test func4-4.4 {
      catchsql {
        INSERT INTO t2 (x) VALUES ('');
      }
    } {1 {CHECK constraint failed: t2}}
    do_test func4-4.5 {
      catchsql {
        INSERT INTO t2 (x) VALUES ('bad');
      }
    } {1 {CHECK constraint failed: t2}}
    do_test func4-4.6 {
      catchsql {
        INSERT INTO t2 (x) VALUES ('1234bad');
      }
    } {1 {CHECK constraint failed: t2}}
    do_test func4-4.7 {
      catchsql {
        INSERT INTO t2 (x) VALUES ('1234.56bad');
      }
    } {1 {CHECK constraint failed: t2}}
    do_test func4-4.8 {
      catchsql {
        INSERT INTO t2 (x) VALUES (1234);
      }
    } {0 {}}
    do_test func4-4.9 {
      catchsql {

        INSERT INTO t2 (x) VALUES ('1234.56');
      }
    } {0 {}}
    do_test func4-4.12 {
      catchsql {
        INSERT INTO t2 (x) VALUES (ZEROBLOB(4));
      }
    } {1 {CHECK constraint failed: t2}}
    do_test func4-4.13 {
      catchsql {
        INSERT INTO t2 (x) VALUES (X'');
      }
    } {1 {CHECK constraint failed: t2}}
    do_test func4-4.14 {
      catchsql {
        INSERT INTO t2 (x) VALUES (X'1234');
      }
    } {1 {CHECK constraint failed: t2}}
    do_test func4-4.15 {
      catchsql {
        INSERT INTO t2 (x) VALUES (X'12345678');
      }
    } {1 {CHECK constraint failed: t2}}
    do_execsql_test func4-4.16 {
      SELECT x FROM t2 ORDER BY x;
    } {1234.0 1234.0 1234.56 1234.56}
  }
}

ifcapable floatingpoint {

    SELECT * FROM t5;
  }
} {111}
do_test in-10.2 {
  catchsql {
    INSERT INTO t5 VALUES(4);
  }
} {1 {CHECK constraint failed: t5}}

# Ticket #1821
#
# Type affinity applied to the right-hand side of an IN operator.
#
do_test in-11.1 {
  execsql {

do_test incrblob2-8.4 {
  execsql BEGIN
  set h [db incrblob t3 b 3]
  sqlite3_blob_read $h 0 20
} {cccccccccccccccccccc}
do_test incrblob2-8.5 {
  catchsql {UPDATE t3 SET a = 6 WHERE a > 3}
} {1 {UNIQUE constraint failed: t3.a}}
do_test incrblob2-8.6 {
  catchsql {UPDATE t3 SET a = 6 WHERE a > 3}
} {1 {UNIQUE constraint failed: t3.a}}
do_test incrblob2-8.7 {
  sqlite3_blob_read $h 0 20
} {cccccccccccccccccccc}
do_test incrblob2-8.8 {
  catchsql {UPDATE t3 SET a = 6 WHERE a = 3 OR a = 5}
} {1 {UNIQUE constraint failed: t3.a}}
do_test incrblob2-8.9 {
  set rc [catch {sqlite3_blob_read $h 0 20} msg]
  list $rc $msg
} {1 SQLITE_ABORT}
do_test incrblob2-8.X {
  close $h
} {}

    }
  } {}
  do_test index-19.2 {
    catchsql {
      BEGIN;
      INSERT INTO t7 VALUES(1);
    }
  } {1 {UNIQUE constraint failed: t7.a}}
  do_test index-19.3 {
    catchsql {
      BEGIN;
    }
  } {1 {cannot start a transaction within a transaction}}
  do_test index-19.4 {
    catchsql {
      INSERT INTO t8 VALUES(1);
    }
  } {1 {UNIQUE constraint failed: t8.a}}
  do_test index-19.5 {
    catchsql {
      BEGIN;
      COMMIT;
    }
  } {0 {}}
  do_test index-19.6 {

  }
} {1 1}
do_test index3-1.2 {
  catchsql {
    BEGIN;
    CREATE UNIQUE INDEX i1 ON t1(a);
  }
} {1 {UNIQUE constraint failed: t1.a}}
do_test index3-1.3 {
  catchsql COMMIT;
} {0 {}}
integrity_check index3-1.4

# This test corrupts the database file so it must be the last test
# in the series.

    INSERT INTO t2 VALUES(15);
    INSERT INTO t2 VALUES(35);
    INSERT INTO t2 VALUES(16);
  COMMIT;
}
do_catchsql_test 2.2 {
  CREATE UNIQUE INDEX i3 ON t2(x);
} {1 {UNIQUE constraint failed: t2.x}}


finish_test

  CREATE UNIQUE INDEX t3a ON t3(a) WHERE a<>999;
} {}
do_test index6-3.2 {
  # unable to insert a duplicate row a-value that is not 999.
  catchsql {
    INSERT INTO t3(a,b) VALUES(150, 'test1');
  }
} {1 {UNIQUE constraint failed: t3.a}}
do_test index6-3.3 {
  # can insert multiple rows with a==999 because such rows are not
  # part of the unique index.
  catchsql {
    INSERT INTO t3(a,b) VALUES(999, 'test1'), (999, 'test2');
  }
} {0 {}}

  CREATE UNIQUE INDEX t3a ON t3(a) WHERE a<>999;
} {}
do_test index7-3.2 {
  # unable to insert a duplicate row a-value that is not 999.
  catchsql {
    INSERT INTO t3(a,b) VALUES(150, 'test1');
  }
} {1 {UNIQUE constraint failed: t3.a}}
do_test index7-3.3 {
  # can insert multiple rows with a==999 because such rows are not
  # part of the unique index.
  catchsql {
    INSERT INTO t3(a,b) VALUES(999, 'test1'), (999, 'test2');
  }
} {0 {}}

    DELETE FROM t1;
    DELETE FROM t2;
    INSERT INTO t2 VALUES(9,1);
  }
  catchsql {
    INSERT INTO t1 SELECT * FROM t2;
  }
} {1 {CHECK constraint failed: t1}}
xferopt_test insert4-1.2 0
do_test insert4-1.3 {
  execsql {
    SELECT * FROM t1;
  }
} {}

xferopt_test insert4-2.3.2  0
do_test insert4-2.3.3 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
    SELECT * FROM t1;
  }
} {1 {CHECK constraint failed: t1}}
xferopt_test insert4-2.3.4 0

# Do not run the transfer optimization if there is a DISTINCT
#
do_test insert4-2.4.1 {
  execsql {
    DELETE FROM t3;
    INSERT INTO t3 SELECT DISTINCT * FROM t2;
    SELECT * FROM t3;
  }
} {9 1 1 9}
xferopt_test insert4-2.4.2 0
do_test insert4-2.4.3 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 SELECT DISTINCT * FROM t2;
  }
} {1 {CHECK constraint failed: t1}}
xferopt_test insert4-2.4.4 0

# The following procedure constructs two tables then tries to transfer
# data from one table to the other.  Checks are made to make sure the
# transfer is successful and that the transfer optimization was used or
# not, as appropriate.
#

do_test insert4-6.6 {
  execsql {
    CREATE TABLE t6b(x CHECK( x<>'abc' COLLATE nocase ));
  }
  catchsql {
    INSERT INTO t6b SELECT * FROM t6a;
  }
} {1 {CHECK constraint failed: t6b}}
do_test insert4-6.7 {
  execsql {
    DROP TABLE t6b;
    CREATE TABLE t6b(x CHECK( x COLLATE nocase <>'abc' ));
  }
  catchsql {
    INSERT INTO t6b SELECT * FROM t6a;
  }
} {1 {CHECK constraint failed: t6b}}

# Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
# Disable the xfer optimization if the destination table contains
# a foreign key constraint
#
ifcapable foreignkey {
  do_test insert4-7.1 {

    execsql {
      DELETE FROM t7b;
      PRAGMA foreign_keys=ON;
    }
    catchsql {
      INSERT INTO t7b SELECT * FROM t7c;
    }
  } {1 {FOREIGN KEY constraint failed}}
  do_test insert4-7.4 {
    execsql {SELECT * FROM t7b}
  } {}
  do_test insert4-7.5 {
    set ::sqlite3_xferopt_count
  } {0}
  do_test insert4-7.6 {

    INSERT INTO t2 VALUES(-99,100);
    INSERT INTO t2 VALUES(1,3);
    SELECT * FROM t1;
  }
  catchsql {
    INSERT INTO t1 SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t1.a}}
do_test insert4-8.6 {
  execsql {
    SELECT * FROM t1;
  }
} {-99 100 1 2} 
do_test insert4-8.7 {
  execsql {
    DROP TABLE IF EXISTS t1;
    DROP TABLE IF EXISTS t2;
    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b);
    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y);
    INSERT INTO t1 VALUES(1,2);
    INSERT INTO t2 VALUES(-99,100);
    INSERT INTO t2 VALUES(1,3);
    SELECT * FROM t1;
  }
  catchsql {
    INSERT INTO t1 SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t1.a}}
do_test insert4-8.8 {
  execsql {
    SELECT * FROM t1;
  }
} {1 2} 
do_test insert4-8.9 {
  execsql {

    SELECT * FROM t1;
  }
  catchsql {
    BEGIN;
    INSERT INTO t1 VALUES(2,3);
    INSERT INTO t1 SELECT * FROM t2;
  }
} {1 {UNIQUE constraint failed: t1.a}}
do_test insert4-8.10 {
  catchsql {COMMIT}
} {1 {cannot commit - no transaction is active}}
do_test insert4-8.11 {
  execsql {
    SELECT * FROM t1;
  }

# failure.
#
do_test intpkey-1.6 {
  set r [catch {execsql {
     INSERT INTO t1 VALUES(5,'second','entry');
  }} msg]
  lappend r $msg
} {1 {UNIQUE constraint failed: t1.a}}
do_test intpkey-1.7 {
  execsql {
    SELECT rowid, * FROM t1;
  }
} {5 5 hello world}
do_test intpkey-1.8 {
  set r [catch {execsql {

} {
  # All tests after test 1 depend on conflict resolution. So end the
  # loop if that is not available in this build.
  ifcapable !conflict {
    if {$i>1} break
  }

  if {$t0} {set t1 {UNIQUE constraint failed: t1.a}}
  do_test memdb-5.$i {
    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
    if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
    set r0 [catch {execsql "
      DROP TABLE t1;
      CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1);
      INSERT INTO t1 SELECT * FROM t2;

    SELECT * FROM t5 ORDER BY a;
  }
} {1 2 3}
do_test misc1-7.4 {
  catchsql {
    INSERT INTO t5 VALUES(1,2,4);
  }
} {1 {UNIQUE constraint failed: t5.a, t5.b}}
do_test misc1-7.5 {
  catchsql {
    INSERT INTO t5 VALUES(0,2,4);
  }
} {0 {}}
do_test misc1-7.6 {
  execsql {

} {0 {1 2 3 4 5}}
do_test notnull-1.2 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {NOT NULL constraint failed: t1.a}}
verify_ex_errcode notnull-1.2b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-1.3 {
  catchsql {
    DELETE FROM t1;
    INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {0 {}}
do_test notnull-1.4 {
  catchsql {
    DELETE FROM t1;
    INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {NOT NULL constraint failed: t1.a}}
verify_ex_errcode notnull-1.4b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-1.5 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {NOT NULL constraint failed: t1.a}}
verify_ex_errcode notnull-1.5b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-1.6 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5);
    SELECT * FROM t1 order by a;
  }

} {0 {1 5 3 4 5}}
do_test notnull-1.10 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {NOT NULL constraint failed: t1.b}}
verify_ex_errcode notnull-1.10b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-1.11 {
  catchsql {
    DELETE FROM t1;
    INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
    SELECT * FROM t1 order by a;
  }

} {0 {1 2 6 4 5}}
do_test notnull-1.16 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {NOT NULL constraint failed: t1.c}}
verify_ex_errcode notnull-1.16b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-1.17 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5);
    SELECT * FROM t1 order by a;
  }
} {1 {NOT NULL constraint failed: t1.d}}
verify_ex_errcode notnull-1.17b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-1.18 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5);
    SELECT * FROM t1 order by a;
  }

} {0 {1 2 3 4 8}}
do_test notnull-1.20 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null);
    SELECT * FROM t1 order by a;
  }
} {1 {NOT NULL constraint failed: t1.e}}
verify_ex_errcode notnull-1.20b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-1.21 {
  catchsql {
    DELETE FROM t1;
    INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5);
    SELECT * FROM t1 order by a;
  }
} {0 {5 5 3 2 1}}

do_test notnull-2.1 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {NOT NULL constraint failed: t1.a}}
verify_ex_errcode notnull-2.1b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-2.2 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR REPLACE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {NOT NULL constraint failed: t1.a}}
verify_ex_errcode notnull-2.2b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-2.3 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR IGNORE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {0 {1 2 3 4 5}}
do_test notnull-2.4 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR ABORT t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {NOT NULL constraint failed: t1.a}}
verify_ex_errcode notnull-2.4b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-2.5 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET b=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {NOT NULL constraint failed: t1.b}}
verify_ex_errcode notnull-2.6b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-2.6 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR REPLACE t1 SET b=null, d=e, e=d;
    SELECT * FROM t1 ORDER BY a;

do_test notnull-2.10 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET e=null, a=b, b=a;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {NOT NULL constraint failed: t1.e}}
verify_ex_errcode notnull-2.10b SQLITE_CONSTRAINT_NOTNULL

do_test notnull-3.0 {
  execsql {
    CREATE INDEX t1a ON t1(a);
    CREATE INDEX t1b ON t1(b);
    CREATE INDEX t1c ON t1(c);

} {0 {1 2 3 4 5}}
do_test notnull-3.2 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {NOT NULL constraint failed: t1.a}}
verify_ex_errcode notnull-3.2b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-3.3 {
  catchsql {
    DELETE FROM t1;
    INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {0 {}}
do_test notnull-3.4 {
  catchsql {
    DELETE FROM t1;
    INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {NOT NULL constraint failed: t1.a}}
verify_ex_errcode notnull-3.4b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-3.5 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {NOT NULL constraint failed: t1.a}}
verify_ex_errcode notnull-3.5b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-3.6 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5);
    SELECT * FROM t1 order by a;
  }

} {0 {1 5 3 4 5}}
do_test notnull-3.10 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {NOT NULL constraint failed: t1.b}}
verify_ex_errcode notnull-3.10b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-3.11 {
  catchsql {
    DELETE FROM t1;
    INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5);
    SELECT * FROM t1 order by a;
  }

} {0 {1 2 6 4 5}}
do_test notnull-3.16 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5);
    SELECT * FROM t1 order by a;
  }
} {1 {NOT NULL constraint failed: t1.c}}
verify_ex_errcode notnull-3.16b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-3.17 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5);
    SELECT * FROM t1 order by a;
  }
} {1 {NOT NULL constraint failed: t1.d}}
verify_ex_errcode notnull-3.17b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-3.18 {
  catchsql {
    DELETE FROM t1;
    INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5);
    SELECT * FROM t1 order by a;
  }

} {0 {1 2 3 4 8}}
do_test notnull-3.20 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null);
    SELECT * FROM t1 order by a;
  }
} {1 {NOT NULL constraint failed: t1.e}}
verify_ex_errcode notnull-3.20b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-3.21 {
  catchsql {
    DELETE FROM t1;
    INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5);
    SELECT * FROM t1 order by a;
  }
} {0 {5 5 3 2 1}}

do_test notnull-4.1 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {NOT NULL constraint failed: t1.a}}
verify_ex_errcode notnull-4.1b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-4.2 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR REPLACE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {NOT NULL constraint failed: t1.a}}
verify_ex_errcode notnull-4.2b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-4.3 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR IGNORE t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {0 {1 2 3 4 5}}
do_test notnull-4.4 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR ABORT t1 SET a=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {NOT NULL constraint failed: t1.a}}
verify_ex_errcode notnull-4.4b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-4.5 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET b=null;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {NOT NULL constraint failed: t1.b}}
verify_ex_errcode notnull-4.5b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-4.6 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE OR REPLACE t1 SET b=null, d=e, e=d;
    SELECT * FROM t1 ORDER BY a;

do_test notnull-4.10 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3,4,5);
    UPDATE t1 SET e=null, a=b, b=a;
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {NOT NULL constraint failed: t1.e}}
verify_ex_errcode notnull-4.10b SQLITE_CONSTRAINT_NOTNULL

# Test that bug 29ab7be99f is fixed.
#
do_test notnull-5.1 {
  execsql {
    DROP TABLE IF EXISTS t1;
    CREATE TABLE t1(a, b NOT NULL);
    CREATE TABLE t2(c, d);
    INSERT INTO t2 VALUES(3, 4);
    INSERT INTO t2 VALUES(5, NULL);
  }
}  {}
do_test notnull-5.2 {
  catchsql {
    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t1 SELECT * FROM t2;
  }
} {1 {NOT NULL constraint failed: t1.b}}
verify_ex_errcode notnull-5.2b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-5.3 {
  execsql { SELECT * FROM t1 }
} {1 2}
do_test notnull-5.4 {
  catchsql {
    DELETE FROM t1;
    BEGIN;
      INSERT INTO t1 VALUES(1, 2);
      INSERT INTO t1 SELECT * FROM t2;
    COMMIT;
  }
} {1 {NOT NULL constraint failed: t1.b}}
verify_ex_errcode notnull-5.4b SQLITE_CONSTRAINT_NOTNULL
do_test notnull-5.5 {
  execsql { SELECT * FROM t1 }
} {1 2}

finish_test

    INSERT INTO t1 VALUES(1, randomblob(1500));
    INSERT INTO t1 VALUES(2, randomblob(1500));
    INSERT INTO t1 VALUES(3, randomblob(1500));
    SELECT * FROM counter;
} {3 0}
do_catchsql_test pager1-3.1.3 {
    INSERT INTO t1 SELECT a+3, randomblob(1500) FROM t1
} {1 {CHECK constraint failed: counter}}
do_execsql_test pager1-3.4 { SELECT * FROM counter } {3 0}
do_execsql_test pager1-3.5 { SELECT a FROM t1 } {1 2 3}
do_execsql_test pager1-3.6 { COMMIT } {}

foreach {tn sql tcl} {
  7  { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 0 } {
    testvfs tv -default 1

do_execsql_test pager1-14.1.3 {
  SELECT * FROM t1;
} {1 2}
do_catchsql_test pager1-14.1.4 {
  BEGIN;
    INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1;
    INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1;
} {1 {UNIQUE constraint failed: t1.rowid}}
do_execsql_test pager1-14.1.5 {
  COMMIT;
  SELECT * FROM t1;
} {1 2 2 2}

#-------------------------------------------------------------------------
# Test opening and closing the pager sub-system with different values

  # This causes a ROLLBACK, which deletes the table out from underneath the
  # SELECT statement.
  #
  do_test rollback-1.4 {
    catchsql {
      INSERT INTO t3 SELECT a FROM t1;
    }
  } {1 {UNIQUE constraint failed: t3.a}}
  
  # Try to continue with the SELECT statement
  #
  do_test rollback-1.5 {
    sqlite3_step $STMT
  } {SQLITE_ERROR}

    BEGIN;
      INSERT INTO t4 VALUES(2, 'two');
      SAVEPOINT sp1;
        INSERT INTO t4 VALUES(3, 'three');
        SAVEPOINT sp2;
          INSERT OR ROLLBACK INTO t4 VALUES(1, 'one');
  }
} {1 {UNIQUE constraint failed: t4.a}}
do_test savepoint-12.3 {
  sqlite3_get_autocommit db
} {1}
do_test savepoint-12.4 {
  execsql { SAVEPOINT one }
} {}
wal_check_journal_mode savepoint-12.5

    CREATE TABLE t1(a,b,c, PRIMARY KEY(a) UNIQUE (a) CONSTRAINT one);
    INSERT INTO t1 VALUES(1,2,3);
    SELECT * FROM t1;
  }
} {1 2 3}
do_test schema5-1.2 {
  catchsql {INSERT INTO t1 VALUES(1,3,4);}
} {1 {UNIQUE constraint failed: t1.a}}
do_test schema5-1.3 {
  db eval {
    DROP TABLE t1;
    CREATE TABLE t1(a,b,c,
        CONSTRAINT one PRIMARY KEY(a) CONSTRAINT two CHECK(b<10) UNIQUE(b)
        CONSTRAINT three
    );
    INSERT INTO t1 VALUES(1,2,3);
    SELECT * FROM t1;
  }
} {1 2 3}
do_test schema5-1.4 {
  catchsql {INSERT INTO t1 VALUES(10,11,12);}
} {1 {CHECK constraint failed: two}}
do_test schema5-1.5 {
  db eval {
    DROP TABLE t1;
    CREATE TABLE t1(a,b,c,
       UNIQUE(a) CONSTRAINT one,
       PRIMARY KEY(b,c) CONSTRAINT two
    );
    INSERT INTO t1 VALUES(1,2,3);
  }
} {}
do_test schema5-1.6 {
  catchsql {INSERT INTO t1 VALUES(1,3,4)}
} {1 {UNIQUE constraint failed: t1.a}}
do_test schema5-1.7 {
  catchsql {INSERT INTO t1 VALUES(10,2,3)}
} {1 {UNIQUE constraint failed: t1.b, t1.c}}



    

finish_test

#
ifcapable {foreignkey} {
do_test table-10.1 {
  catchsql {
    CREATE TABLE t6(a REFERENCES t4(a) NOT NULL);
    INSERT INTO t6 VALUES(NULL);
  }
} {1 {NOT NULL constraint failed: t6.a}}
do_test table-10.2 {
  catchsql {
    DROP TABLE t6;
    CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL);
  }
} {0 {}}
do_test table-10.3 {

    INSERT INTO t1 VALUES(2, 2);
  }
  catchsql {
    BEGIN;
      INSERT INTO t1 VALUES(1, 2);
    COMMIT;
  }
} {1 {UNIQUE constraint failed: t1.b}}
do_test tkt-4a03ed-1.2 {
  db eval {
    PRAGMA integrity_check;
  }
} {ok}
do_test tkt-4a03ed-1.3 {
  db eval {

    SELECT * FROM "", t2;
  }
} {1 {} 2}
do_test tkt-78e04-1.1 {
  catchsql {
    INSERT INTO ""("") VALUES(1);
  }
} {1 {UNIQUE constraint failed: .}}
do_test tkt-78e04-1.2 {
  execsql {
    PRAGMA table_info("");
  }
} {0 {} {} 0 {} 0 1 x CHAR(100) 0 {} 0}
do_test tkt-78e04-1.3 {
  execsql {

}
do_catchsql_test 3.2 {
  BEGIN;
    DELETE FROM pp2;
    DROP TABLE pp1;
    DROP TABLE cc1;
  COMMIT;
} {1 {FOREIGN KEY constraint failed}}
do_catchsql_test 3.3 {
    DROP TABLE cc2;
  COMMIT;
} {0 {}}

    UPDATE t1 SET a = a||'x' WHERE rowid%2==0;
  }
} {}
do_test tkt1567-1.4 {
  catchsql {
    UPDATE t1 SET a = CASE WHEN rowid<90 THEN substr(a,1,10) ELSE '9999' END;
  }
} {1 {UNIQUE constraint failed: t1.a}}
do_test tkt1567-1.5 {
  execsql {
    COMMIT;
  }
} {}
integrity_check tkt1567-1.6

    UPDATE t2 SET a = a||'x' WHERE rowid%2==0;
  }
} {}
do_test tkt1567-2.4 {
  catchsql {
    UPDATE t2 SET a = CASE WHEN rowid<90 THEN substr(a,1,10) ELSE '9999' END;
  }
} {1 {UNIQUE constraint failed: t2.a}}
do_test tkt1567-2.5 {
  execsql {
    COMMIT;
  }
} {}
integrity_check tkt1567-2.6

finish_test

do_test tkt35xx-1.2.2 {
  catchsql {
    BEGIN;
    CREATE TABLE t5(e PRIMARY KEY, f);
    DROP TABLE t5;
    INSERT INTO t3(a, b) SELECT c, d FROM t4;
  }
} {1 {UNIQUE constraint failed: t3.a}}
do_test tkt35xx-1.2.3 {
  # Show that the transaction has not been rolled back.
  catchsql BEGIN
} {1 {cannot start a transaction within a transaction}}
do_test tkt35xx-1.2.4 {
  execsql { SELECT count(*) FROM t3 }
} {4}

      SELECT * from tbl;
    }
  } {1 2 3}
  do_test trigger2-6.1b {
    catchsql {
      INSERT OR ABORT INTO tbl values (2, 2, 3);
    }
  } {1 {UNIQUE constraint failed: tbl.a}}
  do_test trigger2-6.1c {
    execsql {
      SELECT * from tbl;
    }
  } {1 2 3}
  do_test trigger2-6.1d {
    catchsql {
      INSERT OR FAIL INTO tbl values (2, 2, 3);
    }
  } {1 {UNIQUE constraint failed: tbl.a}}
  do_test trigger2-6.1e {
    execsql {
      SELECT * from tbl;
    }
  } {1 2 3 2 2 3}
  do_test trigger2-6.1f {
    execsql {
      INSERT OR REPLACE INTO tbl values (2, 2, 3);
      SELECT * from tbl;
    }
  } {1 2 3 2 0 0}
  do_test trigger2-6.1g {
    catchsql {
      INSERT OR ROLLBACK INTO tbl values (3, 2, 3);
    }
  } {1 {UNIQUE constraint failed: tbl.a}}
  do_test trigger2-6.1h {
    execsql {
      SELECT * from tbl;
    }
  } {}
  execsql {DELETE FROM tbl}
  

      SELECT * from tbl;
    }
  } {1 2 10 6 3 4}
  do_test trigger2-6.2b {
    catchsql {
      UPDATE OR ABORT tbl SET a = 4 WHERE a = 1;
    }
  } {1 {UNIQUE constraint failed: tbl.a}}
  do_test trigger2-6.2c {
    execsql {
      SELECT * from tbl;
    }
  } {1 2 10 6 3 4}
  do_test trigger2-6.2d {
    catchsql {
      UPDATE OR FAIL tbl SET a = 4 WHERE a = 1;
    }
  } {1 {UNIQUE constraint failed: tbl.a}}
  do_test trigger2-6.2e {
    execsql {
      SELECT * from tbl;
    }
  } {4 2 10 6 3 4}
  do_test trigger2-6.2f.1 {
    execsql {

      SELECT * FROM tbl;
    }
  } {1 3 10 2 3 4}
  do_test trigger2-6.2g {
    catchsql {
      UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
    }
  } {1 {UNIQUE constraint failed: tbl.a}}
  do_test trigger2-6.2h {
    execsql {
      SELECT * from tbl;
    }
  } {4 2 3 6 3 4}
  execsql {
    DROP TABLE tbl;

    INSERT INTO t1 VALUES(1,2,3,4,5);
    INSERT INTO t1 VALUES(6,7,8,9,10);
    INSERT INTO t1 VALUES(11,12,13,14,15);
  }
} {}
do_test triggerC-1.15 {
  catchsql { UPDATE OR ROLLBACK t1 SET a=100 }
} {1 {UNIQUE constraint failed: t1.a}}


#-------------------------------------------------------------------------
# This block of tests, triggerC-2.*, tests that recursive trigger
# programs (triggers that fire themselves) work. More specifically,
# this block focuses on recursive INSERT triggers.
#

    INSERT INTO t1(a,b,c) VALUES(1,2,3)
  }
} {0 {}}
do_test unique-1.3 {
  catchsql {
    INSERT INTO t1(a,b,c) VALUES(1,3,4)
  }
} {1 {UNIQUE constraint failed: t1.a}}
verify_ex_errcode unique-1.3b SQLITE_CONSTRAINT_PRIMARYKEY
do_test unique-1.4 {
  execsql {
    SELECT * FROM t1 ORDER BY a;
  }
} {1 2 3}
do_test unique-1.5 {
  catchsql {
    INSERT INTO t1(a,b,c) VALUES(3,2,4)
  }
} {1 {UNIQUE constraint failed: t1.b}}
verify_ex_errcode unique-1.5b SQLITE_CONSTRAINT_UNIQUE
do_test unique-1.6 {
  execsql {
    SELECT * FROM t1 ORDER BY a;
  }
} {1 2 3}
do_test unique-1.7 {

    SELECT * FROM t2 ORDER BY a
  }
} {0 {1 2 3 4}}
do_test unique-2.3 {
  catchsql {
    INSERT INTO t2 VALUES(1,5);
  }
} {1 {UNIQUE constraint failed: t2.a}}
verify_ex_errcode unique-2.3b SQLITE_CONSTRAINT_UNIQUE
do_test unique-2.4 {
  catchsql {
    SELECT * FROM t2 ORDER BY a
  }
} {0 {1 2 3 4}}
do_test unique-2.5 {

    SELECT * FROM t2 ORDER BY a, b;
  }
} {0 {1 2 1 5 3 4}}
do_test unique-2.8 {
  catchsql {
    CREATE UNIQUE INDEX i2 ON t2(a);
  }
} {1 {UNIQUE constraint failed: t2.a}}
verify_ex_errcode unique-2.8b SQLITE_CONSTRAINT_UNIQUE
do_test unique-2.9 {
  catchsql {
    CREATE INDEX i2 ON t2(a);
  }
} {0 {}}
integrity_check unique-2.10

  }
} {0 {1 2 3 4 1 2 3 5}}
do_test unique-3.4 {
  catchsql {
    INSERT INTO t3(a,b,c,d) VALUES(1,4,3,5);
    SELECT * FROM t3 ORDER BY a,b,c,d;
  }
} {1 {UNIQUE constraint failed: t3.a, t3.c, t3.d}}
verify_ex_errcode unique-3.4b SQLITE_CONSTRAINT_UNIQUE
integrity_check unique-3.5

# Make sure NULLs are distinct as far as the UNIQUE tests are
# concerned.
#
do_test unique-4.1 {

  catchsql {CREATE UNIQUE INDEX i4a ON t4(a,b)}
} {0 {}}
do_test unique-4.9 {
  catchsql {CREATE UNIQUE INDEX i4b ON t4(a,b,c)}
} {0 {}}
do_test unique-4.10 {
  catchsql {CREATE UNIQUE INDEX i4c ON t4(b)}
} {1 {UNIQUE constraint failed: t4.b}}
verify_ex_errcode unique-4.10b SQLITE_CONSTRAINT_UNIQUE
integrity_check unique-4.99

# Test the error message generation logic.  In particular, make sure we
# do not overflow the static buffer used to generate the error message.
#
do_test unique-5.1 {

    SELECT * FROM t5;
  }
} {1 2 3 4 5 6}
do_test unique-5.2 {
  catchsql {
    INSERT INTO t5 VALUES(1,2,3,4,5,6);
  }
} {1 {UNIQUE constraint failed: t5.first_column_with_long_name, t5.second_column_with_long_name, t5.third_column_with_long_name, t5.fourth_column_with_long_name, t5.fifth_column_with_long_name, t5.sixth_column_with_long_name}}
verify_ex_errcode unique-5.2b SQLITE_CONSTRAINT_UNIQUE


finish_test

  }
} {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
do_test update-10.3 {
  catchsql {
    UPDATE t1 SET a=1, e=10 WHERE f=7;
    SELECT * FROM t1;
  }
} {1 {UNIQUE constraint failed: t1.a}}
do_test update-10.4 {
  catchsql {
    SELECT * FROM t1;
  }
} {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
do_test update-10.5 {
  catchsql {
    UPDATE t1 SET b=2, e=11 WHERE f=6;
    SELECT * FROM t1;
  }
} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
do_test update-10.6 {
  catchsql {
    UPDATE t1 SET b=2, e=12 WHERE f=7;
    SELECT * FROM t1;
  }
} {1 {UNIQUE constraint failed: t1.b}}
do_test update-10.7 {
  catchsql {
    SELECT * FROM t1;
  }
} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
do_test update-10.8 {
  catchsql {
    UPDATE t1 SET c=3, d=4, e=13 WHERE f=6;
    SELECT * FROM t1;
  }
} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
do_test update-10.9 {
  catchsql {
    UPDATE t1 SET c=3, d=4, e=14 WHERE f=7;
    SELECT * FROM t1;
  }
} {1 {UNIQUE constraint failed: t1.c, t1.d}}
do_test update-10.10 {
  catchsql {
    SELECT * FROM t1;
  }
} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}

# Make sure we can handle a subquery in the where clause.

    CREATE VIRTUAL TABLE echo_c USING echo(c);
  }
} {}

# First test outside of a transaction.
do_test vtab1.12-2 {
  catchsql { INSERT INTO echo_c SELECT * FROM b; }
} {1 {echo-vtab-error: UNIQUE constraint failed: c.a}}
do_test vtab1.12-2.1 {
  sqlite3_errmsg db
} {echo-vtab-error: UNIQUE constraint failed: c.a}
do_test vtab1.12-3 {
  execsql { SELECT * FROM c }
} {3 G H}

# Now the real test - wrapped in a transaction.
do_test vtab1.12-4 {
  execsql  {BEGIN}
  catchsql { INSERT INTO echo_c SELECT * FROM b; }
} {1 {echo-vtab-error: UNIQUE constraint failed: c.a}}
do_test vtab1.12-5 {
  execsql { SELECT * FROM c }
} {3 G H}
do_test vtab1.12-6 {
  execsql { COMMIT }
  execsql { SELECT * FROM c }
} {3 G H}

  execsql {
    CREATE TEMP TABLE t3(x UNIQUE);
    BEGIN;
      INSERT INTO t2 VALUES(3, 4);
      INSERT INTO t3 VALUES('abc');
  }
  catchsql { INSERT INTO t3 VALUES('abc') }
} {1 {UNIQUE constraint failed: t3.x}}
do_test wal-5.5 {
  execsql {
    COMMIT;
    SELECT * FROM t2;
  }
} {1 2 3 4}
db close

# Trying to insert a duplicate PRIMARY KEY fails.
#
do_test without_rowid1-1.21 {
  catchsql {
    INSERT INTO t1 VALUES('dynamic','phone','flipper','harvard');
  }
} {1 {UNIQUE constraint failed: t1.c, t1.a}}

# REPLACE INTO works, however.
#
do_execsql_test without_rowid1-1.22 {
  REPLACE INTO t1 VALUES('dynamic','phone','flipper','harvard');
  SELECT *, '|' FROM t1 ORDER BY c, a;
} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic phone flipper harvard | journal sherman gamma patriot |}

  CREATE TABLE t9(a REFERENCES nosuchtable, b);
  CREATE TABLE t10(a REFERENCES t9(c) /D/, b);
}


set FkeySimpleTests {
  1.1  "INSERT INTO t2 VALUES(1, 3)"      {1 {FOREIGN KEY constraint failed}}
  1.2  "INSERT INTO t1 VALUES(1, 2)"      {0 {}}
  1.3  "INSERT INTO t2 VALUES(1, 3)"      {0 {}}
  1.4  "INSERT INTO t2 VALUES(2, 4)"      {1 {FOREIGN KEY constraint failed}}
  1.5  "INSERT INTO t2 VALUES(NULL, 4)"   {0 {}}
  1.6  "UPDATE t2 SET c=2 WHERE d=4"      {1 {FOREIGN KEY constraint failed}}
  1.7  "UPDATE t2 SET c=1 WHERE d=4"      {0 {}}
  1.9  "UPDATE t2 SET c=1 WHERE d=4"      {0 {}}
  1.10 "UPDATE t2 SET c=NULL WHERE d=4"   {0 {}}
  1.11 "DELETE FROM t1 WHERE a=1"         {1 {FOREIGN KEY constraint failed}}
  1.12 "UPDATE t1 SET a = 2"              {1 {FOREIGN KEY constraint failed}}
  1.13 "UPDATE t1 SET a = 1"              {0 {}}

  2.1  "INSERT INTO t4 VALUES(1, 3)"      {1 {FOREIGN KEY constraint failed}}
  2.2  "INSERT INTO t3 VALUES(1, 2)"      {0 {}}
  2.3  "INSERT INTO t4 VALUES(1, 3)"      {0 {}}

  4.1  "INSERT INTO t8 VALUES(1, 3)"      {1 {FOREIGN KEY constraint failed}}
  4.2  "INSERT INTO t7 VALUES(2, 1)"      {0 {}}
  4.3  "INSERT INTO t8 VALUES(1, 3)"      {0 {}}
  4.4  "INSERT INTO t8 VALUES(2, 4)"      {1 {FOREIGN KEY constraint failed}}
  4.5  "INSERT INTO t8 VALUES(NULL, 4)"   {0 {}}
  4.6  "UPDATE t8 SET c=2 WHERE d=4"      {1 {FOREIGN KEY constraint failed}}
  4.7  "UPDATE t8 SET c=1 WHERE d=4"      {0 {}}
  4.9  "UPDATE t8 SET c=1 WHERE d=4"      {0 {}}
  4.10 "UPDATE t8 SET c=NULL WHERE d=4"   {0 {}}
  4.11 "DELETE FROM t7 WHERE b=1"         {1 {FOREIGN KEY constraint failed}}
  4.12 "UPDATE t7 SET b = 2"              {1 {FOREIGN KEY constraint failed}}
  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.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"}}
}

    INSERT INTO i VALUES(35);
    INSERT INTO j VALUES('35.0');
    SELECT j, typeof(j) FROM j;
  }
} {35.0 text}
do_test without_rowid3-1.5.2 {
  catchsql { DELETE FROM i }
} {1 {FOREIGN KEY constraint failed}}

# Same test using a regular primary key with integer affinity.
drop_all_tables
do_test without_rowid3-1.6.1 {
  execsql {
    CREATE TABLE i(i INT UNIQUE);
    CREATE TABLE j(j REFERENCES i(i));
    INSERT INTO i VALUES('35.0');
    INSERT INTO j VALUES('35.0');
    SELECT j, typeof(j) FROM j;
    SELECT i, typeof(i) FROM i;
  }
} {35.0 text 35 integer}
do_test without_rowid3-1.6.2 {
  catchsql { DELETE FROM i }
} {1 {FOREIGN KEY constraint failed}}

# Use a collation sequence on the parent key.
drop_all_tables
do_test without_rowid3-1.7.1 {
  execsql {
    CREATE TABLE i(i TEXT COLLATE nocase PRIMARY KEY) WITHOUT rowid;
    CREATE TABLE j(j TEXT COLLATE binary REFERENCES i(i));
    INSERT INTO i VALUES('SQLite');
    INSERT INTO j VALUES('sqlite');
  }
  catchsql { DELETE FROM i }
} {1 {FOREIGN KEY constraint failed}}

# Use the parent key collation even if it is default and the child key
# has an explicit value.
drop_all_tables
do_test without_rowid3-1.7.2 {
  execsql {
    CREATE TABLE i(i TEXT PRIMARY KEY) WITHOUT rowid;  -- Colseq is "BINARY"
    CREATE TABLE j(j TEXT COLLATE nocase REFERENCES i(i));
    INSERT INTO i VALUES('SQLite');
  }
  catchsql { INSERT INTO j VALUES('sqlite') }
} {1 {FOREIGN KEY constraint failed}}
do_test without_rowid3-1.7.3 {
  execsql {
    INSERT INTO i VALUES('sqlite');
    INSERT INTO j VALUES('sqlite');
    DELETE FROM i WHERE i = 'SQLite';
  }
  catchsql { DELETE FROM i WHERE i = 'sqlite' }
} {1 {FOREIGN KEY constraint failed}}

#-------------------------------------------------------------------------
# This section (test cases without_rowid3-2.*) contains tests to check that the
# deferred foreign key constraint logic works.
#
proc without_rowid3-2-test {tn nocommit sql {res {}}} {
  if {$res eq "FKV"} {
    set expected {1 {FOREIGN KEY constraint failed}}
  } else {
    set expected [list 0 $res]
  }
  do_test without_rowid3-2.$tn [list catchsql $sql] $expected
  if {$nocommit} {
    do_test without_rowid3-2.${tn}c {
      catchsql COMMIT
    } {1 {FOREIGN KEY constraint failed}}
  }
}

without_rowid3-2-test 1 0 {
  CREATE TABLE node(
    nodeid PRIMARY KEY,
    parent REFERENCES node DEFERRABLE INITIALLY DEFERRED

without_rowid3-2-test 62 0   "DELETE FROM leaf"
without_rowid3-2-test 63 0   "DELETE FROM node"
without_rowid3-2-test 64 1   "INSERT INTO leaf VALUES('a', 1)"
without_rowid3-2-test 65 1   "INSERT INTO leaf VALUES('b', 2)"
without_rowid3-2-test 66 1   "INSERT INTO leaf VALUES('c', 1)"
do_test without_rowid3-2-test-67 {
  catchsql          "INSERT INTO node SELECT parent, 3 FROM leaf"
} {1 {UNIQUE constraint failed: node.nodeid}}
without_rowid3-2-test 68 0 "COMMIT"           FKV
without_rowid3-2-test 69 1   "INSERT INTO node VALUES(1, NULL)"
without_rowid3-2-test 70 0   "INSERT INTO node VALUES(2, NULL)"
without_rowid3-2-test 71 0 "COMMIT"

without_rowid3-2-test 72 0 "BEGIN"
without_rowid3-2-test 73 1   "DELETE FROM node"

    INSERT INTO ab VALUES(1, 'b');
    INSERT INTO cd VALUES(1, 'd');
    INSERT INTO ef VALUES(1, 'e');
  }
} {}
do_test without_rowid3-3.1.3 {
  catchsql { UPDATE ab SET a = 5 }
} {1 {CHECK constraint failed: ef}}
do_test without_rowid3-3.1.4 {
  execsql { SELECT * FROM ab }
} {1 b}
do_test without_rowid3-3.1.4 {
  execsql BEGIN;
  catchsql { UPDATE ab SET a = 5 }
} {1 {CHECK constraint failed: ef}}
do_test without_rowid3-3.1.5 {
  execsql COMMIT;
  execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
} {1 b 1 d 1 e}

do_test without_rowid3-3.2.1 {
  execsql BEGIN;
  catchsql { DELETE FROM ab }
} {1 {FOREIGN KEY constraint failed}}
do_test without_rowid3-3.2.2 {
  execsql COMMIT
  execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
} {1 b 1 d 1 e}

#-------------------------------------------------------------------------
# Test cases without_rowid3-4.* test that recursive foreign key actions 

  execsql {
    CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT rowid;
    CREATE TABLE t2(c INT PRIMARY KEY REFERENCES t1, b) WITHOUT rowid;
  }
} {}
do_test without_rowid3-7.2 {
  catchsql { INSERT INTO t2 VALUES(1, 'A'); }
} {1 {FOREIGN KEY constraint failed}}
do_test without_rowid3-7.3 {
  execsql { 
    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t1 VALUES(2, 3);
    INSERT INTO t2 VALUES(1, 'A');
  }
} {}
do_test without_rowid3-7.4 {
  execsql { UPDATE t2 SET c = 2 }
} {}
do_test without_rowid3-7.5 {
  catchsql { UPDATE t2 SET c = 3 }
} {1 {FOREIGN KEY constraint failed}}
do_test without_rowid3-7.6 {
  catchsql { DELETE FROM t1 WHERE a = 2 }
} {1 {FOREIGN KEY constraint failed}}
do_test without_rowid3-7.7 {
  execsql { DELETE FROM t1 WHERE a = 1 }
} {}
do_test without_rowid3-7.8 {
  catchsql { UPDATE t1 SET a = 3 }
} {1 {FOREIGN KEY constraint failed}}

#-------------------------------------------------------------------------
# Test that it is not possible to enable/disable FK support while a
# transaction is open.
# 
drop_all_tables
proc without_rowid3-8-test {tn zSql value} {

  }
} {1 2}
do_test without_rowid3-9.1.4 {
  execsql { SELECT * FROM t1 }
} {2 two}
do_test without_rowid3-9.1.5 {
  catchsql { DELETE FROM t1 }
} {1 {FOREIGN KEY constraint failed}}

do_test without_rowid3-9.2.1 {
  execsql {
    CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c)) WITHOUT rowid;
    CREATE TABLE cc(d DEFAULT 3, e DEFAULT 1, f DEFAULT 2,
        FOREIGN KEY(f, d) REFERENCES pp 
        ON UPDATE SET DEFAULT 

  execsql "INSERT INTO t2 VALUES('two')"
} {}
do_test without_rowid3-12.1.3 { 
  execsql "UPDATE t1 SET b = 'four' WHERE b = 'one'"
} {}
do_test without_rowid3-12.1.4 { 
  catchsql "UPDATE t1 SET b = 'five' WHERE b = 'two'"
} {1 {FOREIGN KEY constraint failed}}
do_test without_rowid3-12.1.5 { 
  execsql "DELETE FROM t1 WHERE b = 'two'"
} {}
do_test without_rowid3-12.1.6 { 
  catchsql "COMMIT"
} {1 {FOREIGN KEY constraint failed}}
do_test without_rowid3-12.1.7 { 
  execsql {
    INSERT INTO t1 VALUES(2, 'two');
    COMMIT;
  }
} {}

  execsql {
    DROP TABLE t2;
    CREATE TABLE t2(y REFERENCES t1 ON DELETE RESTRICT);
    INSERT INTO t2 VALUES('a');
    INSERT INTO t2 VALUES('b');
  }
  catchsql { DELETE FROM t1 }
} {1 {FOREIGN KEY constraint failed}}
do_test without_rowid3-12.2.4 {
  execsql {
    SELECT * FROM t1;
    SELECT * FROM t2;
  }
} {A B a b}

    UPDATE up SET c34 = 'possibly';
    SELECT c38, c39 FROM down;
    DELETE FROM down;
  }
} {no possibly}
do_test without_rowid3-12.3.3 {
  catchsql { INSERT INTO down(c39, c38) VALUES('yes', 'no') }
} {1 {FOREIGN KEY constraint failed}}
do_test without_rowid3-12.3.4 {
  execsql { 
    INSERT INTO up(c34, c35) VALUES('yes', 'no');
    INSERT INTO down(c39, c38) VALUES('yes', 'no');
  }
  catchsql { DELETE FROM up WHERE c34 = 'yes' }
} {1 {FOREIGN KEY constraint failed}}
do_test without_rowid3-12.3.5 {
  execsql { 
    DELETE FROM up WHERE c34 = 'possibly';
    SELECT c34, c35 FROM up;
    SELECT c39, c38 FROM down;
  }
} {yes no yes no}

  }
} {}
foreach {tn stmt} {
  1   "REPLACE INTO pp VALUES(1, 4, 5)"
} {
  do_test without_rowid3-13.1.$tn.1 {
    catchsql $stmt
  } {1 {FOREIGN KEY constraint failed}}
  do_test without_rowid3-13.1.$tn.2 {
    execsql {
      SELECT * FROM pp;
      SELECT * FROM cc;
    }
  } {1 2 3 2 3 1}
  do_test without_rowid3-13.1.$tn.3 {
    execsql BEGIN;
    catchsql $stmt
  } {1 {FOREIGN KEY constraint failed}}
  do_test without_rowid3-13.1.$tn.4 {
    execsql {
      COMMIT;
      SELECT * FROM pp;
      SELECT * FROM cc;
    }
  } {1 2 3 2 3 1}

    {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4") WITHOUT rowid}      \
    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)
            WITHOUT rowid}     \
    {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
  ]
  do_test without_rowid3-14.2.2.3 {
    catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
  } {1 {FOREIGN KEY constraint failed}}
  do_test without_rowid3-14.2.2.4 {
    execsql { INSERT INTO t4 VALUES(1, NULL) }
  } {}
  do_test without_rowid3-14.2.2.5 {
    catchsql { UPDATE t4 SET b = 5 }
  } {1 {FOREIGN KEY constraint failed}}
  do_test without_rowid3-14.2.2.6 {
    catchsql { UPDATE t4 SET b = 1 }
  } {0 {}}
  do_test without_rowid3-14.2.2.7 {
    execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
  } {}

    {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4") WITHOUT rowid}      \
    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)
            WITHOUT rowid}     \
    {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
  ]
  do_test without_rowid3-14.2tmp.2.3 {
    catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
  } {1 {FOREIGN KEY constraint failed}}
  do_test without_rowid3-14.2tmp.2.4 {
    execsql { INSERT INTO t4 VALUES(1, NULL) }
  } {}
  do_test without_rowid3-14.2tmp.2.5 {
    catchsql { UPDATE t4 SET b = 5 }
  } {1 {FOREIGN KEY constraint failed}}
  do_test without_rowid3-14.2tmp.2.6 {
    catchsql { UPDATE t4 SET b = 1 }
  } {0 {}}
  do_test without_rowid3-14.2tmp.2.7 {
    execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
  } {}

    {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4") WITHOUT rowid}      \
    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)
            WITHOUT rowid}     \
    {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
  ]
  do_test without_rowid3-14.2aux.2.3 {
    catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
  } {1 {FOREIGN KEY constraint failed}}
  do_test without_rowid3-14.2aux.2.4 {
    execsql { INSERT INTO t4 VALUES(1, NULL) }
  } {}
  do_test without_rowid3-14.2aux.2.5 {
    catchsql { UPDATE t4 SET b = 5 }
  } {1 {FOREIGN KEY constraint failed}}
  do_test without_rowid3-14.2aux.2.6 {
    catchsql { UPDATE t4 SET b = 1 }
  } {0 {}}
  do_test without_rowid3-14.2aux.2.7 {
    execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
  } {}
}

    INSERT INTO t1 VALUES('a', 1);
    CREATE TABLE t2(x REFERENCES t1);
    INSERT INTO t2 VALUES('a');
  }
} {}
do_test without_rowid3-2.14.3.3 {
  catchsql { DROP TABLE t1 }
} {1 {FOREIGN KEY constraint failed}}
do_test without_rowid3-2.14.3.4 {
  execsql {
    DELETE FROM t2;
    DROP TABLE t1;
  }
} {}
do_test without_rowid3-2.14.3.4 {
  catchsql { INSERT INTO t2 VALUES('x') }
} {1 {no such table: main.t1}}
do_test without_rowid3-2.14.3.5 {
  execsql {
    CREATE TABLE t1(x PRIMARY KEY) WITHOUT rowid;
    INSERT INTO t1 VALUES('x');
  }
  execsql { INSERT INTO t2 VALUES('x') }
} {}
do_test without_rowid3-2.14.3.6 {
  catchsql { DROP TABLE t1 }
} {1 {FOREIGN KEY constraint failed}}
do_test without_rowid3-2.14.3.7 {
  execsql {
    DROP TABLE t2;
    DROP TABLE t1;
  }
} {}
do_test without_rowid3-2.14.3.8 {

  } {}
  do_test without_rowid3-16.1.$tn.2 {
    execsql { UPDATE self SET a = 14, b = 14 }
  } {}

  do_test without_rowid3-16.1.$tn.3 {
    catchsql { UPDATE self SET b = 15 }
  } {1 {FOREIGN KEY constraint failed}}

  do_test without_rowid3-16.1.$tn.4 {
    catchsql { UPDATE self SET a = 15 }
  } {1 {FOREIGN KEY constraint failed}}

  do_test without_rowid3-16.1.$tn.5 {
    catchsql { UPDATE self SET a = 15, b = 16 }
  } {1 {FOREIGN KEY constraint failed}}

  do_test without_rowid3-16.1.$tn.6 {
    catchsql { UPDATE self SET a = 17, b = 17 }
  } {0 {}}

  do_test without_rowid3-16.1.$tn.7 {
    execsql { DELETE FROM self }
  } {}
  do_test without_rowid3-16.1.$tn.8 {
    catchsql { INSERT INTO self VALUES(20, 21) }
  } {1 {FOREIGN KEY constraint failed}}
}

# Additional tests cases using multi-column self-referential
# FOREIGN KEY constraints.
#
drop_all_tables
do_execsql_test without_rowid3-16.4.1.1 {

  SELECT *, '|' FROM t1 ORDER BY a, b;
} {1 2 99 5 5 99 | 2 3 4 876 876 4 | x y 1.5 fizzle fizzle 1.5 |}

do_test without_rowid3-16.4.1.4 {
  catchsql {
    UPDATE t1 SET c=11, e=22 WHERE a=1;
  }
} {1 {FOREIGN KEY constraint failed}}

do_test without_rowid3-16.4.1.5 {
  catchsql {
    UPDATE t1 SET d=11, f=22 WHERE a=1;
  }
} {1 {FOREIGN KEY constraint failed}}

do_execsql_test without_rowid3-16.4.1.6 {
  DELETE FROM t1 WHERE a=1;
  SELECT *, '|' FROM t1 ORDER BY a, b;
} {2 3 4 876 876 4 | x y 1.5 fizzle fizzle 1.5 |}

do_execsql_test without_rowid3-16.4.2.1 {

  SELECT *, '|' FROM t1 ORDER BY a, b;
} {1 2 99 5 5 99 | 2 3 4 876 876 4 | x y 1.5 fizzle fizzle 1.5 |}

do_test without_rowid3-16.4.2.4 {
  catchsql {
    UPDATE t1 SET c=11, e=22 WHERE a=1;
  }
} {1 {FOREIGN KEY constraint failed}}

do_test without_rowid3-16.4.2.5 {
  catchsql {
    UPDATE t1 SET d=11, f=22 WHERE a=1;
  }
} {1 {FOREIGN KEY constraint failed}}

do_execsql_test without_rowid3-16.4.2.6 {
  DELETE FROM t1 WHERE a=1;
  SELECT *, '|' FROM t1 ORDER BY a, b;
} {2 3 4 876 876 4 | x y 1.5 fizzle fizzle 1.5 |}

} {1 1 1 1 1}
do_test without_rowid3-17.1.6 {
  catchsql {
    BEGIN;
      INSERT INTO one VALUES(0, 0, 0);
      UPDATE two SET e=e+1, f=f+1;
  }
} {1 {FOREIGN KEY constraint failed}}
do_test without_rowid3-17.1.7 {
  execsql { SELECT * FROM one }
} {1 2 3 2 3 4 3 4 5 0 0 0}
do_test without_rowid3-17.1.8 {
  execsql { SELECT * FROM two }
} {1 2 3 2 3 4 3 4 5}
do_test without_rowid3-17.1.9 {

  rename auth {}
  proc auth {args} {
    if {[lindex $args 1] == "long"} {return SQLITE_IGNORE}
    return SQLITE_OK
  }
  do_test without_rowid3-18.8 {
    catchsql { INSERT INTO short VALUES(1, 3, 2) }
  } {1 {FOREIGN KEY constraint failed}}
  do_test without_rowid3-18.9 {
    execsql { INSERT INTO short VALUES(1, 3, NULL) }
  } {}
  do_test without_rowid3-18.10 {
    execsql { SELECT * FROM short }
  } {1 3 2 1 3 {}}
  do_test without_rowid3-18.11 {
    catchsql { UPDATE short SET f = 2 WHERE f IS NULL }
  } {1 {FOREIGN KEY constraint failed}}

  db auth {}
  unset authargs
}


do_test without_rowid3-19.1 {

  3 "INSERT OR ABORT"
  4 "INSERT OR ROLLBACK"
  5 "INSERT OR REPLACE"
  6 "INSERT OR FAIL"
} {
  do_test without_rowid3-20.2.$tn.1 {
    catchsql "$insert INTO cc VALUES(1, 2)"
  } {1 {FOREIGN KEY constraint failed}}
  do_test without_rowid3-20.2.$tn.2 {
    execsql { SELECT * FROM cc }
  } {}
  do_test without_rowid3-20.2.$tn.3 {
    execsql {
      BEGIN;
        INSERT INTO pp VALUES(2, 'two');
        INSERT INTO cc VALUES(1, 2);
    }
    catchsql "$insert INTO cc VALUES(3, 4)"
  } {1 {FOREIGN KEY constraint failed}}
  do_test without_rowid3-20.2.$tn.4 {
    execsql { COMMIT ; SELECT * FROM cc }
  } {1 2}
  do_test without_rowid3-20.2.$tn.5 {
    execsql { DELETE FROM cc ; DELETE FROM pp }
  } {}
}