尝试更新adnroid中的sqlite数据库时出错 [英] Error when trying to update sqlite database in adnroid

问题描述

我的数据库工作，但我插入的所有记录是为了检查的目的，现在我想删除所有的表，并创建新的，所以我尝试通过更改版本更新数据库。

I had my database working but all the records I have inserted were for checking purpose, now I wanted to delete all the tables and creating new ones, so I tried updating the database by changing the version.

我没有更改任何关于创建表查询，但我得到外键约束失败（代码787）。

I didn't change anything on the create table queries but I'm getting a foreign key constraint failed (code 787).

这是我的 DBHelper 类：

private static final String DATABASE_NAME = "roomatesDB";
    // database version
    private static final int DATABASE_VERSION = 3;

    // tables name
    public static final String APARTMENT_TABLE = "apartment";
    public static final String ROOMATE_TABLE = "roomate";
    public static final String SHOPCART_TABLE = "shopcart";
    public static final String ITEMS_TABLE = "items";

    // common column
    public static final String APARTMENT_NUMBER_COLUMN = "apartmentNum";

    // roomates table columns
    public static final String FIRST_NAME_COLUMN = "firstName";
    public static final String LAST_NAME_COLUMN = "lastName";
    public static final String PHONE_NUMBER_COLUMN = "phoneNumber";

    // shop cart table columns
    public static final String NUMBER_COLUMN = "number";
    public static final String LIST_NAME_COLUMN = "name";

    // item table
    public static final String PRICE_COLUMN = "price";
    public static final String ITEM_NAME_COLUMN = "name";

    // query for creating roomate table
    public static final String CREATE_ROOMATE_TABLE = "CREATE TABLE "
            + ROOMATE_TABLE + "(" + FIRST_NAME_COLUMN + " TEXT, "
            + LAST_NAME_COLUMN + " TEXT, " + PHONE_NUMBER_COLUMN + " TEXT, "
            + APARTMENT_NUMBER_COLUMN + " INTEGER, "
            + "FOREIGN KEY(" + APARTMENT_NUMBER_COLUMN + ") REFERENCES "
            + APARTMENT_TABLE + "(apartmentNum) " + ")";

    // query for crating shop cart table
    public static final String CREATE_SHOPLIST_TABLE = "CREATE TABLE "
            + SHOPCART_TABLE + "(" + NUMBER_COLUMN + " INTEGER PRIMARY KEY,"
            + LIST_NAME_COLUMN + " TEXT, "
            + APARTMENT_NUMBER_COLUMN + " INTEGER, "
            + "FOREIGN KEY(" + APARTMENT_NUMBER_COLUMN + ") REFERENCES "
            + APARTMENT_TABLE + "(apartmentNum) " + ")";

    // query for creating shop item table
    public static final String CREATE_SHOPITEM_TABLE = "CREATE TABLE "
            + ITEMS_TABLE + "(" + ITEM_NAME_COLUMN + " TEXT,"
            + PRICE_COLUMN + " DOUBLE, "
            + NUMBER_COLUMN + " INT, "
            + "FOREIGN KEY(" + NUMBER_COLUMN + ") REFERENCES "
            + SHOPCART_TABLE + "(number) " + ")";

    // query for creating apartment table
    public static final String CREATE_APARTMENT_TABLE = "CREATE TABLE "
            + APARTMENT_TABLE + "(" + APARTMENT_NUMBER_COLUMN + " INTEGER PRIMARY KEY"
            + ")";


    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        db.setForeignKeyConstraintsEnabled(true);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_APARTMENT_TABLE);
        db.execSQL(CREATE_ROOMATE_TABLE);
        db.execSQL(CREATE_SHOPLIST_TABLE);
        db.execSQL(CREATE_SHOPITEM_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + APARTMENT_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + ROOMATE_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + SHOPCART_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + ITEMS_TABLE);

        onCreate(db);

    }

推荐答案

a href =http://www.sqlite.org/lang_droptable.html =nofollow>文档说：

The documentation says:

如果启用外键约束，DROP TABLE命令会在从数据库模式中删除表之前执行隐式DELETE FROM命令。 [...]如果作为DROP TABLE命令的一部分执行的隐式DELETE FROM违反任何直接外键约束，则会返回错误，并且不会删除该表。

If foreign key constraints are enabled, a DROP TABLE command performs an implicit DELETE FROM command before removing the table from the database schema. […] If the implicit DELETE FROM executed as part of a DROP TABLE command violates any immediate foreign key constraints, an error is returned and the table is not dropped.

以正确的顺序删除数据，以使所有中间步骤有效。或者只是禁用外部约束检查（ db.setForeignKeyConstraintsEnabled（））。

Remove the data in the correct order so that all intermediate steps are valid. Or just disable foreign constraint checking (db.setForeignKeyConstraintsEnabled()).

这篇关于尝试更新adnroid中的sqlite数据库时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！