How do you enforce foreign key constraints in SQLite through Java?

Question

It appears that SQLite does not enforce foreign keys by default. I'm using sqlitejdbc-v056.jar and I've read that using PRAGMA foreign_keys = ON; will turn on foreign key constraints, and that this needs to be turned on in a per-connection basis.

My question is: what Java statements do I need to execute to turn on this command? I've tried:

connection.createStatement().execute("PRAGMA foreign_keys = ON");

and

Properties properties = new Properties();
properties.setProperty("PRAGMA foreign_keys", "ON");
connection = DriverManager.getConnection("jdbc:sqlite:test.db", properties);

and

connection = DriverManager.getConnection("jdbc:sqlite:test.db;foreign keys=true;");

but none of those work. Is there something I am missing here?

I've seen this answer and I want to do exactly the same thing, only using JDBC.

Answer 1

Code like this:

DriverManager.getConnection("jdbc:sqlite:some.db;foreign keys=true;")

Does not work. You have to create org.sqlite.SQLiteConfig and set it as properties when call getConnection from DriverManager.

public static final String DB_URL = "jdbc:sqlite:database.db";  
public static final String DRIVER = "org.sqlite.JDBC";  

public static Connection getConnection() throws ClassNotFoundException {  
    Class.forName(DRIVER);  
    Connection connection = null;  
    try {  
        SQLiteConfig config = new SQLiteConfig();  
        config.enforceForeignKeys(true);  
        connection = DriverManager.getConnection(DB_URL,config.toProperties());  
    } catch (SQLException ex) {}  
    return connection;  
}

This code taken from this.