在 Android 中使用 Delete 级联的 SQLite? 的外键约束

我有两个表: 路径和路径点，一个路径可以有许多路径点，但是一个路径点只分配给一个路径。

在 way points 表中，我有一个名为“ trackidfk”的列，它在跟踪完成后插入 track _ ID，但是我没有在这个列上设置外键约束。

When I delete a track I want to delete the assigned waypoints, is this possible?. I read about using Triggers but I don't think they are supported in 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"
+ " );"
);


...
}
68020 次浏览
小开

I don't think SQLite supports this out of the box. What I'm doing in my apps is:

  1. Create transaction
  2. Delete detail data (waypoints in your example)
  3. Delete master data (tracks in your example)
  4. Commit transaction on success

That way I'm sure that either all the data is deleted or none.

小开

Triggers are supported by android and that type of cascade delete is not supported by sqlite. An example of using triggers on android can be found here. Though using transactions as Thorsten stated is probably just as easy as a trigger.

小开
最佳答案

Foreign key constraints with on delete cascade are supported, but you need to enable them.
I just added the following to my SQLOpenHelper, which seems to do the trick.

@Override
public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
if (!db.isReadOnly()) {
// Enable foreign key constraints
db.execSQL("PRAGMA foreign_keys=ON;");
}
}

I declared my referencing column as follows.

mailbox_id INTEGER REFERENCES mailboxes ON DELETE CASCADE
小开

SQLite version in android 1.6 is 3.5.9 so it doesn't support foreign keys...

http://www.sqlite.org/foreignkeys.html "This document describes the support for SQL foreign key constraints introduced in SQLite version 3.6.19."

In Froyo it's SQLite version 3.6.22, so ...

EDIT: to see sqlite version : adb shell sqlite3 -version

小开

Foreign keys with "on delete cascade" are supported in SQLite in Android 2.2 and up. But be careful when using them: sometimes an error is reported when firing up one foreign key on one column, but the real problem lies in either another column foreign key constraint in the child table, or some other table thet references this table.

Looks like SQLite checks all constraints when firing up one of them. It is actually mentioned in the documentation. DDL versus DML constraint checks.

小开

Since Android 4.1 (API 16) SQLiteDatabase supports:

public void setForeignKeyConstraintsEnabled (boolean enable)
小开

As the post from e.shishkin says from API 16 up you should enable foreign key constraints in the SqLiteOpenHelper.onConfigure(SqLiteDatabase) method using the db.setForeignKeyConstraintsEnabled(boolean)

@Override
public void onConfigure(SQLiteDatabase db){
db.setForeignKeyConstraintsEnabled(true);
}
小开

Whatever @phil mentioned is good. But you can use another default method available in Database itself to set the foreignkey. That is setForeignKeyConstraintsEnabled(true). 

@Override
public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
if (!db.isReadOnly()) {
// Enable foreign key constraints
db.execSQL("PRAGMA foreign_keys=ON;");
//(OR)
db.setForeignKeyConstraintsEnabled (true)
}
}

For Docs refer SQLiteDatabase.setForeignKeyConstraintsEnabled

小开

Never too old of a question to answer with a more complete answer.

@Override public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
if (!db.isReadOnly()) {
setForeignKeyConstraintsEnabled(db);
}
mOpenHelperCallbacks.onOpen(mContext, db);
}


private void setForeignKeyConstraintsEnabled(SQLiteDatabase db) {
if (Build.VERSION.SDK_INT < Build.VERSION_CODES.JELLY_BEAN) {
setForeignKeyConstraintsEnabledPreJellyBean(db);
} else {
setForeignKeyConstraintsEnabledPostJellyBean(db);
}
}


private void setForeignKeyConstraintsEnabledPreJellyBean(SQLiteDatabase db) {
db.execSQL("PRAGMA foreign_keys=ON;");
}


@TargetApi(Build.VERSION_CODES.JELLY_BEAN)
private void setForeignKeyConstraintsEnabledPostJellyBean(SQLiteDatabase db) {
db.setForeignKeyConstraintsEnabled(true);
}
小开

If you are using Android Room, do as shown below.

Room.databaseBuilder(context, AppDatabase::class.java, DATABASE_NAME)
.addCallback(object : RoomDatabase.Callback() {
// Called when the database has been opened.
override fun onOpen(db: SupportSQLiteDatabase) {
super.onOpen(db)
//True to enable foreign key constraints
db.setForeignKeyConstraintsEnabled(true)
}


// Called when the database is created for the first time.
override fun onCreate(db: SupportSQLiteDatabase) {
super.onCreate(db)
}
}).build()

提交答案