不工作在Android上的SQLite数据库的DELETE CASCADE外键约束 [英] foreign key constraint ON DELETE CASCADE not working in sqlite database on android

问题描述

我已经创建了天表如下

"create table days(" +
            "day_id  integer primary key autoincrement, " +
            "conference_id integer , " +
            "day_date text, " +
            "day_start_time text, " +
            "day_end_time text, " +
            "day_summary text, " +
            "day_description text)";

创建为

和我有曲目表如下

and i have tracks table created as follows

CREATE_TABLE_TRACK = "create table track(" +
        "track_id integer primary key autoincrement," +
        "day_id integer,"+
        "track_name text," +
        "track_description text," +
        " FOREIGN KEY(day_id) REFERENCES days(day_id) ON DELETE CASCADE )";

如上图所示，我有外键day_id引用的表天day_id ...

as shown above i have foreign key day_id referencing to the day_id of table days...

所以，如果我再删除相应的曲目也应删除这一天我想是...但它简化版，发生在我的情况。

So what i want is if i delete the day then corresponding track should also be deleted... But it does't happen in my case..

我有源码版本的 3.5.9

和还我增加了1号线在我的辅助类为

And also i have added 1 line in my helper class as

> db.execSQL（杂foreign_keys = ON;）;

但仍然是行不通的..请帮助我..

but is still won't work.. please help me out..

推荐答案

级联删除不支持，直到SQLite的版本3.6.19，它是第一个包含在Android 2.2。

Cascading delete isn't supported until Sqlite version 3.6.19, which is first included on Android 2.2.

幸运的是有一个替代。

您可以执行另一个查询像这样下方的创建表查询：

You can execute another query like this below your create table query:

db.execSQL("CREATE TRIGGER delete_days_with track BEFORE DELETE ON track "
       +  "FOR EACH ROW BEGIN"
       +         " DELETE FROM days WHERE track.day_id = days.day_id "
       +  "END;");

注意 delete_days_with_track 只是一个名字描述的触发做什么，而这仅仅是我用的图案;我相信你能说出它，你想要什么。

Note that delete_days_with_track is just a name descriptive of what the trigger does, and this is just the pattern I use; I believe you could name it anything you wish.

这篇关于不工作在Android上的SQLite数据库的DELETE CASCADE外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！