ON DELETE CASCADE is not working is in sqlite3 in ios

Question

I created a .sqlite file in ios programatically like below by enabling pragma foreignkeys ON

    NSFileManager *theFileManager = [NSFileManager defaultManager];
    if ([theFileManager fileExistsAtPath:[self getDatabasePath]] == NO)
    {
        char *theError;
        const char *databasePath = [[self getDatabasePath] UTF8String];
        const char *enableForienKey = [@"PRAGMA foreign_keys = ON;" UTF8String];

        if (sqlite3_open(databasePath, &mDatabase) == SQLITE_OK)
        {
            if (sqlite3_exec(mDatabase, enableForienKey, NULL, NULL, &theError)!=SQLITE_OK)
            {
                DEBUGLOG(kCreateTableError,sqlite3_errmsg(mDatabase));
            }
            sqlite3_close(mDatabase);
        }
        else {
            DEBUGLOG(KFailedToCreateDBFile);
        }
    }

Pragma foreign key is enabling but, I created two tables like below with create queries including ON DELETE CASCADE // First table Create query

   @"CREATE TABLE IF NOT EXISTS Session (sessionAppID INTEGER PRIMARY KEY  NOT NULL , sessionID VARCHAR(255) NOT NULL, userAppID INTEGER, deviceAppID INTEGER NOT NULL, sessionStartTime VARCHAR(255) NOT NULL, sessionEndTime VARCHAR(255), sessionCreatedDateTime VARCHAR(200) NOT NULL,sessionUpdatedDateTime VARCHAR(200) NOT NULL, sessionFailureCount INTEGER NOT NULL,sessionStatus INTEGER NOT NULL, FOREIGN KEY(userAppID) REFERENCES User(userAppID), FOREIGN KEY(deviceAppID) REFERENCES Device(deviceAppID))"

//Second table which is child of first table query

 @"CREATE TABLE IF NOT EXISTS EventLog (eventLogAppID INTEGER PRIMARY KEY  NOT NULL , eventGUID VARCHAR(255) NOT NULL, sessionAppID INTEGER NOT NULL , eventName VARCHAR(255) NOT NULL, eventGroupGUID VARCHAR(255), eventParentGUID VARCHAR(255), eventCategory INTEGER NOT NULL,eventStartTime VARCHAR(255) NOT NULL, eventEndTime VARCHAR(255) ,eventDuration VARCHAR(255),eventType INTEGER NOT NULL,eventCreatedDateTime VARCHAR(200) NOT NULL,eventUpdatedDateTime VARCHAR(200) NOT NULL,eventFailureCount INTEGER NOT NULL,eventStatus INTEGER NOT NULL, FOREIGN KEY(sessionAppID) REFERENCES Session(sessionAppID)ON DELETE CASCADE)"

On deleting the session record, Only session record is deleting eventLog records are not deleting, Can any one please help on this, what will be the problem.By the way I am using sqlite3 version 3.7.1

Answer 1

See http://www.sqlite.org/foreignkeys.html#fk_enable. Note that you need to enable foreign keys for each connection.

Presumably you are creating a new connection to perform the delete query. Update your code so every time you open a db connection, you set the pragma as needed.