HDBC-sqlite3 and Foreign Key Constraints

Summary: If you want foreign key constraints with sqlite in Haskell don't use HDBC.

I was using the HDBC and HDBC-sqlite3 packages, and ran into a problem where foreign key constraints weren't being enforced. Using the example from the sqlite website, I wasn't able to get an error when I did the following.

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY,
  artistname  TEXT
);

CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT,
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

-- Should throw an error
INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);

Running the example from the command line, I was able to check the foreign key PRAGMA and verify that my version of sqlite3 had been compiled to support foreign keys.

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;
> 1

However, I wasn't able to set the PRAGMA with run in HDBC-sqlite3. I'm not 100% sure what the problem is, but it seems to have something to do with autocommit and transactions. The HDBC package does not support autocommit, and the sqlite website says,

It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect.

Ultimately, I ended up switching to sqlite-simple