SQLite的级联删除不工作 [英] SQLite Delete Cascade not working

问题描述

在的Andr​​oid 4.2，使用SQLite 3.7.11，当我从测验表中删除一行，谁的模式如下，在QuizQuestions表中相应的行不会被删除。

我无法弄清楚什么是错的。我试图把

  db.execSQL（杂foreign_keys = ON;）;
 

前后创建表的语句。

CREATE TABLE语句：

  CREATE TABLE测验（quiz_name TEXT PRIMARY KEY COLLATE NOCASE）;

CREATE TABLE quizQuestions（quiz_name TEXT，question_id INTEGER，
     PRIMARY KEY（quiz_name，question_id）
     外键（quiz_name）参考文献测验（quiz_name）ON DELETE CASCADE，
     外键（question_id）参考文献的问题（question_id）ON DELETE CASCADE）;
 

解决方案

您的数据库应该删除 quizQuestions 行，以防有人从测验删除或问题。它会忽略的全部的情况下外键的支持外键约束关闭，您刚才常规列，可以包含任意值。

SQLite的默认设置为 PRAGMA foreign_keys = OFF 每次的的时间打开的数据库。这不是模式的表或属性。

如果你使用 SQLiteOpenHelper 把它放在的OnOpen 。这是被称为每次打开数据库时的地方。 的onCreate 仅在创建数据库时一次。

---

什么 SQLiteOpenHelper 要求当你调用 getWriteableDatabase 首次为

1. onConfigure 每一次，API等级> = 16要求
2. 根据存在和版本的数据库文件的以下称为事务中
   • 的onCreate 如果没有数据库文件。通常情况下，这种情况只有一次在应用程序的整个生命周期。
   • onUpgrade 如果数据库版本（ PRAGMA user_version - 保存在数据库文件内部）小于该版本提供在SQLiteOpenHelper的构造函数。发生的每一次撞击的版本，在code。
   • 在任何文件是否存在和版本相匹配。
3. 的OnOpen 每次

如果 SQLiteOpenHelper 的相同实例已经有一个打开的数据库，它将只返回它并没有以上情况发生。

In Android 4.2, using SQLite 3.7.11, when I delete a row from the Quizzes table, who's schema is below, the corresponding rows in the QuizQuestions table are not deleted.

I can't figure out what's wrong. I have tried putting

db.execSQL("PRAGMA foreign_keys = ON;"); 

before and after the create table statements.

Create table statements:

CREATE TABLE quizzes(quiz_name TEXT PRIMARY KEY COLLATE NOCASE);

CREATE TABLE quizQuestions(quiz_name TEXT, question_id INTEGER,
     PRIMARY KEY(quiz_name, question_id),
     FOREIGN KEY(quiz_name) REFERENCES quizzes(quiz_name) ON DELETE CASCADE,
     FOREIGN KEY(question_id) REFERENCES questions(question_id) ON DELETE CASCADE);

解决方案

Your database should delete rows from quizQuestions in case someone is deleting from quizzes or from questions. It will ignore the entire foreign key constraint in case foreign key support is turned off and you have just regular columns that can contain any value.

SQLite defaults to PRAGMA foreign_keys = OFF every time you open the database. It's not a property of a table or of the schema.

In case you use SQLiteOpenHelper put it in onOpen. That is the place that is called every time the database is opened. onCreate only once when the database is created.

---

What SQLiteOpenHelper calls when you call getWriteableDatabase for the first time is

1. onConfigure every time, API Level >= 16 required
2. depending on the existence and version of the database file the following is called within an transaction
   • onCreate if there is no database file. Typically, this happens only once in the entire lifetime of the app.
   • onUpgrade if the database version (PRAGMA user_version - saved inside the database file) is less then the version supplied in SQLiteOpenHelper's constructor. Happens every time you bump the version in your code.
   • Nothing if file exists and version matches.
3. onOpen every time

If the same instance of SQLiteOpenHelper already has an open database it will just return it and nothing of above happens.

这篇关于SQLite的级联删除不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！