title: SQLite Foreign Key Constraint Failed (code 787)
body: <p>I ran into the <code>Foreign Key Constraint Failed (code 787)</code> error when I tried to upgrade my database. The only change I did was try to add a 4th entry to my <code>InsertStatus</code>. I looked around and I read that using <code>ON DELETE CASCADE</code> should solve my problem so I tried placing it at all my FK references and tried again but still the same problem. </p>
  
  <p>Logcat points to my <code>onUpgrade</code> and all the <code>DROP TABLES</code> in it ( i tried removing it one at a time to see which ones were bad and apparently all of them were ). </p>
  
  <p>Am I using <code>ON DELETE CASCADE</code> wrong? Or is it something else in my code? </p>
  
  <p><strong>InsertStatus</strong></p>
  
  <pre><code>void InsertStatus(SQLiteDatabase db) {
      ContentValues cv = new ContentValues();
      cv.put(colStatusID, 0);
      cv.put(colStatClass, "Active");
      db.insert(statTable, colStatusID, cv);
      cv.put(colStatusID, 1);
      cv.put(colStatClass, "Settled");
      db.insert(statTable, colStatusID, cv);
      cv.put(colStatusID, 2);
      cv.put(colStatClass, "Terminated");
      db.insert(statTable, colStatusID, cv);
      cv.put(colStatusID, 3);
      cv.put(colStatClass, "");
      db.insert(statTable, colStatusID, cv);
  }
  </code></pre>
  
  <p><strong>DatabaseHelper</strong></p>
  
  <pre><code>db.execSQL("CREATE TABLE " + termsTable + " (" + colTermsID + " INTEGER PRIMARY KEY , " + colTermsClass + " TEXT)");
  
      db.execSQL("CREATE TABLE " + periodTable + " (" + colPeriodID + " INTEGER PRIMARY KEY , " + colPeriodClass + " TEXT)");
  
      db.execSQL("CREATE TABLE " + statTable + " (" + colStatusID + " INTEGER PRIMARY KEY , " + colStatClass + " TEXT)");
  
      db.execSQL("CREATE TABLE " + accountsTable + " (" + colID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
              colName + " TEXT, " +
              colAmount + " Integer, " +
              colPurpose + " TEXT, " +
              colTerms + " INTEGER NOT NULL, " +
              colPeriod +" INTEGER NOT NULL, " +
              colBalance +" INTEGER, "+
              colStatus + " INTEGER DEFAULT '1'," +
              colDate + " TEXT, " +
              colEditDate + " TEXT, " +
              "FOREIGN KEY (" + colTerms + ") REFERENCES " + termsTable + " (" + colTermsID + ") ON DELETE CASCADE," +
              "FOREIGN KEY (" + colPeriod + ") REFERENCES " + periodTable + " (" + colPeriodID + ") ON DELETE CASCADE," +
              "FOREIGN KEY (" + colStatus + ") REFERENCES " + statTable + " (" + colStatusID + ") ON DELETE CASCADE);");
  
      db.execSQL("CREATE TABLE " + payTable + " (" + colPayID + " INTEGER PRIMARY KEY , " +
              colGroupID + " INTEGER NOT NULL, " +
              colPayBal + " TEXT, " +
              colInterest + " TEXT, " +
              colPayDue + " TEXT, " +
              colDateDue + " TEXT, " +
              colPaid + " Integer, " +
              "FOREIGN KEY (" + colGroupID + ") REFERENCES " + accountsTable + " (" + colID + ") ON DELETE CASCADE);");
  
      db.execSQL("CREATE VIEW " + viewAccs +
              " AS SELECT " + accountsTable + "." + colID + " AS _id," +
              " " + accountsTable + "." + colName + "," +
              " " + accountsTable + "." + colAmount + "," +
              " " + accountsTable + "." + colPurpose + "," +
              " " + termsTable + "." + colTermsClass + "," +
              " " + periodTable + "." + colPeriodClass + "," +
              " " + accountsTable+ "." + colBalance + "," +
              " " + statTable + "." + colStatClass + "," +
              " " + accountsTable + "." + colDate + "," +
              " " + accountsTable + "." + colEditDate + "" +
              " FROM " + accountsTable +
              " JOIN " + termsTable + " ON " + accountsTable + "." + colTerms + " = " + termsTable + "." + colTermsID +
              " JOIN " + periodTable + " ON " + accountsTable + "." + colPeriod + " = " + periodTable + "." + colPeriodID +
              " JOIN " + statTable + " ON " + accountsTable + "." + colStatus + " = " + statTable + "." + colStatusID );
  
      db.execSQL("CREATE VIEW " + viewPmnts +
              " AS SELECT " + payTable + "." + colPayID + " AS _id," +
              " " + accountsTable + "." + colID + "," +
              " " + payTable + "." + colGroupID + "," +
              " " + payTable + "." + colPayBal + "," +
              " " + payTable + "." + colInterest + "," +
              " " + payTable + "." + colPayDue + "," +
              " " + payTable + "." + colDateDue + "," +
              " " + payTable + "." + colPaid + "" +
              " FROM " + payTable +
              " JOIN " + accountsTable + " ON " + payTable + "." + colGroupID + " = " + accountsTable + "." + colID );
  
      InsertTerms(db);
      InsertPeriods(db);
      InsertStatus(db);
  }
  </code></pre>
  
  <p><strong>onUpgrade</strong></p>
  
  <pre><code>@Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  
  
      db.execSQL("DROP TABLE IF EXISTS " + accountsTable);
      db.execSQL("DROP TABLE IF EXISTS " + termsTable);
      db.execSQL("DROP TABLE IF EXISTS " + periodTable);
      db.execSQL("DROP TABLE IF EXISTS " + statTable);
      db.execSQL("DROP TABLE IF EXISTS " + payTable);
  
      db.execSQL("DROP TRIGGER IF EXISTS acc_id_trigger");
      db.execSQL("DROP TRIGGER IF EXISTS acc_id_trigger22");
      db.execSQL("DROP TRIGGER IF EXISTS fk_accterm_termid");
      db.execSQL("DROP TRIGGER IF EXISTS fk_accperiod_periodid");
      db.execSQL("DROP TRIGGER IF EXISTS fk_accpay_payid");
      db.execSQL("DROP TRIGGER IF EXISTS fk_accstat_statid");
  
      db.execSQL("DROP VIEW IF EXISTS " + viewAccs);
      db.execSQL("DROP VIEW IF EXISTS " + viewPmnts);
  
      onCreate(db);
  }
  </code></pre>