objective-c - FMDatabaseQueue 中的外键

Question

所以我正在使用 FMDB 库，我想启用通过以下方式完成的外键

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

但我正在使用 FMDatabaseQueue，像这样初始化

-(FMDatabaseQueue*)queue
{

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

然后我像这样使用它

-(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;
}

而且我不认为队列中启用了外键，有没有办法为队列启用它们，或者我必须在每个查询中都这样做？

Answer 1

两个观察:

1. 关于 FMDatabaseQueue 外键约束的唯一警告是我建议不要在 FMDatabaseQueue 事务中使用 PRAGMA foreign_keys (即在 inTransaction block 中)。 The documentation对于 PRAGMA foreign_keys 说:

   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.

   不过，如果您在 inDatabase block 中执行此 pragma，就没问题。

2. 您的示例没有说明 foreign_keys 的作用。外键约束仅在修改数据库中的数据时适用。但是你只是在做 SELECT，所以 PRAGMA foreign_keys 的设置是无关紧要的。

   为了说明此 pragma 的用法，请考虑以下示例。我创建了 book 和 author 表，其中前者有一个指向后者的外键:

   [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]);
   }];
   

   没有 foreign_keys pragma，这有效:

   [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]);
   }];
   

   但是如果我打开外键:

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

   如果再试一次，第一次插入没有相应author 条目的book 会失败。在插入 author 条目之前，我无法插入 book 条目:

   [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]);
   }];
   

总而言之，外键与 FMDatabaseQueue 一起工作很好，但我只建议不要在 inTransaction 调用中这样做。