SQLite Foreign Key Constraint Failed (code 787)

Question

I ran into the Foreign Key Constraint Failed (code 787) error when I tried to upgrade my database. The only change I did was try to add a 4th entry to my InsertStatus. I looked around and I read that using ON DELETE CASCADE should solve my problem so I tried placing it at all my FK references and tried again but still the same problem.

Logcat points to my onUpgrade and all the DROP TABLES in it ( i tried removing it one at a time to see which ones were bad and apparently all of them were ).

Am I using ON DELETE CASCADE wrong? Or is it something else in my code?

InsertStatus

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); } 

DatabaseHelper

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); } 

onUpgrade

@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); } 

Answer 1

The error here is related to creating a child entity before their parent is in existence.

The Flow should be:

1. Create Parent and get parent ID.
2. Create Child entity containing a reference to parent ID

So If you create a child entity without first having a valid parent ID you will be thrown this fatal error.