CODEWITHSUNDEEP
c#databasesqlitewindows-runtime
Bedine
Bedine

Reputation: 31

SQLite Cascade Delete

actually i'm developping windows metro app using SQLite DataBase. i use sqlite manager (mozilla) for administration. i tried delete cascade but it work only in sqlite manager not in C# code :

My function 

public async Task<string> DeleteSurvey(int SurveyID)
{
    string result = string.Empty;
    var db = new SQLite.SQLiteAsyncConnection(App.DBPath);
    var survey = await GetSurvey(SurveyID);
    var res = await db.DeleteAsync(survey);

    if (res > 0)
        result = "Success";
    else
        result = "Echec";

    return result;
}

db.CreateTable<Survey>();

SQLiteCommand command1 = new SQLiteCommand(db);
command1.CommandText = "create table if not exists SurveyItemGroup";
command1.CommandText += "(ID integer primary key autoincrement not null, IDSurvey integer,";
command1.CommandText += "Number integer, Name varchar(50), FOREIGN KEY(IDSurvey) REFERENCES Survey(ID) ON DELETE CASCADE ON UPDATE CASCADE)";
command1.ExecuteNonQuery();

In C# code it only delete survey table not both (Survey and SurveyItemGroup)

PS: I have the same problem with pragma (pragma foreign_keys=ON;) it works only if i do it sqlite manager.

Upvotes: 3

Views: 2113

Answers (1)

Peter Hansen
Peter Hansen

Reputation: 22057

(I'm adding this answer because the OP noted this as his solution, but only in a comment.)

Currently, for ON DELETE CASCADE to work, pragma foreign_keys=on; must be issued in each new connection. It's not currently a persistent setting.

Upvotes: 6

Related Questions