Grdb.swift: foreignKeysEnabled doesn't work on iOS?

[CocoaPriest]

I tried it in simulator only (10.2) and PRAGMA foreign_keys = ON doesn't seem to work when creating the DB (just using dbQueue = try DatabaseQueue(path: dataFileUrl.path, configuration: config)). Can somebody confirm that pls?

[groue]

Hello @CocoaPriest

What do you mean by "doesn't work"? That's not quite clear.

Last time I checked, foreign keys, which are enabled by default on database queues, were working pretty well (which means that errors are thrown on foreign key violations):

let dbQueue = DatabaseQueue()

// SQLite error 787 with statement `INSERT INTO children (parentId) VALUES (1)`: FOREIGN KEY constraint failed
try! dbQueue.inDatabase { db in
    try db.create(table: "parents") { t in
        t.column("id", .integer).primaryKey()
    }
    try db.create(table: "children") { t in
        t.column("parentId", .integer).references("parents")
    }
    try db.execute("INSERT INTO children (parentId) VALUES (1)")
}

[CocoaPriest]

Well, after opening the db file in some external sqlite browser I see that foreign_keys is deactivated. It's also interesting that queries like yours fail and yes, it should mean that fk work. But if you test for cascade delete, for example – these things don't work.
But may be I'm missing something? My background is MS SQL Server, not sqlite...

[groue]

Nope: delete cascades are quite OK as well:

let dbQueue = DatabaseQueue()

try! dbQueue.inDatabase { db in
    try db.create(table: "parents") { t in
        t.column("id", .integer).primaryKey()
    }
    try db.create(table: "children") { t in
        t.column("parentId", .integer).references("parents", onDelete: .cascade)
    }
    try db.execute("INSERT INTO parents (id) VALUES (1)")
    try db.execute("INSERT INTO children (parentId) VALUES (1)")
    try db.execute("DELETE FROM parents")
    try print(Row.fetchAll(db, "SELECT * FROM children"))   // empty array
}

If you have a trouble, please state it clearly.

[CocoaPriest]

This is really weird. Are you using an in-memory db or in file?

[groue]

In-memory dbs in the sample codes above. But GRDB is thoroughly tested with file databases. Yes, it's weird, but not on GRDB's side.

[CocoaPriest]

When in file, PRAGMA foreign_keys doesn't have any effect here:

❯ sqlite3 TDataModel.sqlite
SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
sqlite> PRAGMA foreign_keys;
0

[groue]

Quoting the documentation for PRAGMA foreign_keys:

Changing the foreign_keys setting affects the execution of all statements prepared using the database connection.

It does not say that statements in other connections are affected as well. In other words, that setting is not sticky. And you have to set it to true for all connections where you expect foreign keys to be in effect.

[CocoaPriest]

Oh, this explains it. Thanks!