Android中使用SQLite的外键约束？在删除级联

Question

Android中使用SQLite的外键约束？在删除级联

我有两个表：轨道和航点，一个轨道可以有很多航点，但一个航点只分配给一个轨道。

在点数表中，我有一个名为“trackidfk”的列，它在制作轨道时插入track_ID，但是我没有在此列上设置外键约束。

当我删除一条轨道时，我想删除指定的路标，这可能吗？我读过有关使用触发器的内容，但我不认为它们在Android中受支持。

要创建航点表：

public void onCreate(SQLiteDatabase db) {
    db.execSQL( "CREATE TABLE " + TABLE_NAME 
                + " (" 
                + _ID         + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
                + LONGITUDE   + " INTEGER," 
                + LATITUDE    + " INTEGER," 
                + TIME        + " INTEGER,"
                + TRACK_ID_FK + " INTEGER"
                + " );"
              );

    ...}

Answer 1

支持使用on delete cascade的外键约束，但您需要启用它们。我刚刚将以下内容添加到我的SQLOpenHelper中，这似乎可以解决问题。@Overridepublic void onOpen(SQLiteDatabase db) {     super.onOpen(db);     if (!db.isReadOnly()) {         // Enable foreign key constraints         db.execSQL("PRAGMA foreign_keys=ON;");     }}我将我的引用列声明如下。mailbox_id INTEGER REFERENCES mailboxes ON DELETE CASCADE

Answer 2

用更完整的答案回答问题永远不会过于陈旧。@Override&nbsp;public&nbsp;void&nbsp;onOpen(SQLiteDatabase&nbsp;db)&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;super.onOpen(db); &nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;(!db.isReadOnly())&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;setForeignKeyConstraintsEnabled(db); &nbsp;&nbsp;&nbsp;&nbsp;} &nbsp;&nbsp;&nbsp;&nbsp;mOpenHelperCallbacks.onOpen(mContext,&nbsp;db);}private&nbsp;void&nbsp;setForeignKeyConstraintsEnabled(SQLiteDatabase&nbsp;db)&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;(Build.VERSION.SDK_INT&nbsp;<&nbsp;Build.VERSION_CODES.JELLY_BEAN)&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;setForeignKeyConstraintsEnabledPreJellyBean(db); &nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;else&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;setForeignKeyConstraintsEnabledPostJellyBean(db); &nbsp;&nbsp;&nbsp;&nbsp;}}private&nbsp;void&nbsp;setForeignKeyConstraintsEnabledPreJellyBean(SQLiteDatabase&nbsp;db)&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;db.execSQL("PRAGMA&nbsp;foreign_keys=ON;");}@TargetApi(Build.VERSION_CODES.JELLY_BEAN)private&nbsp;void&nbsp;setForeignKeyConstraintsEnabledPostJellyBean(SQLiteDatabase&nbsp;db)&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;db.setForeignKeyConstraintsEnabled(true);}

Answer 3

无论@phil提到什么都是好的。但您可以使用Database自身中可用的另一种默认方法来设置外键。那就是setForeignKeyConstraintsEnabled（true）。@Overridepublic&nbsp;void&nbsp;onOpen(SQLiteDatabase&nbsp;db)&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;super.onOpen(db); &nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;(!db.isReadOnly())&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;//&nbsp;Enable&nbsp;foreign&nbsp;key&nbsp;constraints &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;db.execSQL("PRAGMA&nbsp;foreign_keys=ON;");&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;//(OR) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;db.setForeignKeyConstraintsEnabled&nbsp;(true) &nbsp;&nbsp;&nbsp;&nbsp;}}对于Docs，请参阅SQLiteDatabase.setForeignKeyConstraintsEnabled