How to turn on foreign keys in FMDatabase?

Question

Just stumbled upon the fact foreign keys are disabled by default in sqlite. I have "on delete casdade" foreign key and deleting parent table records does not delete child records. Various posts indicate you need to enable that on each connection "PRAGMA foreign_keys = ON; ". So, where to enable it in FMDatabase? I would prefer setting some setting instead of running the command before each SQL stmt. PS. I'm using FMDatabaseQueue.

Answer 1

I quickly checked and PRAGMA foreign_keys = ON; works fine for me both on 5.1 simulator and 5.1 iPod Touch. As ccgus suggests, you should cache the data base connection. If you use the queue, just cache the queue and reorganize your code so it doesn't create new queue each time you need to use the database. With your current approach, what is the point of having a queue if you don't really use it but create new each time?

But back to the question, as you are already aware, foreign keys are off by default, so you need to first enable it. I managed to do it with PRAGMA foreign_keys = ON;, here is some more of the test code I used:

//create database
NSString* dbPath = [(NSArray*)NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
dbPath = [dbPath stringByAppendingPathComponent:@"test.db"];
db = [FMDatabase databaseWithPath:dbPath];
if ([db open]) {
    NSLog(@"Database %@ opened", dbPath);
    //check for foreign_key
    NSString* sql = @"PRAGMA foreign_keys";
    FMResultSet *rs = [db executeQuery:sql];
    int enabled;
    if ([rs next]) {
        enabled = [rs intForColumnIndex:0];
    }
    [rs close];
    if (!enabled) {
        // enable foreign_key
        sql = @"PRAGMA foreign_keys = ON;";
        [db executeUpdate:sql];
        // check if successful
        sql = @"PRAGMA foreign_keys";
        FMResultSet *rs = [db executeQuery:sql];
        if ([rs next]) {
            enabled = [rs intForColumnIndex:0];
        }
        [rs close];
    }
    // do your stuff here, or just cache the connection
} else {
    NSLog(@"Failed to open %@", dbPath);
}

Looks fairly straightforward, the only thing that comes to mind is that you used executeQuery instead of executeUpdate.

Answer 2

Why would you have to run it before each statement? Just open the database, run your pragmas, and then cache the connection for use later on (which you should be doing anyway…).