SQLite外键约束失败(代码787)

问题描述

当我尝试升级数据库时遇到了Foreign Key Constraint Failed (code 787)错误.我所做的唯一更改是尝试在我的InsertStatus中添加第4个条目.我环顾四周，并阅读到使用ON DELETE CASCADE应该可以解决我的问题，因此我尝试将其放置在所有FK引用中，然后再次尝试，但仍然是相同的问题.

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指向我的onUpgrade和其中的所有DROP TABLES(我尝试一次将其删除，以查看哪些是不好的，而且显然所有都是).

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 ).

我使用ON DELETE CASCADE是错误的吗?还是我的代码中还有其他内容?

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

数据库帮助器

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

推荐答案

根据下面的链接，您插入的值未通过外键约束表示您添加了一个foregin键的值父表中存在

According to the below link you insert the value which failed the constraints of foreign key means you added a value of foregin key which not exists in parent table

https://www.sqlite.org/foreignkeys.html

这篇关于SQLite外键约束失败(代码787)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！