Sqlite Foreign Keys

Question

I try to enable foreign keys using HDBC-sqlite3 haskell library. This library uses a little helper c - function

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

which calls in turn sqlite3_open one. In the sqlite documentation I've found nice sqlite3_db_config function which is supposed to enable foreign keys. To test it I've added quickly 2 lines into sqlite3_open2 (the two last ones of the listing):

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.

Could somebody give me a hint what am I doing wrong or what would be the proper way of enabling foreign keys?

Hoggish

Answer 1

According to the docs:

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

sqlite> PRAGMA foreign_keys = ON;

Foreign key constraints are disabled by default (for backwards compatibility), so 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);

Hedveh

your solution works. You've opened my eyes in respect how to solve my problem on Haskell level. Once I finish it I'll post it here. – Hoggish

Answer 2

I had difficulties with enabling foreign keys using HDBC-sqlite3 API because mentioned PRAGMA required to be invoked beyond transaction and the library opens in background a new transaction after connection gets established and after each commit. Still the workaround was easy:

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

Hoggish