title: How to enforce foreign keys using Xerial SQLite JDBC?
body: <p>According to their release notes, the <a href="http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC" rel="nofollow noreferrer">Xerial SQLite JDBC</a> 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:</p>
  
  <pre><code>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)");
  }
  </code></pre>
  
  <p>The table definitions are taken directly from the sample in the <a href="http://www.sqlite.org/foreignkeys.html" rel="nofollow noreferrer">SQLite documentation</a>. This is supposed to fail, but it doesn't. I also checked, and it really inserts the tuple (no ignore or something like that).</p>
  
  <p>Does anyone have any experience with that, or knows how to make it work?</p>
  
  <p><strong>Update</strong></p>
  
  <p>I got a <a href="http://code.google.com/p/xerial/source/browse/src/test/java/org/sqlite/ConnectionTest.java?repo=sqlite#68" rel="nofollow noreferrer">testcase from Xerial</a> 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?</p>