外键约束ON DELETE CASCADE在android上的sqlite数据库中不起作用 [英] 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)";

我有如下创建的轨道表

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 引用表 days 的 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("PRAGMA 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.

幸运的是，还有其他选择.

Fortunately there is an alternative.

您可以在创建表查询下面执行另一个类似这样的查询:

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.

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