How to enforce foreign keys using Xerial SQLite JDBC?

Question

According to their release notes, the Xerial SQLite JDBC driver supports foreign keys since version 3.6.20.1. I have tried some time now to get a foreign key constraint to be enforced, but to no avail. Here is what I came up with:

public static void main(String[] args) throws ClassNotFoundException, SQLException {
       Class.forName("org.sqlite.JDBC");

       SQLiteConfig config = new SQLiteConfig();
       config.enforceForeignKeys(true);
       Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:", config.toProperties());

       connection.createStatement().executeUpdate(
               "CREATE TABLE artist(" +
               "artistid    INTEGER PRIMARY KEY, " +
                "artistname  TEXT);");
       connection.createStatement().executeUpdate(
               "CREATE TABLE track("+
                       "trackid     INTEGER," + 
                       "trackname   TEXT," + 
                       "trackartist INTEGER," +
                       "FOREIGN KEY(trackartist) REFERENCES artist(artistid)" +
                ");");
       connection.createStatement().executeUpdate(
               "INSERT INTO track VALUES(14, 'Mr. Bojangles', 3)");
}

The table definitions are taken directly from the sample in the SQLite documentation. This is supposed to fail, but it doesn't. I also checked, and it really inserts the tuple (no ignore or something like that).

Does anyone have any experience with that, or knows how to make it work?

Update

I got a testcase from Xerial that runs for them but fails for me. So there is nothing wrong with the code, but probably the setup. I run on Mac OS and this comes with SQLite 3.6.12 (no foreign keys) installed. Is it possible that their library uses this? How can I check this? If so, how can I make it use another version?

Answer 1

Foreign key enforcement is off by default. You need to use a PRAGMA to switch it on:

PRAGMA foreign_keys = ON;

Moreover, the SQLite library must also have been built with foreign key support at all. It probably has been though.