Foreign keys in FMDatabaseQueue

Question

so I'm using FMDB library, and I would like to enable foreign keys which is done via

[db executeQuery:@"PRAGMA foreign_keys=ON"];

But I'm using FMDatabaseQueue, initialised like this

-(FMDatabaseQueue*)queue
{

    if (_queue == nil)
    {
        FMDatabaseQueue* queue = [FMDatabaseQueue databaseQueueWithPath:self.dbPath];
        _queue = queue;
    }
    return _queue;
} 

and then I'm using it like this

-(NSNumber*)phoneDBID:(NSString*)phoneNumber
{
    __block NSNumber* phoneDBID = nil;
    [self.queue inDatabase:^(FMDatabase *db) {
        FMResultSet* result = [db executeQuery:@"SELECT * from Contact_numbers where number= ?;", phoneNumber];
        if ([result next])
        {
            phoneDBID = [NSNumber numberWithInt:[result intForColumn:@"contact_id"]];
        }
        [result close];
    }];
    return phoneDBID;
}

and I don't think Foreign keys are enabled in queue, is there a way how to enable them for queue, or I have to do it in every single query?

Answer 1

Two observations:

1. The only caveat regarding foreign key constraints with FMDatabaseQueue is that I would advise against using PRAGMA foreign_keys within a FMDatabaseQueue transaction (i.e. within a inTransaction block). The documentation for PRAGMA foreign_keys says:

   This pragma is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT.

   If you do this pragma from within a inDatabase block, though, you will be fine.

2. Your example does not illustrate foreign_keys in action. Foreign key constraints are only applicable when modifying data in the database. But you're only doing SELECT, so the setting of PRAGMA foreign_keys is irrelevant.

   To illustrate the use of this pragma, consider the following example. I create book and author tables, where the former has a foreign key to the latter:

   [queue inDatabase:^(FMDatabase *db) {
       success = [db executeUpdate:@"create table author (author_id integer primary key, name text)"];
       if (!success) NSLog(@"Create author table failed: %@", [db lastErrorMessage]);
   
       success = [db executeUpdate:@"create table book (book_id integer primary key, author_id integer, title text, FOREIGN KEY(author_id) REFERENCES author(author_id))"];
       if (!success) NSLog(@"Create book table failed: %@", [db lastErrorMessage]);
   }];
   

   Without foreign_keys pragma, this works:

   [queue inDatabase:^(FMDatabase *db) {
   
       // without foreign key constraints enforced, this will succeed, even though the author_id has not yet been added to author table
   
       success = [db executeUpdate:@"insert into book (book_id, author_id, title) values (?, ?, ?)", @(1), @(101), @"Romeo and Juliet"];
       if (!success) NSLog(@"Insert 'Romeo and Juliet' failed: %@", [db lastErrorMessage]);
   
       // obviously, this will, too
   
       success = [db executeUpdate:@"insert into author (author_id, name) values (?, ?)", @(101), @"William Shakespeare"];
       if (!success) NSLog(@"Insert 'William Shakespeare' failed: %@", [db lastErrorMessage]);
   }];
   

   But if I turn on foreign keys:

   [queue inDatabase:^(FMDatabase *db) {
   
       // turn on foreign keys
   
       success = [db executeUpdate:@"PRAGMA foreign_keys = YES"];
       if (!success) NSLog(@"Foreign keys pragma failed: %@", [db lastErrorMessage]);
   }];
   

   And if try that again, the first insert of the book without a corresponding author entry fails. I can't insert the book entry until I insert the author entry:

   [queue inDatabase:^(FMDatabase *db) {
   
       // with foreign key this should (and does) fail
   
       success = [db executeUpdate:@"insert into book (book_id, author_id, title) values (?, ?, ?)", @(2), @(201), @"One Hundred Years of Solitude"];
       if (!success) NSLog(@"First insert of 'Hundred Years of Solitude' failed: %@", [db lastErrorMessage]);
   
       // but if we insert author ...
   
       success = [db executeUpdate:@"insert into author (author_id, name) values (?, ?)", @(201), @"Gabriel García Márquez"];
       if (!success) NSLog(@"Insert 'Gabriel García Márquez' failed: %@", [db lastErrorMessage]);
   
       // ... now this will succeed.
   
       success = [db executeUpdate:@"insert into book (book_id, author_id, title) values (?, ?, ?)", @(2), @(201), @"One Hundred Years of Solitude"];
       if (!success) NSLog(@"Second insert 'Hundred Years of Solitude' failed: %@", [db lastErrorMessage]);
   }];
   

So, the long and short of it, foreign keys work fine with FMDatabaseQueue, but I would only advise against doing it from within a inTransaction call.