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

SQLite Foreign Key Constraint Failed (code 787)(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?

插入状态

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

推荐答案

这里的错误与在父实体存在之前创建子实体有关.

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

流程应该是:

1. 创建父级并获取父级 ID.
2. 创建包含一个子实体对父 ID 的引用

因此，如果您在没有有效父 ID 的情况下创建子实体，则会抛出此致命错误.

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

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