Java 使用 SQLite 的 Android 中的外键约束？在删除级联
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2545558/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Foreign key constraints in Android using SQLite? on Delete cascade
提问by jcrowson
I have two tables: tracks and waypoints, a track can have many waypoints, but a waypoint is assigned to only 1 track.
我有两个表：tracks 和waypoints，一个track 可以有很多个waypoint，但是一个waypoint 只分配给1 个track。
In the way points table I have a column called "trackidfk" which inserts the track_ID once a track is made, however I have not setup Foreign Key constraints on this column.
在路径点表中，我有一个名为“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.
当我删除一个轨迹时，我想删除指定的航点，这可能吗？。我阅读了有关使用触发器的信息，但我认为 Android 不支持它们。
To create the waypoints table:
要创建航点表：
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"
+ " );"
);
...
}
采纳答案by Phil
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.
支持删除级联的外键约束，但您需要启用它们。
我刚刚将以下内容添加到我的SQLOpenHelper 中，这似乎可以解决问题。
@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
回答by Thorsten Dittmar
I don't think SQLite supports this out of the box. What I'm doing in my apps is:
我不认为 SQLite 开箱即用地支持这一点。我在我的应用程序中所做的是：
- Create transaction
- Delete detail data (waypoints in your example)
- Delete master data (tracks in your example)
- Commit transaction on success
- 创建交易
- 删除详细数据（示例中的航点）
- 删除主数据（示例中的曲目）
- 成功时提交交易
That way I'm sure that either all the data is deleted or none.
这样我确定要么所有数据都被删除，要么没有。
回答by Dave.B
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.
android 支持触发器，sqlite 不支持这种类型的级联删除。可以在此处找到在 android 上使用触发器的示例。虽然像 Thorsten 所说的那样使用事务可能和触发器一样简单。
回答by GBouerat
SQLite version in android 1.6 is 3.5.9 so it doesn't support foreign keys...
android 1.6 中的 SQLite 版本是 3.5.9 所以它不支持外键...
http://www.sqlite.org/foreignkeys.html"This document describes the support for SQL foreign key constraints introduced in SQLite version 3.6.19."
http://www.sqlite.org/foreignkeys.html“本文档描述了对 SQLite 3.6.19 版中引入的 SQL 外键约束的支持。”
In Froyo it's SQLite version 3.6.22, so ...
在 Froyo 中，它是 SQLite 版本 3.6.22，所以......
EDIT: to see sqlite version : adb shell sqlite3 -version
编辑：查看sqlite版本：adb shell sqlite3 -version
回答by Yar
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.
Android 2.2 及更高版本的 SQLite 支持带有“删除级联”的外键。但是在使用它们时要小心：有时在一个列上启动一个外键时会报告错误，但真正的问题在于子表中的另一列外键约束，或者其他一些表引用了这个表。
Looks like SQLite checks all constraints when firing up one of them. It is actually mentioned in the documentation. DDL versus DML constraint checks.
看起来 SQLite 在启动其中一个约束时会检查所有约束。它实际上在文档中提到。DDL 与 DML 约束检查。
回答by e.shishkin
Since Android 4.1 (API 16) SQLiteDatabasesupports:
从 Android 4.1 (API 16) SQLiteDatabase 开始支持：
public void setForeignKeyConstraintsEnabled (boolean enable)
回答by malcolm
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)
正如 e.shishkin 的帖子所说，从 API 16 开始，您应该SqLiteOpenHelper.onConfigure(SqLiteDatabase)使用以下方法在方法中启用外键约束db.setForeignKeyConstraintsEnabled(boolean)
@Override
public void onConfigure(SQLiteDatabase db){
db.setForeignKeyConstraintsEnabled(true);
}
回答by anand krish
Whatever @phil mentioned is good. But you can use another default method available in Database itself to set the foreignkey. That is setForeignKeyConstraintsEnabled(true).
@phil 提到的一切都很好。但是您可以使用数据库本身中可用的另一种默认方法来设置外键。那就是 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
回答by Codeversed
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);
}
回答by krishnakumarp
If you are using Android Room, do as shown below.
如果您使用的是 Android Room，请按如下所示操作。
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()
