How to enforce foreign keys using Xerial SQLite JDBC?
- by Space_C0wb0y
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?
