Summary: If you want foreign key constraints with sqlite in Haskell don’t use
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