Sqlite Foreign Keys

Question

I am trying to enable foreign keys using the haskell library HDBC-sqlite3. This library uses a small helper c-function

int sqlite3_open2(const char *filename, finalizeonce **ppo) 

which in turn calls sqlite3_open one. In the sqlite documentation, I found a nice sqlite3_db_config function that should include foreign keys. To test this, I quickly added 2 rows to sqlite3_open2 (the last two from the list):

 int sqlite3_open2(const char *filename, finalizeonce **ppo) { sqlite3 *ppDb; finalizeonce *newobj; int res, *resFK, resFK1; fprintf(stderr, "DB pointer: %d\n", ppDb); res = sqlite3_open(filename, &ppDb); resFK1 = sqlite3_db_config(ppDb, 1002, 1, resFK); fprintf(stderr, "\nForeign Keys: ON/OFF:%d ERR:%d\n", resFK, resFK1); ... 

My surprise was the result: Foreign Keys: ON/OFF:0 ERR:1 .

Can someone give me a hint what am I doing wrong or what will be the correct way to include foreign keys?

Answer 1

In accordance with the documents :

Assuming the library is compiled with foreign key constraints enabled, it should be enabled by the application at runtime using the PRAGMA foreign_keys command. For instance:

sqlite> PRAGMA foreign_keys = ON;

Foreign key restrictions are disabled by default (for backward compatibility), so they must be enabled separately for each database connection separately.

So, after your sqlite3_open() you probably want to add the following:

 sqlite3_exec(ppDb, "PRAGMA foreign_keys = ON;", 0, 0, 0); 

Answer 2

I had difficulty enabling foreign keys using the HDBC-sqlite3 API , because the mentioned PRAGMA had to be called outside the transaction, and the library opened a new transaction in the background after the connection was established and after each commit. However, the workaround was easy:

 main = do conn <- connectSqlite3 "test.db" runRaw conn "COMMIT; PRAGMA foreign_keys = ON; BEGIN TRANSACTION"